PDO query returns result, but i cant fetch it

Question

Hello im trying out PDO and im stucked in a weird situation.

$statement->fetch()

returns me the desired results, but when i try to fetch em , while doesnt seem to work. I tried use $statement->fetchAll() with foreach but it doesnt work either.

I also tried fetch(PDO::FETCH_ASSOC) but again no luck. AM i doing something wrong ?

$pdoObject = new PDO("mysql:host=$dbhost;dbname=$dbname;", $dbuser, $dbpass);
$pdoObject -> exec("set names utf8");
$sql="SELECT * from prokiriksi_clean where name=:name";
$statement = $pdoObject->prepare($sql);
$statement->execute(array(':name' =>$clean));
$testyo=$statement->fetch();
var_dump($testyo); //returns array

if ($statement->fetch()){
  echo 'inside if'; //it is printed
  while ($record =  $statement->fetch()) {
  var_dump($record); //doesnt print
  echo 'inside while'; //doesnt print
  }
}

score 2 · Accepted Answer · answered Oct 29 '15 at 16:45

The problem is that you don't save the results of your first $statement->fetch(), so you skip the first row of your results. Given the way you have written your query, it looks like you only have one row, so you have nothing to loop over after that first row. Thus, the while never runs.

Remove the if and just do the while ($record = $statement->fetch()) loop:

while ($record =  $statement->fetch()) {
    var_dump($record);
    echo 'inside while';
}

If you need to verify that you have any results before doing the loop, you can use the procedure described in the documentation or simply use a counter to see if the loop ran, like this:

$rows = 0;
while ($record =  $statement->fetch()) {
    var_dump($record);
    echo 'inside while';
    $rows++;
}
if ($rows) {
    echo "Processed $rows rows\n";
} else {
    echo "Oops! No data!\n";
}

oh god, i never thought that the cursor would move with the `$statement->fetch()` .. — IseNgaRt, Oct 29 '15 at 16:54
@IseNgaRt Yep. You *technically* can reset the cursor with creative uses of [`fetch()`](http://php.net/manual/en/pdostatement.fetch.php) (see the `$offset` parameter), but you're better off not doing that for lots of reasons. — elixenide, Oct 29 '15 at 16:59

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

When doing your if statement, you're fetching the result of your query, so you cannot fetch it a second time. Check this thread to perform multiple fetches: PDO multiple fetching of same query

edited May 23 '17 at 11:51

Community

1
1

answered Oct 29 '15 at 16:45

LeCoco

61
1
1
5

PDO query returns result, but i cant fetch it

2 Answers2