My SQL query wont work

Is there something wrong with the Select query below. Im trying to select the last row in the table event_log. And then push this from the server to a page through SSE.

Im getting this error: PHP Fatal error: Uncaught Error: Call to a member function fetch() on boolean

include ('../con/pdo_connect.php');
$query = "SELECT * FROM event_log ORDER BY timestamp DESC LIMIT 1"; 
 $stmt = $conn->prepare($query);

  while(true){
      if ($result = $stmt->execute()) {
          $row = $result->fetch(PDO::FETCH_ASSOC);
           echo "data: " . $row['event_msg'] . "\n\n";
          flush();
     }
     sleep(2);
 }

Why do you have a loop for one result? There is nothing to loop over.

1 Like

That mostly leads to an error within your SQL statement. On your development machine you should let PDO throw exceptions, so you get to see SQL errors

$pdo = new PDO('mysql:host=localhost;dbname=database', 'user', 'pw', [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
1 Like

Especially a loop that isn’t conditional on anything other than true. Might perhaps be better to check whether $stmt is not false.

In any case, isn’t the issue that the OP is calling fetch() on the wrong object? Surely it should be on $stmt?

If the fault was in either prepare() or execute(), the OP would never get to the fetch() because the surrounding if() wouldn’t let it.

The loop is eternal.

I understand the reasoning behind it - the OP is attempting to tail a log using PHP.

Whether it’s effective or not (overall execution timeout i assume has been set to 0, there’s not a concern of server usage, etc).

Do Forever:
  Get Last Row of Table
  Output Last Row of Table to Buffer
  Flush Buffer
  Sleep 2
End Loop

(Now this assumes that during that 2 seconds, a single new row of the log is entered, otherwise you’ll end up outputting the same result again, or missing some data.)

As Chorn points out, this error is due to your SQL query having a fault in it, resulting in stmt_execute returning FALSE. Probably (i’m going to guess now) that you’re using a reserved word (timestamp) as a column name and need to encapsulate it in backticks.

You can throw exceptions, or you can have the tailer echo out $conn->errorInfo (PDO) or $conn->error (MySQLi).

1 Like

If stmt_execute() returns false, how does the code ever execute the fetch() that the error is complaining about?

Good point about it being an ever-updating log display, though with that 2-second sleep in the code, presumably that could result in some missing log entries during that period? A wise thing to have for performance, of course, as long as missing entries aren’t a problem.

It doesnt - that’s WHAT it’s complaining about:

It gets to this line:

$result = false;

false->fetch(…)

Primitive does not have a member function fetch. Throw Error.

Yes, but in order for it to get to fetch(), surely this line has to return true?

if ($result = $stmt->execute()) {

and wouldn’t that return false if there was a problem either preparing or executing the query?

I looked at my similar code earlier on (as I fell into the trap of posting without checking the other day, and it bit me) and I still think the issue is that it should be $stmt->fetch(), not $result->fetch(). execute doc says “Returns TRUE on success or FALSE on failure.”, not a results object.

1 Like

Oh sorry. I see what you’re saying. You’re correct, it doesnt return a reference object it returns a boolean.
Difference between execute and query. I’ll wake up eventually haha.

1 Like

Thank you everyone - after I remove the WHILE loop the connection was made and it works.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.