If you want to read more about MySql5 improvements, please read the official release notes.

The aim of this post is to write more efficient MySql queries. Some servers watch over the MySql resource a web page uses. If the number of requests for one single web page is considered too resource-hungry, the developer may receive a warning (or more) from the host!

Read article “PHP5 to watch over MySql queries” if you want PHP to display MySql queries generated in a specific Web page.

What I want to focus on here is a syntax that was introduced in MySql 4.1 but which is still rarely used :

INSERT INTO… ON DUPLICATE KEY UPDATE…

But first, let’s develop multiple queries for deletion.

Deleting several rows in one single query (DELETE)

Instead of dynamically producing queries in order to delete rows in a table :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

FOREACH($array as $value) {

$sql = “DELETE FROM table_name WHERE id=’$value’”;

$mysql->strictExecute($sql); //Execute query through $mysql object

}

which will produce 3 queries, you can do the following :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

$sql = “”;

FOREACH($array as $value) {

IF($sql <> “”) $sql .= ” OR “;

$sql .= “id=’$value’”; //adds string id=’$value’ to $sql

}

$sql = “DELETE FROM table_name WHERE “.$sql; //builds the single query : DELETE FROM table_name WHERE id=’1′ OR id=’2′ OR id=’4′;

$mysql->strictExecute($sql); //Execute query through $mysql object

which will produce one single query to DB.

I would even advise using the IN predicate :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

$sql = “”;

FOREACH($array as $value) {

IF($sql <> “”) $sql .= “, “;

$sql .= “‘$value’”; //adds string id=’$value’ to $sql

}

$sql = “DELETE FROM table_name WHERE id IN (“.$sql.”)”; //builds the single query : DELETE FROM table_name WHERE id IN (’1′, ’2′, ’4′);

$mysql->strictExecute($sql); //Execute query through $mysql object

Inserting several rows in one single query (INSERT TO)

In order to insert multiple rows in one query, do the following :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“title”=>”Mysql explained”,”n_pages”=>”350″),

1=>array(“title”=>”PHP explained”,”n_pages”=>”150″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

IF($sql <> “”) $sql .= “, “;

$sql .= “(‘”.$value["title"].”‘,’”.$value["n_pages"].”‘)”;

}

$sql = “INSERT INTO table_name(title,n_pages) VALUES”.$sql;

$mysql->strictExecute($sql); //Execute query through $mysql object

Updating several rows in one single query (INSERT INTO… ON DUPLICATE KEY UPDATE)

Updating MySql rows can be done this way :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“id”=>1,”title”=>”Mysql explained”,”n_pages”=>”450″),

1=>array(“id”=>2,”title”=>”PHP explained”,”n_pages”=>”450″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

$sql .= “UPDATE table_name SET title=’”.$value["title"].”‘,n_pages=’”.$value["n_pages"].”‘ WHERE id=’”.$value["id"].”‘”;

$mysql->strictExecute($sql); //Execute query through $mysql object

}

which will produce 2 queries.

The trick here is to generate an INSERT INTO query with the ON DUPLICATE KEY UPDATE syntax. In case the key already exists, only an update will take place. If the key doesn’t exist, Mysql will operate a classical insert into query :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“id”=>1,”title”=>”Mysql explained”,”n_pages”=>”450″),

1=>array(“id”=>2,”title”=>”PHP explained”,”n_pages”=>”450″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

IF($sql <> “”) $sql .= “,”;

$sql .= (‘”.$value["id"].”‘,’”.$value["title"].”‘,’”.$value["n_pages"].”‘);

}

$sql = “INSERT INTO table_name(id,title,n_pages) VALUES”.$sql.” ON DUPLICATE KEY UPDATEid=VALUES(id),title=VALUES(title),n_pages=VALUES(n_pages)”;

$mysql->strictExecute($sql); //Execute query through $mysql object

Webliography :

MySql5 Release notes