There are (more than) three popular ways to use MySQL from PHP. This outlines some features/differences PHP: Choosing an API: (DEPRECATED) The mysql functions are procedural and use manual escaping. MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements. PDO (PHP Data Objects) is a … Read more
To answer your concerns: MySQL >= 5.1.17 (or >= 5.1.21 for the PREPARE and EXECUTE statements) can use prepared statements in the query cache. So your version of MySQL+PHP can use prepared statements with the query cache. However, make careful note of the caveats for caching query results in the MySQL documentation. There are many … Read more
query runs a standard SQL statement without parameterized data. execute runs a prepared statement which allows you to bind parameters to avoid the need to escape or quote the parameters. execute will also perform better if you are repeating a query multiple times. Example of prepared statements: $sth = $dbh->prepare(‘SELECT name, colour, calories FROM fruit … Read more
Just fetch. only gets one row. So no foreach loop needed 😀 $row = $STH -> fetch(); example (ty northkildonan): $id = 4; $stmt = $dbh->prepare(“SELECT name FROM mytable WHERE id=? LIMIT 1”); $stmt->execute([$id]); $row = $stmt->fetch();
You say this : I never see the final query as it’s sent to the database Well, actually, when using prepared statements, there is no such thing as a “final query“ : First, a statement is sent to the DB, and prepared there The database parses the query, and builds an internal representation of it … Read more
According to documentation you’re correct (http://php.net/manual/en/pdo.connections.php): The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted–you do this by assigning NULL to the variable that holds the object. If you don’t do this explicitly, … Read more
I assume you mean that you want the final SQL query, with parameter values interpolated into it. I understand that this would be useful for debugging, but it is not the way prepared statements work. Parameters are not combined with a prepared statement on the client-side, so PDO should never have access to the query … Read more
So I think I’ll finally answer my own question in order to have a full solution for the record. But have to thank Ben James and Kailash Badu which provided the clues for this. Short Answer As mentioned by Ben James: NO. The full SQL query does not exist on the PHP side, because the … Read more
Multiple Values Insert with PDO Prepared Statements Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts. $datafields = array(‘fielda’, ‘fieldb’, … ); $data[] = array(‘fielda’ => ‘value’, ‘fieldb’ => ‘value’ ….); $data[] = array(‘fielda’ => ‘value’, ‘fieldb’ => ‘value’ ….); more data values or you … Read more
Please be sure to read this answer below, which details ways to mitigate the problems outlined here. The same drawbacks exist using PDO as with any other PHP database interface that does persistent connections: if your script terminates unexpectedly in the middle of database operations, the next request that gets the left over connection will … Read more