PHP PDO Query returns last row

Question

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.

You're reseting the variable $names each time you loop, try $names .= "... — PhilS, Mar 17 '17 at 10:26
you got you answer below, one more thing I will recommend you to use fetchall and then use loop to show results rather using fetch in while loop. In this case you can improve your code performance and best practice. — Mubashar Iqbal, Mar 17 '17 at 10:31
Possible duplicate of [How to combine two strings together in PHP?](https://stackoverflow.com/questions/8336858/how-to-combine-two-strings-together-in-php) — mickmackusa, Jun 13 '17 at 03:31

score 1 · Accepted Answer · answered Mar 17 '17 at 10:26

1

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>";

answered Mar 17 '17 at 10:26

Sahil Gulati

15,028
4
24
42

Thank you very much Sahil, can't believe I over looked that '.' – Cal Brown Mar 17 '17 at 10:28

score 0 · Answer 2 · edited Mar 17 '17 at 10:55

0

Use the .= operator instead of =

edited Mar 17 '17 at 10:55

Michelangelo

5,888
5
31
50

answered Mar 17 '17 at 10:27

Jozeph B.

26
4

score 0 · Answer 3 · answered Mar 17 '17 at 11:22

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.

PHP PDO Query returns last row

3 Answers3