MySQL DELETE Query

If you want to delete a record from any MySQL table, then you can use SQL command DELETE FROM. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax:

Here is generic SQL syntax of DELETE command to delete data from a MySQL table:

DELETE FROM table_name [WHERE Clause]

• If WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
• You can specify any condition using WHERE clause.
• You can delete records in a single table at a time.

The WHERE clause is very useful when you want to delete selected rows in a table.

Deleting Data from Command Prompt:

This will use SQL DELETE command with WHERE clause to delete selected data into MySQL table tutorials_tbl.

Example:

Following example will delete a record into tutorial_tbl whose tutorial_id is 3.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

Deleting Data Using PHP Script:

You can use SQL DELETE command with or without WHERE CLAUSE into PHP function mysql_query(). This function will execute SQL command in similar way it is executed at mysql> prompt.

Example:

Try out the following example to delete a record from tutorial_tbl whose tutorial_id is 3.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'DELETE FROM tutorials_tbl
        WHERE tutorial_id=3';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
?>

MySQL UPDATE Query

There may be a requirement where existing data in a MySQL table needs to be modified. You can do so by using SQL UPDATE command. This will modify any field value of any MySQL table.

Syntax:

Here is generic SQL syntax of UPDATE command to modify data into MySQL table:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• You can update one or more field altogether.
• You can specify any condition using WHERE clause.
• You can update values in a single table at a time.

The WHERE clause is very useful when you want to update selected rows in a table.

Updating Data from Command Prompt:

This will use SQL UPDATE command with WHERE clause to update selected data into MySQL table tutorials_tbl.

Example:

Following example will update tutorial_title field for a record having tutorial_id as 3.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> UPDATE tutorials_tbl 
    -> SET tutorial_title='Learning JAVA' 
    -> WHERE tutorial_id=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Updating Data Using PHP Script:

You can use SQL UPDATE command with or without WHERE CLAUSE into PHP function mysql_query(). This function will execute SQL command in similar way it is executed at mysql> prompt.

Example:

Try out the following example to update tutorial_title field for a record having tutorial_id as 3.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'UPDATE tutorials_tbl
        SET tutorial_title="Learning JAVA"
        WHERE tutorial_id=3';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>

MySQL WHERE Clause

We have seen SQL SELECT command to fetch data from MySQL table. We can use a conditional clause called WHERE clause to filter out results. Using WHERE clause, we can specify a selection criteria to select required records from a table.

Syntax:

Here is generic SQL syntax of SELECT command with WHERE clause to fetch data from MySQL table:

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• You can use one or more tables separated by comma to include various conditions using a WHERE clause, but WHERE clause is an optional part of SELECT command.
• You can specify any condition using WHERE clause.
• You can specify more than one conditions using AND or OR operators.
• A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

The WHERE clause works like an if condition in any programming language. This clause is used to compare given value with the field value available in MySQL table. If given value from outside is equal to the available field value in MySQL table, then it returns that row.

Here is the list of operators, which can be used with WHERE clause.

Assume field A holds 10 and field B holds 20, then:

Operator	Description	Example
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.	(A = B) is not true.
!=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(A != B) is true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(A > B) is not true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(A < B) is true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(A >= B) is not true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.	(A <= B) is true.

The WHERE clause is very useful when you want to fetch selected rows from a table, especially when you use MySQL Join. Joins are discussed in another chapter.

It is a common practice to search records using Primary Key to make search fast.

If given condition does not match any record in the table, then query would not return any row.

Fetching Data from Command Prompt:

This will use SQL SELECT command with WHERE clause to fetch selected data from MySQL table tutorials_tbl.

Example:

Following example will return all the records from tutorials_tbl table for which author name is Sanjay:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author='Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Unless performing a LIKE comparison on a string, the comparison is not case sensitive. You can make your search case sensitive using BINARY keyword as follows:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl \
          WHERE BINARY tutorial_author='sanjay';
Empty set (0.02 sec)

mysql>

Fetching Data Using PHP Script:

You can use same SQL SELECT command with WHERE CLAUSE into PHP functionmysql_query(). This function is used to execute SQL command and later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Example:

Following example will return all the records from tutorials_tbl table for which author name is Sanjay:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl
        WHERE tutorial_author="Sanjay"';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MySQL Select Query

The SQL SELECT command is used to fetch data from MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax:

Here is generic SQL syntax of SELECT command to fetch data from MySQL table:

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

• You can use one or more tables separated by comma to include various conditions using a WHERE clause, but WHERE clause is an optional part of SELECT command.
• You can fetch one or more fields in a single SELECT command.
• You can specify star (*) in place of fields. In this case, SELECT will return all the fields.
• You can specify any condition using WHERE clause.
• You can specify an offset using OFFSET from where SELECT will start returning records. By default offset is zero.
• You can limit the number of returns using LIMIT attribute.

Fetching Data from Command Prompt:

This will use SQL SELECT command to fetch data from MySQL table tutorials_tbl

Example:

Following example will return all the records from tutorials_tbl table:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-21      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-21      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)

mysql>

Fetching Data Using PHP Script:

You can use same SQL SELECT command into PHP function mysql_query(). This function is used to execute SQL command and later another PHP functionmysql_fetch_array() can be used to fetch all the selected data. This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Below is a simple example to fetch records from tutorials_tbl table.

Example:

Try out the following example to display all the records from tutorials_tbl table.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

The content of the rows are assigned to the variable $row and the values in row are then printed.

NOTE: Always remember to put curly brackets when you want to insert an array value directly into a string.

In above example, the constant MYSQL_ASSOC is used as the second argument to PHP function mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.

PHP provides another function called mysql_fetch_assoc(), which also returns the row as an associative array.

Example:

Try out the following example to display all the records from tutorial_tbl table using mysql_fetch_assoc() function.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($retval))
{
    echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

You can also use the constant MYSQL_NUM as the second argument to PHP function mysql_fetch_array(). This will cause the function to return an array with numeric index.

Example:

Try out the following example to display all the records from tutorials_tbl table using MYSQL_NUM argument.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
    echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>

All the above three examples will produce the same result.

Releasing Memory:

It's a good practice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysql_free_result(). Below is the example to show how it has to be used.

Example:

Try out the following example

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, 
               tutorial_author, submission_date
        FROM tutorials_tbl';

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
    echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
}
mysql_free_result($retval);
echo "Fetched data successfully\n";
mysql_close($conn);
?>

While fetching data, you can write as complex SQL as you like. Procedure will remain same as mentioned above.