Asked  2 Years ago    Answers:  5   Viewed   213 times

Which is the equivalent of mysql_data_seek using pdo objects? Can you give me an example?




The usual answer is: do your data seek directly in the array PDOStatement::fetchAll... But it is WRONG IF the query fetches a lot of data (!).

There are 2 real solutions,

1) if database permits use PDO::FETCH_ORI_ABS or PDO::FETCH_ORI_REL, example,

$result = $sth->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, 973);

(EDIT) But, as commented by @ChoiZ, have a PDO-MySQL limitation: "MySQL does not support cursors" (outside stored programs) "and the driver cannot emulate them for you"... Try later or with MySQL's forks, like MariaDB.

2) use the database solution (a kind of pagination). Example:

SELECT a, b FROM table LIMIT 1, 973 
Tuesday, November 15, 2022

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.
mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.
PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.

* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

Tuesday, November 1, 2022

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Monday, October 10, 2022

I think you are looking for:

while($row = $stmt->fetch(/* PDO::FETCH_ASSOC */)) {
    // do loop stuff

PDO::fetchAll() returns an associative array of all of the query results (a 2-D array). This is not recommended for large result sets according to the PHP docs. PDO::fetch() returns just one row from a result set and mimics mysql_fetch_array(). See for more details.

Friday, August 12, 2022

There are two different character sets at issue:

  • the encoding in which MySQL assumes strings are sent by the client (character_set_client); and
  • the encoding in which MySQL will send its responses (character_set_results).

To ascertain the current value of these variables using PDO, you could fetch the results of the relevant SHOW VARIABLES statement; for example:

$qry = $db->query("SHOW VARIABLES LIKE 'character_set_client'");

The documentation for mysql_client_encoding() is somewhat ambiguous, as it states:

Retrieves the character_set variable from MySQL.

However, no such server system variable exists: so I'm not sure which it would return.

Finally, rather than setting a MYSQL_ATTR_INIT_COMMAND, you can specify your desired character set in the DSN (as mentioned in the manual):

$db = new PDO("mysql:dbname=$db;host=$host;charset=$charset", $user, $password);
Monday, August 8, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :

Browse Other Code Languages