2

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.

hakre
  • 193,403
  • 52
  • 435
  • 836
TheVDM
  • 43
  • 1
  • 1
  • 4
  • 1
    What does `print_r($dbc->errorInfo());` say? Prob because both tables have a ID, change your `ORDER BY id` – Niels Nov 23 '11 at 11:05
  • print_r($dbc->errorInfo()); outputs "Fatal error: Call to undefined method mysqli::errorInfo()" I have also changed ORDER BY id to ORDER BY blog.id. – TheVDM Nov 23 '11 at 11:13

3 Answers3

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.

GordonM
  • 31,179
  • 15
  • 87
  • 129
  • Hey GordonM, that phrase "depending on error handling" - I'm searching on that and can you elaborate? Working through a similar problem – Oliver Williams May 21 '15 at 13:55
  • When you instantiate a PDO object you can instruct it to optionally throw exceptions when errors occur. I can't remember the exact parameter you have to set but it should be in PHP's documentation regarding PDO's constructor or defined constants. – GordonM May 21 '15 at 14:09
1

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

TheVDM
  • 43
  • 1
  • 1
  • 4
0

Your sql query got an error,

ORDER BY id DESC

should be

ORDER BY comments.blogid  DESC
Toto
  • 89,455
  • 62
  • 89
  • 125