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 sting, 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 col01, col02 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 prepare, bind and execute with your PDO code, use beginTransaction andcommit. This will significantly increase the speed of you interaction with the database.
2 responses to “How to use array element references inside query strings? [PHP]”
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?
VEHICLE_TABLE
VEHICLE_NAME | VEHICLE_TYPE
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?
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) { $query = "SELECT VEHICLE_NAME FROM VEHICLE_TABLE WHERE VEHICLE_TYPE = $vehicleType"; ... 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]}"; ... }