PDO with WHERE... IN queries
Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.
$idlist = array('260','201','221','216','217','169','210','212','213');
$questionmarks = str_repeat("?,", count($idlist)-1) . "?";
$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` IN ($questionmarks)");
and loop to bind the parameters.
PHP - Using PDO with IN clause array
PDO is not good with such things. You need to create a string with placeholders dynamically and insert it into the query, while binding array values the usual way. With positional placeholders it would be like this:
$in = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE my_value IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($in_array);
$data = $stm->fetchAll();
In case there are other placeholders in the query, you could use the following approach (the code is taken from my PDO tutorial):
You could use array_merge()
function to join all the variables into a single array, adding your other variables in the form of arrays, in the order they appear in your query:
$arr = [1,2,3];
$in = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stm = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stm->execute($params);
$data = $stm->fetchAll();
In case you are using named placeholders, the code would be a little more complex, as you have to create a sequence of the named placeholders, e.g. :id0,:id1,:id2
. So the code would be:
// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];
$ids = [1,2,3];
$in = "";
$i = 0; // we are using an external counter
// because the actual array keys could be dangerous
foreach ($ids as $item)
{
$key = ":id".$i++;
$in .= ($in ? "," : "") . $key; // :id0,:id1,:id2
$in_params[$key] = $item; // collecting values into a key-value array
}
$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$in_params)); // just merge two arrays
$data = $stm->fetchAll();
Luckily, for the named placeholders we don't have to follow the strict order, so we can merge our arrays in any order.
Executing Multiple Queries Using PDO
Run your first query which is the insert then after success on that one get the last insertid then use the id on your next query.. Eg.
<?php
try {
$db = new Database(); //Create a new object of type Database establishing a connection to the MySQL database
$query = $db->prepare("INSERT INTO orders (order_type`, `item`, `amount`, `price`, `price_btc`, `status`, `timestamp`, `placed_by`, `secret`, `first_name`, `last_name`, `address_1`, `address_2`, `city`, `zip_code`, `country`, `state`, `phone_number`) VALUES(:order_type, :item, :amount, :price, :price_btc, :status, :timestamp, :placed_by, :secret, :first_name, :last_name, :address_1, :address_2, :city, :zip_code, :country, :state, :phone_number)");
$query->execute(array( /* your values*/ ));
$lastId = $db->lastInsertId(); // fetch last insert id, after success.
$order = $db->prepare("SELECT * FROM `orders` WHERE `ID`=?");
$order->bindValue(1, $lastId);
$order->execute();
//Fetch your records and display.
}
catch (PDOException $e) {
echo "Error : " . $e->getMessage();
}
?>
I left some part of the codes like you did, but the important thing is to run the insert first then collect the last
PHP PDO select like query
You can not bind column names in PDO with the PARAM_STR
type. Ideally, you should not be binding the columns in your query, but if you really want to do so, use the PARAM_INT
data type:
$query = "select * from books where ? LIKE ?";
$result = $db->prepare($query);
$result->bindValue(1, $searchTerm, PDO::PARAM_INT);
$result->bindValue(2, "%$searchValue%", PDO::PARAM_STR);
$result->execute();
php PDO query with several parameters to bind
I do it by passing an array, and using question marks as my place holders
I also recommend working up a set of generic functions or a db handler class that you can simply pass a query and array (or maybe a 2nd array with db connection info) and get back an array with a true or false at element 0 and either an error message at element 1 or data from element 1 on (in the case ofa select).
Here's a snippet of mine, modified to take out all the other handling, but it shows how I paramaterize and prepare the query.
<?php
$query="insert into tablename(first_name,last_name) values(?,?)";
$array=array("John","Doe");
$dbconn = new PDO('mysql:host=' . $hostname . ';port=' . $dbPort . ';dbname=' . $dbName . ';charset=utf8', $username, $password, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
$dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $dbconn->prepare($query);
$result->execute($arr);
if (!$result) {
$retVal[] = false;
$retVal[] = "some sort of error on execute";
$retVal[] = $dbconn->errorInfo();
return;
}
$retVal[] = true;
$retVal[] = $dbconn->lastInsertId();
return;
?>
Conditional query with PDO prepare and bind statement
you can use handy PDO's feature that lets you to send array with parameters straight into execute()
$where = '';
$params = array();
if (isset($parameters['searchTerm'])) {
$where =" And title LIKE :searchTerm";
$params['searchTerm'] = "%$parameters[searchTerm]%";
}
$sql = "Select * from table data Where tableId = 5 $where";
$pdo->prepare($sql)->execute($params);
Note that PHP syntax in your code is also wrong.
PDO lastInsertID() failing due to running multiple queries in a single call
First of all, I would strongly recommend to run every query in a distinct API call. This is how an Application Programming Interface is intended to work.
It won't only prevent situations like this but also will make your code a multitude times more readable and maintainable.
And it will make your code much safer too. You can run multiple statements in a single call only at the expense of the native prepared statements. However virtual this vulnerability is, why taking chances at all?
Why not to make a regular SELECT query instead of SET, get the resulting value into a PHP variable and then use it among other variables, just through a placeholder? I don't see any reason why there should be such a complex way to deal with simple data.
In case I failed to convince you, the reason is simple. You are running two queries, and the first one doesn't trigger any insert ids. And obviously, you need this query's metadata (errors, affected rows, whatever), not the other one's first. So you get it. And to get the second'query's metadata you have to ask a database for it. The process is explained in my article: Treating PHP delusions - The only proper PDO tutorial: Running multiple queries with PDO. Basically PDOStatement::nextRowset()
is what you need.
PDO query in loop
You could completely avoid all of that code by just doing this:
$db->query("UPDATE videos SET vkey = MD5(CONCAT(vkey, 'video'))");
(Or you could do this query in your backend like PHPMyAdmin UPDATE videos SET vkey = MD5(CONCAT(vkey, 'video'))
)
However, if you for some reason want to loop through your database, you could do this:
$sql = "SELECT id FROM videos";
//no reason to use prepare() because you aren't passing variables.
$stmp = $db->query($sql);
$stmp->execute();
$results = $stmp->fetchAll(PDO::FETCH_ASSOC);
//prepare UPDATE query outside of loop, this way you don't send 2 requests to your database for every row
$stmp = $db->prepare("UPDATE videos SET vkey = :vkey WHERE id = :id");
foreach($results as $result) {
$vkey = md5($result['id']."video");
$stmp->execute(
array(
":vkey" => $vkey,
":id" => $result['id']
)
);
}
Also, it's usually a good idea to check the return values inside the loop to make sure there were no errors, you could probably do this by using something like $stmp->rowCount()
to check if there were any rows effected.
Related Topics
Converting MySQL Result Array to Json
Best Way to Completely Destroy a Session - Even If the Browser Is Not Closed
Running Command-Line Application from PHP as Specific User
Merging Arrays With the Same Keys
Preserve Key Order (Stable Sort) When Sorting With PHP'S Uasort
PHP Is Confused When Adding and Concatenating
How to Fix the Permission Error When I Call Session_Start()
How to Access Dynamic Variable Names in Twig
The Accuracy of PHP Float Calculate
How to Detect a Create, Update, Delete Query Is Successful in Codeigniter
PHP File Cannot Enter Some Part of Code
Laravel Migration: Unique Key Is Too Long, Even If Specified
How to Implement a Callback in PHP
Variable-Length Lookbehind-Assertion Alternatives For Regular Expressions