Using MySQL User-Defined Variables in PHP

User-defined variables are a neat way to have Dynamic SQL queries — statements that accept parameters and possibly produce different result sets based on different parameter values. They’re defined in a MySQL query as follows –

SET @varname=’value’;

eg. SET @varname=’value’; SELECT * FROM Table WHERE Cell=@varname;

I first encountered these in MS-SQL Stored Procedures, so when I had to use them in an ongoing PHP project, these weren’t scary at all. MS-SQL queries involved DECLARE @varname and other stuff — It’s no different from having a variable in a program’s function. Weird syntax though, but that was necessary – How else would you distinguish user-defined variables from columns in the table?

The idea of having user-defined variables introduces a degree flexibility that might be desirable in your application. But you need to be a little careful when using MySQL user-defined variables the query you dispatch to the MySQL Server for execution. I was stuck almost 4 hours trying to debug the problem, and it’s a pretty annoying thing. I’ll get straight to it.

I had used the popular mysql_query() function in my Database Abstraction Layer, only to realize much later that –

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that’s associated with the specified $link_identifier.

The multiple queries crammed into one query idea seemed to fail, even when I tried that bunch of ugly code as a transaction (because that’s presumably what happens when a Stored Procedure has a BEGIN…END block)

The Solution

mysqli_multi_query() — Executes one or multiple queries which are concatenated by a semicolon.

Of course, this means moving your database connection funk to mysqli-based functions. You really can’t complain about it, since the mysqli extension is, in fact, pretty good…unless, you have no way of installing/enabling the mysqli extension at the server. That’s when you’ll really pull your hair out.