Fatal Error: Call to a member function execute() on a non-object with MySQL Left Join in PHP

Question

I am having some trouble with a left join in a simple blog script I am writing to go with my basic CMS.

$result = $dbc->prepare("SELECT blog.id, blog.title, blog.post, blog.date, blog.time, comments.blogid FROM blog LEFT JOIN comments ON blog.id = comments.blogid ORDER BY id DESC LIMIT $start_blog, $blog_per_page");
$result->execute();
$result->bind_result($id, $title, $post, $date, $time, $blogid);

The above code is returning the following error: Fatal error: Call to a member function execute() on a non-object in C:\xampp\htdocs\pcms\includes\blog.php on line 56 (with line 56 being $result ->execute();)

I just cant see what is causing the error, if I remove the LEFT JOIN the SQL code is working fine.

Solution 1

From the PHP manual on the prepare function:

If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling).

The reason you're getting a fatal error is because you're not checking to see if prepare was successful. Never assume that a method that might fail was successful.

The reason why your call to prepare failed is because you're using the wrong syntax for parameter binding. It should be ? (for unnamed parameters) or :variable_name (for named parameters). $variable_name doesn't work.

Solution 2

Ok, after much trial and error I managed to get the correct left join query. Here is the code if anybody finds it of use or interest.

$result = $dbc->prepare("SELECT blog.id, blog.title, blog.post, blog.date, blog.time, count(blog_comment.b_id) CommCount FROM blog LEFT JOIN blog_comment ON blog.id = blog_comment.b_id GROUP by blog.id ORDER BY id DESC LIMIT $start_blog , $blog_per_page");
$result->execute(); 
$result->bind_result($id, $title, $post, $date, $time, $b_id);
while ($row = $result->fetch()) {
  //Code to show blog posts, using $b_id to display the number of comments
}

Many thanks for the help and input, it all added up to finding the solution that I craved!!

Jim