CODEWITHSUNDEEP
phphtml
Cal Brown
Cal Brown

Reputation: 137

PHP PDO Query returns last row

I have the following code, which I've used a variation of elsewhere on my project and it works everywhere else, but not in this case.

    $pdo = new PDO('sqlsrv:server=localhost;Database=db', 'root', 'password');
$getResultsInst = $pdo->query("SELECT distinct [names], [id] FROM [tblStaff] ORDER BY [names] asc");

while ($result = $getResultsInst->fetch(PDO::FETCH_ASSOC)) {
    $names = "<tr>
            <td>".$result['names']."</td>
            <td>Test</td>
            </tr>";
}

When I run that query in SQL Server management studio is returns a lot more than 1 row, but when I run this code in the HTML...

<table class="table table-bordered">
<thead>
  <tr>
    <th>Name</th>
    <th>ID</th>
  </tr>
</thead>
<tbody>
  <?php echo $names;?>
</tbody>

It just returns the last row of the SQL query, I can't figure out why, as I've used this while loop elsewhere and it creates a new row for each fetched result.

Upvotes: 0

Views: 880

Answers (3)

Manngo
Manngo

Reputation: 16311

It’s not PDO that’s causing the problem — it’s the assignment. You’re re-assigning the same variable to something else, so that when it’s finished it only has the last value.

You really should be accumulating the result.

Rather than repeated string concatenation I have always found it easiest and most maintainable to us an interim array, and then join the string at the end.

While you’re at it, you really should take advantage of the fact that a PDOStatement object (the result of a PDO::query() function call) is iterable: you can use it in a PHP foreach.

Here is a possible alternative:

<?php   
    //  …

    $names=array();
    foreach($getResultsInst as $result) {
        $names[]="<tr><td>{$result['names']}</td><td>Test</td></tr>";
    }
    $names=implode('',$names);
?>

The braces {} around the variable are there because of a quirk in PHP's handling of arrays inside an interpolated string.

Upvotes: 0

Jozeph B.
Jozeph B.

Reputation: 36

Use the .= operator instead of =

Upvotes: 0

Sahil Gulati
Sahil Gulati

Reputation: 15141

Change this $names = to $names .= You are overriding value every time.

$names = "<tr>
            <td>".$result['names']."</td>
            <td>Test</td>
            </tr>";

to

$names .= "<tr>
            <td>".$result['names']."</td>
            <td>Test</td>
            </tr>";

Upvotes: 1

Related Questions