CODEWITHSUNDEEP
phpmysql
TheVDM
TheVDM

Reputation: 43

PHP/MySQL Select and count matching records

I have the following code for selecting records from a mysql database and loading the blog posts, what I would like to do is count the number of matching records for b_id and id and still display the blog posts.

$result = $dbc->prepare("SELECT id, title, post, date, time, blog, b_id, name, comment FROM blog ORDER BY id DESC LIMIT $start_blog , $blog_per_page");
$result->execute(); 
$result->bind_result($id, $title, $post, $date, $time, $blog, $b_id, $name, $comment);
while ($row = $result->fetch()) {
    Code to show the blog posts
    Show number of comments on the post i.e. Comments (2)
}

I must be having a blank moment as I am sure it's something fairly simple, but whenever I use the code

count ( b_id ) WHERE b_id = id

the blog posts aren't shown (i.e. its counting the matches and not sending the blog post data.

Hope this makes sense

Regards Jim

Upvotes: 0

Views: 376

Answers (2)

TheVDM
TheVDM

Reputation: 43

Ok, after much trial and error I managed to do this with a left join. 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

Upvotes: 0

Anonymous
Anonymous

Reputation: 3689

It might make more sense to outsource the b_id with its data into another table, but that depends on what you are trying to achieve.

Anyway to answer your question correctly, you could either do 2 seperate queries (one with the count, the other with all the data) or just let php handle it:

$count = 0; 
if($b_id == $id) { $count++; }

Upvotes: 1

Related Questions