About a week ago I asked my friend Simon to help me with what I belived was a particulaly difficult problem with a SQL statement. As it happened, I had made a stupid mistake, and that was that I had used Microsoft Access to help me with building queries. The trouble with Access is that the queries that they produce are not optimised, and they are often over complicated. After a quick SQL tutorial from Simon, I was hungry to practise my new skill, so I decided to rewrite the SQL statements in my Free UK dating site.
It’s been roughly 6 months since I did any significant work on the site, and I knew most of the SQL statements were generated from Access, and a few of them even had some hugely inefficient sub queries thrown in by me. All of the work has been done now, which is why I have been a bit quiet here, and there have been some major improvements – there are now no sub-queries, and many queries have been combined. The main benefit is that many pages that had around 30+ queries and now around 5 queries.
To help me debug some of the queries I wrote the following code that displays the eroneous SQL statement and highlights the part of the statement with the error. This makes it far easier to see where the error is if you have a large query.
function DisplayQuery( $Query, $ExtraText = '' )
{
$search = array( 'FROM',
'WHERE',
'AND',
'JOIN',
'LEFT <br />JOIN',
'ORDER BY' );
$replace = array( '<br />FROM',
'<br />WHERE',
'<br />AND',
'<br />JOIN',
'<br />LEFT JOIN',
' <br />ORDER BY' );
if ( strlen($ErrorText = @mysql_error()) > 0 )
{
$ErrorNumber = mysql_errno();
$FirstQuotePos = strpos( $ErrorText, "'" );
$LastQuotePos = strpos( $ErrorText, "'", $FirstQuotePos+1 );
$QuerySubString = '';
if ( $FirstQuotePos == $LastQuotePos && false !== $FirstQuotePos && false !== $LastQuotePos )
{
$QuerySubString = substr( $ErrorText, $FirstQuotePos+1, strlen( $ErrorText ) - $FirstQuotePos - 1 );
} elseif ( $ErrorNumber != 1146 && $FirstQuotePos < $LastQuotePos && false !== $FirstQuotePos && false !== $LastQuotePos )
{
$QuerySubString = substr( $ErrorText, $FirstQuotePos+1, $LastQuotePos - $FirstQuotePos - 1 );
} elseif ( $ErrorNumber == 1146 && $FirstQuotePos < $LastQuotePos && false !== $FirstQuotePos && false !== $LastQuotePos )
{
$QuerySubString = substr( $ErrorText, $FirstQuotePos+1, $LastQuotePos - $FirstQuotePos - 1 );
$QuerySubString = substr( $QuerySubString, strpos( $QuerySubString, '.' )+1, strlen( $QuerySubString ) - strpos( $QuerySubString, '.' ) );
}
$Query = str_replace( $QuerySubString, '<span style="background:yellow;">' . $QuerySubString . '</span>', $Query );
}
return '<div style="border: 2px red solid; padding: 4px;">' . str_replace( $search, $replace, $Query ) . $ExtraText . '</div>';
}
Here’s how I use this in my projects:
function exec_sql( $Query, $ShowError = false )
{
if ( !($result = @mysql_query( $Query ) ) )
{
if ( $ShowError )
{
echo DisplayQuery( $Query, '<br /><br />' . mysql_error() );
}
}
return $result;
}
If a query is passed to exec_sql() it will die silently unless the $ShowError parameter is set to true. The error will display with the query and error message, and the erroneous part of the query will be highlited:
FROM mem22bertable
LEFT JOIN phototable ON (membertable.UserID = phototable.UserID)
WHERE membertable.UserID=301
AND ((phototable.mainphoto = 1 OR phototable.mainphoto IS NULL)
AND (approved = 1 OR approved IS NULL))
Table ‘brewster_dating.mem22bertable’ doesn’t exist
NOTE: I can’t find a plugin for coloured syntax highlighting at the moment that doesnt display php without any issues – some of the characters above are displayed as html entities. If you want to use the code, click on “PLAIN TEXT”, and the code will be converted into plain text which you can copy and use. If anyone knows of a good coloured syntax highliter for wordpress which works with the visual editor please let me know!