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 UK Postcode Validation

A great script by John Gardner for UK postcode validation. Conforms to the official postcode specification by the UK cabinet office. Enjoy!

<?php
/*==============================================================================
Application:   Utiity Function
Author:        John Gardner

Version:       V1.0
Date:          25th December 2004
Description:   Used to check the validity of a UK postcode

Version:       V2.0
Date:          8th March 2005
Description:   BFPO postcodes implemented.
               The rules concerning which alphabetic characters are alllowed in 
               which part of the postcode were more stringently implementd.
  
Parameters:    $postcode - postcodeto be checked. This is returned reformatted 
                           if valid.

This function checks the value of the parameter for a valid postcode format. The 
space between the inward part and the outward part is optional, although is 
inserted if not there as it is part of the official postcode.

The functions returns a value of false if the postcode is in an invalid format, 
and a value of true if it is in a valid format. If the postcode is valid, the 
parameter is loaded up with the postcode in capitals, and a space between the 
outward and the inward code to conform to the correct format.
  
Example call:
  
    if (!checkPostcode($postcode) ) {
      echo 'Invalid postcode 
';
    }
                    
------------------------------------------------------------------------------*/
function checkPostcode (&$toCheck) {

  // Permitted letters depend upon their position in the postcode.
  $alpha1 = "[abcdefghijklmnoprstuwyz]";                          // Character 1
  $alpha2 = "[abcdefghklmnopqrstuvwxy]";                          // Character 2
  $alpha3 = "[abcdefghjkstuw]";                                   // Character 3
  $alpha4 = "[abehmnprvwxy]";                                     // Character 4
  $alpha5 = "[abdefghjlnpqrstuwxyz]";                             // Character 5
  
  // Expression for postcodes: AN NAA, ANN NAA, AAN NAA, and AANN NAA
  $pcexp[0] = '^('.$alpha1.'{1}'.$alpha2.'{0,1}[0-9]{1,2})([0-9]{1}'.$alpha5.'{2})$';

  // Expression for postcodes: ANA NAA
  $pcexp[1] =  '^('.$alpha1.'{1}[0-9]{1}'.$alpha3.'{1})([0-9]{1}'.$alpha5.'{2})$';

  // Expression for postcodes: AANA NAA
  $pcexp[2] =  '^('.$alpha1.'{1}'.$alpha2.'[0-9]{1}'.$alpha4.')([0-9]{1}'.$alpha5.'{2})$';
  
  // Exception for the special postcode GIR 0AA
  $pcexp[3] =  '^(gir)(0aa)$';
  
  // Standard BFPO numbers
  $pcexp[4] = '^(bfpo)([0-9]{1,4})$';
  
  // c/o BFPO numbers
  $pcexp[5] = '^(bfpo)(c\/o[0-9]{1,3})$';

  // Load up the string to check, converting into lowercase and removing spaces
  $postcode = strtolower($toCheck);
  $postcode = str_replace (' ', '', $postcode);

  // Assume we are not going to find a valid postcode
  $valid = false;
  
  // Check the string against the six types of postcodes
  foreach ($pcexp as $regexp) {
  
    if (ereg($regexp,$postcode, $matches)) {
      
      // Load new postcode back into the form element  
      $toCheck = strtoupper ($matches[1] . ' ' . $matches [2]);
      
      // Take account of the special BFPO c/o format
      $toCheck = ereg_replace ('C\/O', 'c/o ', $toCheck);
      
      // Remember that we have found that the code is valid and break from loop
      $valid = true;
      break;
    }
  }
    
  // Return with the reformatted valid postcode in uppercase if the postcode was 
  // valid
  if ($valid){return true;} else {return false;};
}
?>

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.

Remove All Characters except Letters and Numbers (alphanumeric)

Unwanted characters in HTML output can be a pain. Here's a quick way to get rid of them:

ereg_replace("[^A-Za-z0-9]", "", $string);

Send in any improvements via comments :)

Convert Integers to Roman Numerals

A nice little script to easily convert from a decimal integer into roman numerals.

<?php

function getRomanNumerals($decimalInteger) 
{
 $n = intval($decimalInteger);
 $res = '';

 $roman_numerals = array(
    'M'  => 1000,
    'CM' => 900,
    'D'  => 500,
    'CD' => 400,
    'C'  => 100,
    'XC' => 90,
    'L'  => 50,
    'XL' => 40,
    'X'  => 10,
    'IX' => 9,
    'V'  => 5,
    'IV' => 4,
    'I'  => 1);

 foreach ($roman_numerals as $roman => $numeral) 
 {
  $matches = intval($n / $numeral);
  $res .= str_repeat($roman, $matches);
  $n = $n % $numeral;
 }

 return $res;
}

echo getRomanNumerals(32);  ////Example Use - returns XXXII

?>

Any improvements? Drop us a comment. Thanks!

PHP Make URLs into Hyperlinks

On some blogs when you type an url or email address (not a hyperlink with <a> tags), it gets made into a link automatically. Here is a bit of code that does just that using regexp:

function add_html_links($string) 
{  
 //Make links beginning with 'ftp://' or 'http://'
 $string = eregi_replace('(((f|ht){1}tp://)[-a-zA-Z0-9@:%_+.~#?&//=]+)', '<a href="\1">\1</a>', $string);  

 //Make links beginning with 'www.'
 $string = eregi_replace('([[:space:]()[{}])(www.[-a-zA-Z0-9@:%_+.~#?&//=]+)', '\1<a href="http://\2">\2</a>', $string);

 //Make mailto: links out of text that is in email format
 $string = eregi_replace('([_.0-9a-z-]+@([0-9a-z][0-9a-z-]+.)+[a-z]{2,3})', '<a href="mailto:\1">\1</a>', $string);   

 return $string;
}

Enjoy :)

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.

PHP echo() Simple Performance Enhancing

Not many people seem to know this, but PHP's echo() function (ok, it's not strictly a function) can actually take more than one argument. More importantly, it is more efficient to supply multiple arguments than using concatenation.

Most developers will tend to concatenate the argument to the echo function, like this:

$useful = "useful";
echo "This ".$useful." PHP blog is actually quite ".useful."!";

A more efficient way of achieving the same is:

$useful = "useful";
echo "This ",$useful," PHP blog is actually quite ",useful,"!";

It can save more than 20% in execution time, which may be just what the doctor ordered for larger projects.

It is good to remember here that echo is an input/output process, which tends to consume a lot of execution time. Therefore, at times it can be worthwhile in a script to have one long concatenated string with a single call to echo, rather than a lot of small calls to echo.

Stock Quotes with PHP

Yahoo! Finance provides quotes of virtually any stock in CSV format - all you need to supply is its ticker symbol. This is great for PHP developers who want to display quotes on a finance related website.

Now for a function that makes getting stock quotes feel like child's play:

function getQuote($symbol) 
{
 
 $symbol  = urlencode( trim( substr(strip_tags($symbol),0,7) ) ); 
 $yahooCSV = "http://finance.yahoo.com/d/quotes.csv?s=$symbol&f=sl1d1t1c1ohgvpnbaejkr&o=t";
 
 $csv = fopen($yahooCSV,"r");

 if($csv) 
 {
  list($quote['symbol'], $quote['last'], $quote['date'], $quote['timestamp'], $quote['change'], $quote['open'],
    $quote['high'], $quote['low'], $quote['volume'], $quote['previousClose'], $quote['name'], $quote['bid'],
    $quote['ask'], $quote['eps'], $quote['YearLow'], $quote['YearHigh'], $quote['PE']) = fgetcsv($csv, ','); 
  
  fclose($csv);
  
  return $quote; 
 } 
 else 
 {
  return false;
 }
}

The function returns an array of the stock's information. Here is a sample PHP script that uses the above function to display all the available stock information about the Royal Bank of Scotland (Ticker: RBS.L).

$RBSQuote = getQuote("RBS.L"); //Returns an array of stock information

echo  "Symbol ".$RBSQuote['symbol']."<br/>";
echo "Last ".$RBSQuote['last']."<br/>";
echo "Date ".$RBSQuote['date']."<br/>";
echo "Timestamp ".$RBSQuote['timestamp']."<br/>";
echo "Change ".$RBSQuote['change']."<br/>";
echo "Open ".$RBSQuote['open']."<br/>";
echo "High ".$RBSQuote['high']."<br/>";
echo "Low ".$RBSQuote['low']."<br/>";
echo "Volume ".$RBSQuote['volume']."<br/>";
echo "Previous Close ".$RBSQuote['previousClose']."<br/>";
echo "Name ".$RBSQuote['name']."<br/>";
echo "Bid ".$RBSQuote['bid']."<br>";
echo "Ask ".$RBSQuote['ask']."<br/>";
echo "EPS ".$RBSQuote['eps']."<br/>";
echo "Year High ".$RBSQuote['YearLow']."<br/>";
echo "Year Low ".$RBSQuote['YearHigh']."<br/>";
echo "PE ".$RBSQuote['PE'];

A pretty nifty piece of code that adds extra utility to your website. I like it because it's simple and concise.

A PHP class called PHPQUOTE is available at Booyah Media that is capable of performing the same task as above in a more object orientated way. PHPQUOTE Download