Asked  7 Months ago    Answers:  5   Viewed   53 times

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

Thanks!

 Answers

92

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 
Wednesday, March 31, 2021
 
Terry
answered 7 Months ago
64

It seems that PDO::MYSQL_ATTR_FOUND_ROWS is a mysql connection option. Thus, it works only as PDO connection option as well. So, set it up this way

$opt  = array(
    PDO::MYSQL_ATTR_FOUND_ROWS   => TRUE,
    // you may wish to set other options as well
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);
$this->_db = new PDO($dsn,DB_USER,DB_PASS,$opt);
Wednesday, March 31, 2021
 
shivam
answered 7 Months ago
29

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.

Wednesday, August 18, 2021
 
derobert
answered 2 Months ago
67

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 http://php.net/manual/en/function.mysql-fetch-array.php for more details.

Monday, October 18, 2021
 
Carter Pape
answered 6 Days ago
61

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);
Tuesday, October 19, 2021
 
BenOfTheNorth
answered 5 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :