Fetch result not working

MySQL database
#1

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).

#2

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

#3

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.

#4

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

#5

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
#6

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.

#7

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";
}
#8

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