How to use array element references inside query strings? [PHP]

Using braces "{ }", i.e. curly parentheses, will make your life very easy. The braces will allow you to use complex variable expressions inside strings. This is called complex (curly) syntax. In other words, you are encapsulating your variable call structure so it is parsed by PHP before continuing with the string.

If you are working with an array or nested arrays and you want to insert the array element values into a database or use within a string, you will have to define new dedicated variables, to which you will assign the value of the array element. Or you will have to utilize addition string concatenation. Both ways make your code longer and harder to read.


In my examples below I will use an insert SQL query string. The string will be executed using the PDO driver methods.
/* We will use nested arrays and a DB handle, which has already
* been declared. We will assume that the DB already
* exists.
*/

$nestedArrays = arrays(
    array('1','2','3'),
    array('Hello','World','!'),
);

We will use a for loop, which will run until it reaches the end of the main array. Below is the for loop code.
$localDBHandle->beginTransaction();

for ($i = 0; $i < sizeof($nestedArrays); $i++)
{
$localDBHandle->exec("INSERT INTO testDB.tableName (col01,col02,col03)
    VALUES ('$rowData[$i][0]','$rowData[$i][1]','$rowData[$i][2]');");
}

$localDBHandle->commit();

The above string will end up inserting in col01col02 and col03 the values Array[0]Array[1] and Array[2], respectively. Not exactly what you would expect.

You can re-write the query string to use string concatenation.
{
$localDBHandle->exec("INSERT INTO testDB.tableName (col01,col02,col03)
    VALUES ('".$nestedArrays[$i][0]."','".$nestedArrays[$i][1]."','".$nestedArrays[$i][2]."');");
}

But the above adds complexity, it is longer, messier and ugly! Alternatively you can define variables which will hold the date. You will set the variable values inside the loop. Your loop will look like this.
{
$col01 = $nestedArrays[$i][0];
$col02 = $nestedArrays[$i][1];
$col03 = $nestedArrays[$i][2];

$localDBHandle->exec("INSERT INTO testDB.tableName (col01,col02,col03)
    VALUES ('$col01','$col02','$col03');");
}

The query looks much cleaner now, but we have added some memory overhead by defining three new variables. Something we might have to take care of later.

The best way would be to use complex syntax, where you will encapsulate your variable reference inside a set of curly parentheses.
{
$localDBHandle->exec("INSERT INTO testDB.tableName (col01,col02,col03)
    VALUES ('{$rowData[$i][0]}','{$rowData[$i][1]}','{$rowData[$i][2]}');");
}

The last syntax can come handy not just with query strings but also in other ways, i.e. objects or other php elements inside HTML output.

You can view examples of various uses of the complex (curly) syntax on the PHP manual page.

P.S.

If you do not want to use preparebind and execute with your PDO code, use beginTransaction andcommit. This will significantly increase the speed of you interaction with the database.

Popular posts from this blog

Validate Email Address [JavaScript]

Messing with WordPress SPAM Bots

SNOOZ: Sound. Sleep.