Fetch result not working

I am in the process of trying to figure out how PDO_MySQL works, and converting my mysql queries over to it.

Currently, I have the following code to check if rows were returned in a query:
if ($result->num_rows > 0) {

I tried changing this over to PDO by using the following references:

$sql=“select * from tableCategories;”;
$result = $pdo->query($sql);
$result->setFetchMode(PDO::FETCH_ASSOC);

if ($result->fetchColumn() > 0) {

while($row = $result->fetch()) {

This does retrieve the data needed, however the first row of data is not captured. I am fairly certain it is related to the IF statement, but I don’t know why. Why is this? I tried changing the value of 0 to -1, but that didn’t help.

(Note: I have stripped the page specific code in between all the references – there is data that displays in between the IF and the WHILE, so both are needed).

Checking for results like that is redundant and not necessary. If there are results, they are going to show.

I think I figured it out. The $result->setFetchMode(PDO::FETCH_ASSOC); was messing it up. I was working off a tutorial online and it had included this line even though it was retrieving and displaying all the rows. That seems like an error in their code.

Once I removed this line, it all seems to work fine now.

If you understood how while works it would be clear there is no need to check if there are results. From the manual…

The meaning of a while statement is simple. It tells PHP to execute the nested statement(s) repeatedly, as long as the while expression evaluates to TRUE .
http://php.net/manual/en/control-structures.while.php

For PDO, you don’t need the while loop at all.

$resp = $result->fetchAll();

$resp now holds the result set in an array.

1 Like

Thanks Benanamen. There was a reason at the time I had both the IF and the WHILE… but I can’t figure out why. I have moved things around in the code now as I can definitely see the redundancy you have noted.

I think I do though in my case. For every row of a data, I am outputting a result into a table row. I don’t see how the above would work for this.

I have also been trying to understand the “proper PDO” guide, and it suggests using foreach instead of while such as the example below. I’m a little confused now which would be the most optimal way to go.

$stmt = $pdo->query('SELECT name FROM users');
foreach ($stmt as $row)
  {    
  echo $row['name'] . "\n";
}

This is where variable naming comes into play.

So, $stmt stands for statement.

$pdo = new PDO(/..../);
$stmt = $pdo->prepare('SELECT id, firstname, lastname FROM members WHERE lastname = ?');
$results = $stmt->execute([$_POST['lastname']]);

foreach($results as $r) {
    echo "<a href='members/{$r['id']}'>{$r['lastname']}, {$r['firstname']}</a><br>";
}

Not the best practice, but shows an example. Ideally, this would just return JSON data for the front end to deal with like this,

class Members {
    private $pdo;

    public function __construct() {
        $this->pdo = new PDO(/..../);
    }

    public function searchByLastname($lastname) {
        $stmt = $this->pdo->prepare('SELECT id, firstname, lastname FROM members WHERE lastname = ?');
        $results = $stmt->execute([$lastname]);       
        echo json_encode($results);        
   }
}
Sponsor our Newsletter | Privacy Policy | Terms of Service