Test Your Knowledge on PHP & MySQL

FAQ on PHP & MySQL

(You can find the links for downloading the materials for MySQL and PHP here.)

Two Marks: (2 x 5 = 10)

1. Tell whether PHP variable and keywords are case sensitive?
2. List the different data types supported in PHP.
3. Write a console application in PHP for generating Prime Numbers between 1 to 50.
4. How do you create arrays in PHP. Give an example.
5. Write a sample code in PHP for creating and using Objects.

Big Question:

1. Discuss in details the functions used for generating Summary Reports in MySQL. (8)

(OR)

2. List any four string related functions in MySQL and explain its syntax. (8)
3. Create a table called "project" which contains the following data: (16)

empno	projectID	projectname	budget
15	1	Interface Design	10000
17	2	E-Commerce Solution	20000
19	3	Advertising	50000

The employee number (empno) belongs to the employee who is in charge of project. Choose appropriate datatypes for each of the fields. Create the table and insert the data. Decide which keys you might need (primary key, foreign key) and make sure that these are correctly implemented.

Hint: If MySQL complains about warnings or errors when you insert the data, use "show warnings" and "show errors" to look at the warnings and errors.

• The company has decided that the budgets are too small. Update the project table. Add 5000 to each of the budgets.
• The company decides to take project number 3 from employee 19 and give it to employee 20. Update the table to reflect this change.

(OR)

4. a) How to use MySQL left join to select data from multiple tables? Explain (8)

b) What is metadata in MySQL? How to obtain it and use it? Explain. (8)

5. a) What is PHP? What does it do? List out its features? (8)
   b) Mention the scalar data-types provided by PHP? Explain with Examples (8)

(OR)

6. Write a console application in PHP to find the factorial of first 10 numbers. (16)

Continue reading →

Exercises on MySQL for Engineers

Do the following using MySQL DBMS

Part A:

Execute the following:

1.     CREATE TABLE hiking( trail CHAR(50), area CHAR(50), 
                            distance FLOAT, est_time FLOAT );
   
       Put results for the following commands into exercises.txt:
       
       show tables:
   
       
       show columns from hiking:
   
   

2.     INSERT INTO hiking 
       VALUES( 'Cedar Creek Falls', 'Upper San Diego River', 4.5, 2.5 );
   
       INSERT INTO hiking (trail, area ) 
       VALUES ( 'East Mesa Loop', 'Cuyamaca Mountains' );        
   
       Put results for the following commands into exercises.txt:
   
       select * from hiking
   
   

3.     UPDATE hiking 
       SET distance = 10.5, est_time = 5.5 
       WHERE trail = 'East Mesa Loop';
   
       Put results of select * from hiking into exercises.txt:
   
   

4.   
       DELETE FROM hiking 
       WHERE trail = 'Cedar Creek Falls';
   
       Put results of select * from hiking into exercises.txt:
   
   

Part B:

1. Give the SQL statements to insert the following values into the hiking table:

      +------------------------+--------------------+----------+----------+
      | trail                  | area               | distance | est_time |
      +------------------------+--------------------+----------+----------+
      | East Mesa Loop         | Cuyamaca Mountains |    10.50 |     5.50 |
      | Oak Canyon             | NULL               |     3.00 |     NULL |
      +------------------------+--------------------+----------+----------+
      

2. Give the SQL statement(s) to update the entry for the 'Oak Canyon' trail. Set the area to 'Mission Trails Regional Park' and the estimated time (est_time) to 2 hours. Your table should then look like the following:

      +------------------------+------------------------------+----------+----------+
      | trail                  | area                         | distance | est_time |
      +------------------------+------------------------------+----------+----------+
      | East Mesa Loop         | Cuyamaca Mountains           |    10.50 |     5.50 |
      | Oak Canyon             | Mission Trails Regional Park |     3.00 |     2.00 |
      +------------------------+------------------------------+----------+----------+
      

3. Give the SQL statement to delete trails with a distance greater than 5 miles.
4. Give the SQL statement to create a table called 'rating'. This table rates the difficulty of a hiking trail. It will have two columns: the trail name, 'trail' and the difficulty, 'difficulty'. The trail name is a string of no more than 50 characters and the difficulty is an integer (INT).
   Put the results of show tables into exercises.txt
   Put the results of show columns from rating into exercises.txt
5. What is the command to delete the rating table?

Continue reading →

Data Types Supported in MySQL for handling Numeric Values

Number Data Types Supported in MySQL

MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character and byte) types. MySQL supports all standard SQL numeric data types. These types include the exact numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keywords DEC and FIXED are synonyms for DECIMAL.

MySQL treats DOUBLE as a synonym for DOUBLE PRECISION (a nonstandard extension). MySQL also treats REAL as a synonym for DOUBLE PRECISION (a nonstandard variation), unless the REAL_AS_FLOAT SQL mode is enabled.

The BIT data type stores bit-field values and is supported for MyISAM, MEMORY, InnoDB and NDBCLUSTER tables.

Integer Types (Exact Value)

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.

Type	Storage	Minimum Value	Maximum Value
	(Bytes)	(Signed/Unsigned)	Signed/Unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC. MySQL 5.1 stores DECIMAL values in binary format.
In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point. Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.
In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), where the implementation is permitted to decide the value of M. MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10. If the scale is 0, DECIMAL values contain no decimal point or fractional part.
The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are permitted by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the permissible number of digits.)

Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column.
MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.
Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

Bit-Value Type - BIT

The BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64. To specify bit values, b'value' notation can be used. value is a binary value written using zeros and ones. For example, b'111' and b'10000000' represent 7 and 128, respectively.
If you assign a value to a BIT(M) column that is less than M bits long, the value is padded on the left with zeros. For example, assigning a value of b'101' to a BIT(6) column is, in effect, the same as assigning b'000101'.

Numeric Type Attributes

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.
When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.

Note

The ZEROFILL attribute is ignored when a column is involved in expressions or UNION queries.

If we store values larger than the display width in an integer column that has the ZEROFILL attribute, we may experience problems when MySQL generates temporary tables for some complicated joins. In these cases, MySQL assumes that the data values fit within the column display width.

All integer types can have an optional (nonstandard) attribute UNSIGNED. Unsigned type can be used to permit only nonnegative numbers in a column or when we need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.
Floating-point and fixed-point types also can be UNSIGNED. As with integer types, this attribute prevents negative values from being stored in the column. Unlike the integer types, the upper range of column values remains the same. If we specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
Integer or floating-point data types can have the additional attribute AUTO_INCREMENT. When we insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table.

Continue reading →

Getting started to work with MySQL Client

Entering and Executing Commands on MySQL Client

This section describes the basic principles of entering commands, using several queries you can try out to familiarize yourself with how mysql works.  Here is a simple command that asks the server to tell you its version number and the current date. Type it in as shown here following the mysql> prompt and press Enter:

mysql> SELECT VERSION(), CURRENT_DATE;

+--------------+--------------+

| VERSION()    | CURRENT_DATE |

+--------------+--------------+

| 5.5.0-m2-log | 2009-05-04   |

+--------------+--------------+

1 row in set (0.01 sec)

mysql>

·         When you issue a command, mysql sends it to the server for execution and displays the results, then prints another mysql> prompt to indicate that it is ready for another command.

·         mysql displays query output in tabular form (rows and columns). The first row contains labels for the columns. The rows following are the query results. Normally, column labels are the names of the columns you fetch from database tables. If you're retrieving the value of an expression rather than a table column (as in the example just shown), mysql labels the column using the expression itself.

·         mysql shows how many rows were returned and how long the query took to execute, which gives you a rough idea of server performance. These values are imprecise because they represent wall clock time (not CPU or machine time), and because they are affected by factors such as server load and network latency.

Keywords may be entered in any lettercase. The following queries are equivalent:

mysql> SELECT VERSION(), CURRENT_DATE;

mysql> select version(), current_date;

mysql> SeLeCt vErSiOn(), current_DATE;

Here is another query. It demonstrates that you can use mysql as a simple calculator:

mysql> SELECT SIN(PI()/4), (4+1)*5;

+------------------+---------+

| SIN(PI()/4)      | (4+1)*5 |

+------------------+---------+

| 0.70710678118655 |      25 |

+------------------+---------+

1 row in set (0.02 sec)

The queries shown thus far have been relatively short, single-line statements. You can even enter multiple statements on a single line. Just end each one with a semicolon:

mysql> SELECT VERSION(); SELECT NOW();

+--------------+

| VERSION()    |

+--------------+

| 5.5.0-m2-log |

+--------------+

1 row in set (0.00 sec)

+---------------------+

| NOW()               |

+---------------------+

| 2009-05-04 15:15:00 |

+---------------------+

1 row in set (0.00 sec)

A command need not be given all on a single line, so lengthy commands that require several lines are not a problem. mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line.  Here is a simple multiple-line statement:

mysql> SELECT

    -> USER()

    -> ,

    -> CURRENT_DATE;

+---------------+--------------+

| USER()        | CURRENT_DATE |

+---------------+--------------+

| jon@localhost | 2005-10-11   |

+---------------+--------------+

In this example, notice how the prompt changes from mysql> to -> after you enter the first line of a multiple-line query. This is how mysql indicates that it has not yet seen a complete statement and is waiting for the rest.

If you decide you do not want to execute a command that you are in the process of entering, cancel it by typing \c:

mysql> SELECT

    -> USER()

    -> \c

mysql>

Here, too, notice the prompt. It switches back to mysql> after you type \c, providing feedback to indicate thatmysql is ready for a new command.

Multiple-line statements commonly occur by accident when you intend to issue a command on a single line, but forget the terminating semicolon. In this case, mysql waits for more input:

mysql> SELECT USER()

    ->

If this happens to you (you think you've entered a statement but the only response is a -> prompt), most likelymysql is waiting for the semicolon. If you don't notice what the prompt is telling you, you might sit there for a while before realizing what you need to do. Enter a semicolon to complete the statement, and mysql executes it:

mysql> SELECT USER()

    -> ;

+---------------+

| USER()        |

+---------------+

| jon@localhost |

+---------------+

Continue reading →

Accessing MySQL for Managing Databases!

USING MySQL SERVER!

Starting MySQL Client:

The standard tool for interacting with MySQL is the mysql client program. To get started, issue the following command at your prompt:

mysql -u root -p

You will be prompted to enter the root MySQL user's password. Enter the password you assigned when you installed MySQL, and you'll be presented with the MySQL monitor display:

Welcome to the MySQL monitor.

Commands end with ; or \g. Your MySQL connection id is 41 Server version: 5.1.37-1ubuntu5 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

To access a MySQL server which is running on a remote computer, use the following command:
$ mysql -h 192.168.1.8 -u root -p

In this command, use your host IP Address instead of 192.168.1.8 for remote login.

Using MySQL Client through Commands:

Let's create a database and assign a user to it. Issue the following commands at the MySQL prompt:

CREATE DATABASE testdb;
CREATE USER 'testuser'@localhost IDENTIFIED BY 'CHANGEME';
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@localhost; exit

Now let's log back into the MySQL client as testuser. 
mysql -u testuser -p

Once connected, select the database using the use command, as follows:

mysql>use mydatabase

Once executed, all queries not explicitly specifying a database name will be directed towards the hypothetical mydatabase database.

Now create a sample table called "customers." Issue the following commands:

USE testdb;

CREATE TABLE customers (customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name TEXT, last_name TEXT);

This creates a table with a customer ID field of the type INT for integer (auto-incremented for new records, used as the primary key), as well as two fields for storing the customer's name. Of course, you'd probably want to store much more information than this on a customer, but it's a good example of a common case.

Working with MySQL Databases

A database in MySQL is implemented as a directory containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement creates only a directory under the MySQL data directory and the db.opt file.

If you manually create a directory under the data directory (for example, with mkdir), the server considers it a database directory and it shows up in the output of SHOW DATABASES.

Before you can interact with your MySQL database, you need to create it! You can create a database by executing the following SQL code:

create database DBname; 

Be sure to replace DBname with the actual name you wanted your database to be called.

Use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

The mysql database describes user access privileges. The test database often is available as a workspace for users to try things out.

The list of databases displayed by the statement may be different on your machine; SHOW DATABASES does not show databases that you have no privileges for if you do not have the SHOW DATABASES privilege.

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql. Just specify its name after any connection parameters that you might need to provide. For example:

shell> mysql -h host -u user -p menagerie
Enter password: ********

Experiment with MySQL using the link given here.   Click here for learning Advanced MySQL Queries...

Exercise 1:   

We have a table called store with the following contents:

 Id Name Qty Price
 1 apple 10 1
 2 pear 5 2
 3 banana 10 1.5
 6 lemon 100 0.1
 5 orange 50 0.2 

Write Queries for the following questions:

 

Question 1

List all the items sorted alphabetically. Then list only the first 3. Then the last 3.
Answer:
  select * from store order by 2;
+----+--------+-----+-------+
| Id | Name   | Qty | Price |
+----+--------+-----+-------+
|  1 | apple  |  10 |     1 |
|  3 | banana |  10 |   1.5 |
|  6 | lemon  | 100 |   0.1 |
|  5 | orange |  50 |   0.2 |
|  2 | pear   |   5 |     2 |
+----+--------+-----+-------+

  select * from store order by id desc limit 3;
+----+--------+-----+-------+
| Id | Name   | Qty | Price |
+----+--------+-----+-------+
|  1 | apple  |  10 |     1 |
|  2 | pear   |   5 |     2 |
|  3 | banana |  10 |   1.5 |
+----+--------+-----+-------+


select * from store order by id desc limit 3;
+----+--------+-----+-------+
| Id | Name   | Qty | Price |
+----+--------+-----+-------+
|  6 | lemon  | 100 |   0.1 |
|  5 | orange |  50 |   0.2 |
|  3 | banana |  10 |   1.5 |
+----+--------+-----+-------+

Question 2

list only the items that are more than $1 per unit price
Answer:
select * from store where Price >1;
+----+--------+-----+-------+
| Id | Name   | Qty | Price |
+----+--------+-----+-------+
|  2 | pear   |   5 |     2 |
|  3 | banana |  10 |   1.5 |
+----+--------+-----+-------+

Question 3

list all the items with their extended price (quantity * price)
Answer:
 select *, (Qty*Price) as Total from store;
+----+--------+-----+-------+--------------------+
| Id | Name   | Qty | Price | Total              |
+----+--------+-----+-------+--------------------+
|  1 | apple  |  10 |     1 |                 10 |
|  2 | pear   |   5 |     2 |                 10 |
|  3 | banana |  10 |   1.5 |                 15 |
|  6 | lemon  | 100 |   0.1 | 10.000000149011612 |
|  5 | orange |  50 |   0.2 | 10.000000149011612 |
+----+--------+-----+-------+--------------------+

Question 4

list the total cost of all the items in the store
Answer:
 select SUM((Qty*Price)) as Total from store;
+--------------------+
| Total              |
+--------------------+
| 55.000000298023224 |
+--------------------+

Question 5

how many different items do we have in the store?
Answer:
 select count(*) as Item_Count from store;
+------------+
| Item_Count |
+------------+
|          5 |
+------------+

Exercise 2:

Consider the following tables for manging Department and Courses details.  We have a database with 3 tables:

 Courses
 Id Name deptId
 1 111 1
 2 112 1
 3 250 1
 4 231 1
 5 111 2
 6 250 3
 7 111 4

 Dept
  Id Name
 1 CSC
 2 MTH
 3 EGR
 4 CHM

Write Queries for the following questions USING THE MySQL Server available @ 192.182.172.146:

 

Question 1

List all the CSC Students.
Answer:
Select Course.Name from Dept, Course where Course.deptId = Dept.Id AND Dept.Name LIKE  'CSC';

Question 2

How many different departments are there?
Answer:
Select distinct Name from Dept;

Question 3

List the name of the department and students so that they are output as "CSE 111 112 250", "MTH 111", etc... (in other words, concatenate all the students belong to the same department. The result is expected as follows:

+-----------+-----------------+
| Dept Name | Students        |
+-----------+-----------------+
| CSC       | 111,112,250,231 |
| MTH       | 111             |
| EGR       | 250             |
| CHM       | 111             |
+-----------+-----------------+
Answer:
select Dept.Name as 'Dept Name', GROUP_CONCAT(Course.Name) AS Students FROM Dept, Course WHERE Course.deptId = Dept.Id GROUP BY Dept.Id;

Continue reading →

Installing and Starting MySQL Server

MySQL Database Management System

 
MySQL is a fast, multi-threaded, multi-user, and robust SQL database server. It is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software.

Installing MySQL Server:

To install MySQL, run the following command from a terminal prompt:

sudo apt-get install mysql-server

During the installation process you will be prompted to enter a password for the MySQL root user.

Once the installation is complete, the MySQL server should be started automatically. You can run the following command from a terminal prompt to check whether the MySQL server is running:

sudo netstat -tap | grep mysql

When you run this command, you should see the following line or something similar:

tcp        0      0 localhost.localdomain:mysql           *:* LISTEN -

If the server is not running correctly, you can type the following command to start it:

sudo /etc/init.d/mysql restart 
 

Configuring MySQL for Network Access:

The typical default install of MySQL server only permits connections from localhost (127.0.0.1); this is presumably for reasons of security. While this is certainly secure, in some cases it is undesirable. This post explains how to permit network access to a MySQL server from remote clients.

Locate the my.cnf file, which is the master configuration file for MySQL server. (On a Ubuntu system this file may be located in /etc/mysql.)

Open this file in your favorite editor and look for the following entry:

bind-address = 127.0.0.1

This limits the MySQL server to listening to connections on the localhost address, as explained earlier.  To instead make the MySQL server listen on all interfaces, edit this entry to the following:

bind-address = 0.0.0.0

Save the file, then restart the MySQL server:

sudo /etc/init.d/mysql restart

Your MySQL server should now be network accessible. To verify that it's listening on all interfaces, issue the following command:

netstat -anp | grep 3306

If you see the following, then your configuration is complete:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN 

From the MySQL prompt, grant the user access to a specific database schema. The general format of the command to use is:

GRANT ALL PRIVILEGES ON database.* to ‘user’@'yourremotehost' IDENTIFIED BY 'newpassword';

Where:

‘user’ is the user name of an existing MySQL account. The username is enclosed in single quotes, as shown.

database is the name of the database schema where access will be granted. Either the name of the schema can be used, or an asterisk (*) can be used to specify all databases.

‘yourremotehost’ contains either an IP address where the user will access from, or a domain name. The IP address or host name must appear in single quotes, as shown.

‘newpassword’ contains the password the user must use to access the server. The password must appear in single quotes as well.

To force the changes to take effect immediately, enter the following command:

FLUSH PRIVILEGES;

Specific Examples:
Example A: Granting access for the user jsmith from an IP address:

GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';

Example B: Granting access from a domain:

GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'%.mycompany.com' IDENTIFIED BY 'jimspassword';

Example C: Granting access to all schemas:

GRANT ALL PRIVILEGES ON *.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';

Example D: Granting access from a specific host name on a domain:

GRANT ALL PRIVILEGES ON *.* to jsmith@'jimspc.mycompany.com' IDENTIFIED BY 'jimspassword';

Continue reading →