PHP MySQL Multiple (Batch) Queries

Did you know, you can execute multiple queries in PHP using the mysqli extension. Its pretty easy as you can see:

<?      
$connection = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

if ($connection->connect_error) 
{
   echo "Error Occurred While Connection To DataBase";
}


//SQL statements separated by semi-colons

$sqlStatements = "INSERT INTO dbName (columnName) VALUES ('Value1');INSERT INTO dbName (columnName) VALUES ('Value2');INSERT INTO dbName (columnName) VALUES ('Value3');";
 
$sqlResult = $connection->multi_query($sqlStatements);
 
if($sqlResult == true) 
{
   echo 'Successfully executed all statements.';
} 
else 
{
   echo 'Error occurred executing statements.';
}
?>

Clearly, this is more efficient than executing queries one by one, and could save a bit of time in critical applications. Also, mysqli is object-orientated, so there are other useful variables and method that can be returned from the mysqli object to make your code more efficient.

Errors? You may need to enable the mysqli extensions, as these may not be enabled by default. See here http://www.php.net/manual/en/mysqli.installation.php for more info, or leave a comment with your problem.

PHP Optimising MySQL Tables

If you are working with large MySQL databases and are deleting, inserting or updating a large number of rows, then you probably want all the optimisation you can get, since moving a lot of data around can leave the database a little inefficient.

So, here is a nice little PHP script to "defrag" all your tables - it can cut execution time of SELECT statements down to half in many cases!

dbConnect()

$tables = mysql_query("SHOW TABLES");

while ($table = mysql_fetch_assoc($tables))
{
   foreach ($table as $db => $tableName)
   {
       mysql_query("OPTIMIZE TABLE '".$tableName."'")
       or die(mysql_error());
   }
}

The MySQL command for running this on just one table is simply:

OPTIMIZE TABLE 'tableName';

An easy trick that can really speed up a sluggish database driven website or application. Enjoy.

High Performance MySQL: Optimization, Backups, Replication, and More

PHP Preventing SQL Injections

Often in PHP, we use SQL queries that make use of user input variables, for example:

$_POST['username'] = "aUser"; 

$query = " SELECT * FROM users WHERE username = '$_POST[username]' ";

//Executes SELECT * FROM users WHERE username = 'aUser';

This is a major security floor, since an SQL statement could in theory be passed in $_POST['username'] variable with disastrous results:

$_POST['username'] = "'; DELETE * FROM users"; 

$query = "SELECT * FROM users WHERE username = '$_POST[username]'";

//Executes SELECT * FROM users WHERE username = ''; DELETE * FROM customers !

So all your data could be deleted by a very simple but nasty trick :( . To stop this from happening, use mysql_real_escape_string() as shown below:

$_POST['username'] = "'; DELETE * FROM users";

$username = mysql_real_escape_string($_POST['username']);

$query = "SELECT * FROM users WHERE username = '$username'";

//Executes SELECT * FROM users WHERE username = '\'; DELETE * FROM users'

Now, the single quote is escaped thanks to mysql_real_escape_string() function and the query instead searches for " \'; DELETE * FROM users " as a username rather than executing the delete command.

Please go and add this to your code if you haven't already... I have seen too many amateur websites destroyed by something as simple as an SQL injection attack.

PHP MySQL Get Last Inserted Row ID

A simple function to get the id of the last inserted row into a mysql database is:

mysql_insert_id();

An example using a temporary table is shown here:

<?php

$conn = mysql_connect('localhost', 'username', 'password') or die ('Error connecting to mysql');

mysql_select_db('myDatabase');

mysql_query("CREATE TEMPORARY TABLE testTable (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), column1 VARCHAR(50) )") or die (mysql_error());

mysql_query("INSERT INTO testTable VALUES ()");
$id = mysql_insert_id();
echo $id; //Outputs 1

mysql_query("INSERT INTO testTable VALUES ()");
$id = mysql_insert_id();
echo $id; //Outputs 2

?>

Simple and useful PHP ;)

I use a temporary table in the example to demonstrate the concept, so that in case you do decide to test out the example, it will not leave a footprint on your MySQL server, since temporary tables are deleted when the MySQL session is over.