Skip to main content

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(

We will use a for loop, which will run until it reaches the end of the main array. Below is the for loop code.

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]');");


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.


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.


  1. How can I do something like this?
    I want to strore vehicle tax in a mysql variable called VEHICLE_TAX based on the vehicle type. Is it posible?


    Car | 1
    4X4 | 2
    Truck | 3

    $arr_vehicletaxes=array(1=>10, 2=>15, 3=>20);

    $query="SELECT VEHICLE_NAME, {$arr_vehicletaxes[VEHICLE_TYPE]} AS VEHICLE_TAX FROM vehicles ";

    Tried this with no success... Is there a way to acomplish this?

  2. I am not exactly understanding what you are trying to do between what you are saying and what your code is attempting to do. Are you storing new data into the table or are you trying to match data from the table to data from the array?

    If you are trying to store data in the database.

    First, SELECT is used to retrieve data not to store it. You should use UPDATE to update the date in a column of an existing row.

    Second, you are creating a condition in the SELECT statement where you cannot really have a condition.

    Third I will assume that your table has a third column for the vehicle tax called VEHICLE_TAX.

    You need to create a foreach loop to loop through the $arr_vehicletaxes array, and then perform an update, if the element key matches the VEHICLE_TYPE.

    $arr_vehicletaxes = array(1=>10, 2=>15, 3=>20);

    foreach ($arr_vehicletaxes as $vehicleType => $vehicleTax) {
    $query = "UPDATE VEHICLE_TABLE SET VEHICLE_TAX = $vehicleTax WHERE VEHICLE_TYPE = $vehicleType";

    If you are trying to match the two.

    $arr_vehicletaxes = array(1=>10, 2=>15, 3=>20);

    foreach ($arr_vehicletaxes as $vehicleType => $vehicleTax) {

    echo "The tax for a ", $vehicleNameFromResults, "vehicle is ", $vehicleTax;

    I would recommend you not use a generic array key as your way of matching objects. They can be very unreliable because of type casting and overwriting.

    You can either do

    $arr_vehicletaxes = array("Car" => 10, "4x4" => 15, "Truck" => 20);

    or better yet

    $arr_vehicletaxes = array("1|10", "2|15", "3|20");

    Then your code might look like this:

    $arr_vehicletaxes = array("1|10", "2|15", "3|20");

    foreach ($arr_vehicletaxes as $key => $vehicleTax) {
    $codeTax = explode("|", $vehicleTax);
    $query = "UPDATE VEHICLE_TABLE SET VEHICLE_TAX = {$codeTax[1]} WHERE VEHICLE_TYPE = {$codeTax[0]}";


Post a Comment

Popular posts from this blog

Format MAC Address [JavaScript]

I am currently working on a project which will allow users to register their Wi-Fi enabled, non-web browser enabled, devices on the network. These are devices like printers, Apple TV, and Xbox*. One of the data points that have to be collected from the user is the device MAC address. The project customer wants that address to be properly formatted when they see it in the support ticket.

We have several options. We can format the address either on the back-end after the form has been submitted. Or we can format it on the front end via a separate text field for each character pair, but that is too many fields to handle. A better solution is to use a single field and format the user input at the time of input or upon submit. In those cases, the former is better because the data will already be formatted when the overall form input is being validation after the user clicks the “Submit” button.

We are going to format the user input as it is being provided, thus having proper data when vali…

Balloon Flowers of 2014

We have Balloon Flowers (platycodon grandiflorus) in a half barrow pot. They are one of my favorite flowers that we have around the house. Simply because of how the flower forms. It starts as a small ball, which slowly grows into the shape of a hot air balloon, and once it opens it looks like it had exploded.

[gallery type="rectangular" link="file" ids="2611,2612,2622,2615,2613,2614,2616,2617,2618,2619,2610,2620,2621"]

I planted the flowers from the pictures above about seven years, ago.

SNOOZ: Sound. Sleep.

I was on the Snooz Kickstarter page today, revisiting the project’s details and timeline progress. The device, which is a white-noise generating machine, recently became available for pre-order. Snooz is meant to be an alternative to having a TV or a stationary fan running at bed time. In my case, I bought it for my wife so we can get the TV out of the bedroom.

Whilst on the comments page I read the following comment, posted recently by Ken Tran:

I'm not a fan of the new fabric design. ...
I must disagree. If you take a look at the original design, which is all plastic and no style, I must say it is fugly. It is all utilitarian - not something I rally want to look at or see when I walk into the room. The final design, seen on the right, is something that beacons to be explored, looked at, utilized.

I am a big fan of Bang & Olufsen. They use fabric from Kvadrat in their line of products. Incorporating soft materials like fabric or leather in something which is meant to be place…