PDO fetch returning empty values

Question

I'm pretty new to PHP/SQL and I'm sure I'm making some simple mistake but I've searched for hours and cant figure out where it is. There's no errors being thrown when I test it.

Currently the below code just returns ---| as a result. to clarify the table im trying to get data from has 4 columns: name, room, in, out. it also has 6 rows of data that I can see is there in the database.

I'm trying to get it to return all of the data from the 6 rows that I have stored. Hopefully someone can show me the error of my ways

 <?php

        $db = new PDO('mysql:host=localhost;dbname=dbname','user','pass');

        $sql = "SELECT * FROM table";

        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $stmt = $db->prepare($sql);
        $stmt->execute();

        $error= $stmt->errorInfo();
        echo $error[2];

        while($row = $stmt->fetch(PDO::FETCH_ASSOC));
            {
            echo $row['name']. "-" . $row['room']. "-" . $row['in']. "-" .$row['out'].  "|";
            }
    ?>

UPDATE:

as suggested i removed the while loop and replaced it with

var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

it looks like its able to fetch the results just fine, didnt even have to view the source to see the results but im unsure why the while loop isnt working then. heres the output after replacing the while loop (only showing the result for 1 row, but all 6 rows are identical).

array(4) {
  [0]=>
  array(4) {
    ["name"]=>
    string(4) "john"
    ["room"]=>
    string(4) "room"
    ["in"]=>
    string(1) "5"
    ["out"]=>
    string(2) "11"
  }

Have you checked what the array *is* filled with? is your table actually called table? — Martin, Mar 29 '18 at 15:06
Under `$stmt->execute();` put `$result = $stmt->fetchall();` Then do a `print_r( $result );` and show us the array output. — Ibrahim Hafiji, Mar 29 '18 at 15:11
"it also has 6 rows of data that I can see is there in the database" - Most probably you are using two different servers or two different schemas. — Paul Spiegel, Mar 29 '18 at 15:11
The duplicate reference is wrong. This query didn't fail, since the loop body is executed. — Paul Spiegel, Mar 29 '18 at 15:33

score 0 · Answer 1 · answered Mar 29 '18 at 15:26

the fact that it entered the while loop and gave you ---| proves that there was a result. but it's not rendered for some reason. could be an empty string. or could be that the results include <span style="display:none">foo</span> and that you're viewing the result in a web browser (which is actually rather common when working with PHP), whatever the reason is, replace the while loop with

$stmt->execute();
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

and view PHP's output in something that shows you the raw output (that means, do not view it in a browser that parses it as html. use the View-Source:URL method if you have to view it in a browser. or better yet, use curl from command line.), it should reveal what the actual results was.

I replaced the while loop as you suggested and updated the original post with the results. it looks like its actually getting the data that way, just not sure why my while loop is showing up the way it is? — russell, Mar 29 '18 at 15:53

PDO fetch returning empty values

1 Answers1