How to Rewind a Pdo Result

Is it possible to rewind a PDO result?

I'm pretty sure this is database dependent. Because of that, it is something you should try to avoid. However, I think you can achieve what you want by enabling buffered queries. If that doesn't work, you can always pull the result into an array with fetchAll. Both solutions have implications for your applications performance, so think twice about it, if the resultsets are large.

Is it possible to rewind a PDO result?

I'm pretty sure this is database dependent. Because of that, it is something you should try to avoid. However, I think you can achieve what you want by enabling buffered queries. If that doesn't work, you can always pull the result into an array with fetchAll. Both solutions have implications for your applications performance, so think twice about it, if the resultsets are large.

How can I rewind a pdo result?

Could be as simple as getting all the results on the 1st iteration and loop on the 2nd:

$res = array();
while( $results = $query->fetch() ){
   $res[] = $results;
   // instructions
}

foreach($res as $results){
   // instructions
}

Can't make PDO rewind work with PDO::FETCH_ORI_ABS

There is no mysql_data_seek in PDO and the current mysql-php connections do not support cursors. What you should do instead is pull all your data into an array and iterate over that.

$rows = $rowSetAssistantsProject->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row) {
    // Process rows
}

// Where you would have reset mysql_data_seek back to 0 if it existed

foreach($rows as $row) {
    // More row processing
}

Once your data is in array form you can place a cursor on the array using the reset function like this post does. But the code above is what I have had to do since I moved to PDO. Hope that helps.

Resetting array pointer in PDO results

Save your results to an array and then loop that array twice.

$pdo = new PDO('mysql:host=' . $host . ';dbname='.$database, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('SELECT * FROM mytable WHERE active = 1 ORDER BY name ASC');
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();

$rows = $stmt->fetchAll();

foreach ($rows as $r) {
    // first run
}

foreach ($rows as $r) {
    // seconds run
}

Reuse PDO result set multiple times

You can only loop through the PDO statement once, so you should put the result in a temporary array.

Instead of:

$dataTypes = $dataQuery->rowCount() ? $dataQuery : [];

Do

$dataTypes = $dataQuery->fetchAll(\PDO::FETCH_ASSOC);

How to fetch results from a PDO prepare statement

Your result variable (besides being overwritten) is not what you think it is. It's a PDO statement.

Try this instead:

$stmt = $this->con->prepare("select * from ".$table_name." where username = :user_email");

$stmt->execute([
    ':user_email' => $user_email,
]);

if (false !== ($row = $stmt->fetchColumn()))
{
    return $row;
}

However, this will only return the first column of the first row. Instead you probably want:

 return $stmt->fetchAll(PDO::FETCH_ASSOC);

I changed $result to $stmt, because it's not a result. It's a Statement Object.

Original issues

In your original code (see below) you are overwriting it with the return from execute which is a Boolean.

// Old code (don't use this)
$result = $result->execute([
    ':user_email' => $user_email,
]);

//$result = TRUE|FALSE

if ($result->fetchColumn() !== false)
{
    return $result;
}

And then you try to call a method of the Boolean, which, well, won't work. But the problems are deeper than just that. Let’s assume you don’t overwrite it.

// Old code (don't use this)
$result->execute([
    ':user_email' => $user_email,
]);

//$result = PDOStatment object.

if ($result->fetchColumn() !== false)
{
    return $result;
}

Now the result is still your PDOStatement, which is good, but as I said, you're not saving the fetched data. This time you return the PDOStatement object. Which is not what you want.

Then further, as I stated earlier, if you do save it and return it, it's still probably not what you are after. Because fetchColumn() accesses only one row at a time and only one column.

But I have no way to know what you want. Maybe that is what you want? In that case your query is less than ideal. Maybe you just want to see if a user exists with a given email? In that case I would use this query.

$result = $this->con->prepare("SELECT id FROM ".$table_name." WHERE username = :user_email");

$result->execute([
    ':user_email' => $user_email,
]);
// There isn't any need to check it (see below)
return $result->fetchColumn();

PDOStatement::fetchColumn() returns a single column from the next row of a result set or FALSE if there are no more rows.

I can also tell by your stuff, that your database setup is probably wrong. That is, if you really need a dynamic table $table. The reason I can say this is you should not be duplicating any user data (or any data really, this is called normalization), and having the table dynamic implies that the email may exist separately in two (or more) tables.

If that is not the case then don't make it dynamic. Why is this an issue? Well, think what happens if a user changes their "email" now, because it exists in two tables (potentially). You'll have to update it in both places. But it's worse than that as it overcomplicates anything you do with the emails.

Without seeing the schema for your tables, I can only speculate on that, and how to fix it. But generally you would use a foreign key and associate the user record to that. Then using a JOIN you can access the email without duplication.

That said, there are a few cases where this may be acceptable, but I have no way to know if that is true in your case. Just a quick example would be a separate table for users and administrators (basically two-user systems).

Security

The last thing is be very very careful with this:

"select * from ".$table_name." where username = :user_email"

The issue here is it's open to SQL injection. Anytime you concatenate a variable into SQL, you open the door for injection attacks. Well, you may say I'm passing in a canned string account. Which is OK, but there is no validation at the point of failure. So maybe in five months you reuse this code and forget that you never validated the table name. Maybe not, but the fact remains that if user data could get into that argument, you have no protection whatsoever against injection on the table name. The possibility for it is there.

Something as simple as this:

  public function verify_user($table_name,$user_email){
     $allowed = ['account','users'];
     if(!in_array($table_name, $allowed )) throw new Exception('Invalid table name');
 }

See now it's virtually impossible to inject something into the table name. Further because it's in the same method (at the point of failure), you will never lose that protection. It's very easy to be in a rush latter and copy a piece of code change a few things and .... well you know.

Just my two cents.

UPDATE

So even if the chance is small that user input could get into $table you can not guarantee it 100%, because from within verify_user you have no way to know where the data came from, but you are trusting on faith that it's not user input. When it comes to SQL injection, you can't say well this is OK, because I will only call this method a certain way. It has to be 100% injection proof or as close as is humanly possible.

Why is this important, you ask? Imagine this.

   $userprofileobj->verify_user('account --',$_SESSION['user_email']);

Those two little --s are like // in PHP, but for SQL, they comment out the rest of the line in SQL so your query becomes this.

"select * from account -- where username = :user_email"

Or (essentially)

"select * from account"

So we just modified what your query does. Now thankfully it's not really possible to run two queries at once in PDO. You can do it (with some work) in MySqli. But because of security reasons, they have mostly done away with this ability. The reason is this (or worse like creating database users).

  $userprofileobj->verify_user('account; DROP TABLE account --',$_SESSION['user_email']);

Which, if you could do two queries, would do this:

 SELECT * FROM account
 DROP TABLE account

In any case this is dangerous stuff and is to be avoided at all costs. Being too lazy (and I am a lazy programmer, so don't take that wrong) to put a table name in is not an answer you want to give after your database has been compromised and you have exposed user data to a third party. It's just not an option.

All this does:

if(!in_array($table_name, ['table1', 'table2', ...])) throw new Exception('Invalid table name');

Is throw an error if "needle" $table_name is not in "haystack" - a canned list of table names. So if I do this (using our example above):

if(!in_array('account --', ['table1', 'table2', ...])) throw new Exception('Invalid table name');

It won't find account -- in our list of table1 and table2 and will blow up, thus preventing the injection attack.

How to fetch 2 times in MYSQL PDO without FETCHALL

I take that back looks like you can use the cursor orientation contants to select the result... sample code coming... I havent tried this so you may need to play a bit. This is also based on the assumption that a PDO::FETCH_ORI_FIRST acts like a data_seek and leaves the cursor on the first position as opposed to returning it to whatever it was before.

$stmt = $pdo->prepare('SELECT id FROM table', array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();

$first = $pdo->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_FIRST);
$first_row = $first['id'];

// other stuff

// first iteration we rewind to the first record;
$cursor = PDO::FETCH_ORI_FIRST;

while (false !== ($row = $stmt->fetch(PDO::FETCH_ASSOC, $cursor))) {
   $id = $row['id'];
   // successive iterations we hit the "next" record
   $cursor = PDO::FETCH_ORI_NEXT; 
   echo $id;
}

---

I dont think you can rewind a statement... Assuming these blocks arent seprated by a bunch of intermediary logic id just do it in the loop.

$STH->setFetchMode(PDO::FETCH_COLUMN); // no need to pull an array
$count = 0;
while ($id = $STH->fetch()) {      
  if($count === 0) {
   $first_row = $id;
  }
  echo $id;
  $count++;
}

---

Related Topics