Count records and display

computerbarry · February 1, 2010, 11:35am

Hi all

How can I count the number of records relating to a certain ID, then show the records.

// selects venue list
	$sql3 = "SELECT venue_id FROM tbl_venues"; 
	$result3 = mysql_query($sql3) or die(mysql_error());
	$DATA1=array();
        while ($row3 = mysql_fetch_array($result3, MYSQL_ASSOC)) {
		$row3['title']=str_replace("-"," ",(ucwords($row3['venue_id'])));
		$row3['url']=str_replace(" ","-",(strtolower(ucwords($row3['venue_id']))));
		$DATA1[]=$row3;
		}
        $num_rows2=mysql_num_rows($result3);

I also have a foreach loop which outputs:

venue 1
venue 2
venue 3
…

I have another table called tbl_reviews, which I query on the same page I want to query this table along with the above and show how many reviews there are for that venue_id.

	// selects reviews from venue
	$sql1 = "SELECT comments,
	DATE_FORMAT(review_date, '&#37;M %D %Y') AS subdir,
	publisher, rating FROM tbl_reviews WHERE venue_id='" . str_replace("-"," ",mysql_real_escape_string(strtolower($_GET['venue_id']))) . "' AND confirmed=0 ORDER BY review_date DESC";

Do I just need to create a temporary key and echo this along side the above some how?

venue 1 (24)
venue 2 (62)
…

What is the simplest way?

system · February 1, 2010, 11:56am

Do you really need these comments on this page? Or just number of them?
Anyway, it must be done with one query, using JOIN

Something like
SELECT venue_id, count(review_id) FROM tbl_venues LEFT JOIN tbl_reviews ON tbl_venues.venue_id=tbl_reviews.venue_id

computerbarry · February 1, 2010, 12:28pm

Thanks Shrapnel_N5, just read the post.

Think I just need a simple COUNT() of venue_id on tbl_reviews so I can then echo num_reviews_for_venue as below:

<li><a href="venue/<?=$row3['url']?>"><?=$row3['title']?></a> (<?=$row1['num_reviews_for_venue']?>)</li>

Do you really need these comments on this page? Or just number of them?
The comments will be shown underneath the venue and all it’s details, yes I need the comments and the number of comments left for each individual venue, if that makes sense.

Will it speed things up if I create a JOIN, or will 2 SELECT statements be just as quick? I have a total of 3 SELECTs on this page.

Every SELECT is referring to venue_id, venue_id on tbl_reviews is the FK, review_id is just a auto int.

The first and main SELECT only returns 1 result, the second & third return a number of records, so maybe the JOIN you suggest is the best way?

	// select venue details
	$sql = "SELECT venue_id, venue_slug, meta_kwords, website, address,  FROM tbl_venues WHERE venue_id='" . str_replace("-"," ",mysql_real_escape_string(strtolower($_GET['venue_id']))) . "'"; 
	$result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result);

// selects venue list
	$sql3 = "SELECT venue_id FROM tbl_venues"; 
	$result3 = mysql_query($sql3) or die(mysql_error());
while (...

as above

// selects venue reviews
$sql1 = "SELECT comments,
	DATE_FORMAT(review_date, '&#37;M %D %Y') AS subdir,
	publisher, rating FROM tbl_reviews...
while (..

Thanks:cool:

Jaanboy · February 1, 2010, 12:46pm

I’m thinking that you may be able to use a right join to do this. Something like:

$sql1 = '
	SELECT v.title, v.url, r.comments, DATE_FORMAT(r.review_date, "%M %D %Y") AS subdir, r.publisher, r.rating
	FROM tbl_reviews r
		RIGHT JOIN tbl_venues v
		ON r.venue_id = v.venue_id
	WHERE
		v.venue_id = "' . str_replace('-', ' ', mysql_real_escape_string(strtolower($_GET['venue_id']))) . '"
		AND r.confirmed = 0
	ORDER BY r.review_date DESC
';

Don’t quote me on that though

system · February 1, 2010, 12:50pm

Now I hope I understand.

Correct me if I’m wrong.
This is venue details page.
You’re show particular venue details, it’s comments, and list of other venues - right?
And also list of all venues?
And want to add number of comments to this list?
Yes, this join query is what you want.

computerbarry · February 1, 2010, 12:55pm

Thanks Jaanboy

Now I hope I understand.
exactly right Shrapnel_N5

This is venue details page. yes
You’re show particular venue details, it’s comments, and list of other venues - right? yes
And also list of all venues? yes
And want to add number of comments to this list? yes
Yes, this join query is what you want. Yours or Jaanboy’s join?

I would also like to split this venue list into the separate categories, but one problem at a time.

system · February 1, 2010, 1:17pm

Jaanboy’s join does something different. Getting both venue details and comments with one query. It will have venue details in each row. I doubt it is useful.

system · February 1, 2010, 1:37pm

I forgot to add GROUP BY operator
SELECT venue_id, count(review_id) FROM tbl_venues LEFT JOIN tbl_reviews ON tbl_venues.venue_id=tbl_reviews.venue_id GRUOP BY tbl_venues.venue_id

computerbarry · February 1, 2010, 1:43pm

Ok thanks Shrapnel_N5 and thanks again Jaanboy (welcome to sitepoint)

Just don’t quite understand.

SELECT venue_id, count(review_id) FROM tbl_venues LEFT JOIN tbl_reviews ON tbl_venues.venue_id=tbl_reviews.venue_id GRUOP BY tbl_venues.venue_id

Are you saying add another SELECT to what I have already just so I can count the reviews? Totaling 4 SELECT’s? Also as I said above, I’m not counting the review_id but venue_id on tbl_reviews (that’s how I know how many reviews I have for that venue).

Or do you mean combining the 2nd & 3rd SELECT together and fit the above in somewhere?

Thanks

computerbarry · February 1, 2010, 1:53pm

Is it ok to say leave the first SELECT that’s selecting a single venue thats ok?

The rest of Code (how does the JOIN fit into the below) :

// selects reviews from venue
	$sql1 = "SELECT comments,
	DATE_FORMAT(review_date, '&#37;M %D %Y') AS subdir,
	publisher, rating FROM tbl_reviews WHERE venue_id='" . str_replace("-"," ",mysql_real_escape_string(strtolower($_GET['venue_id']))) . "' AND confirmed=0 ORDER BY review_date DESC"; 
		
	$result1 = mysql_query($sql1) or die(mysql_error());
	$DATA=array();
    while ($row1 = mysql_fetch_array($result1, MYSQL_ASSOC)) {
	$row1['publisher']=ucwords(strtolower($row1['publisher']));
	$DATA[]=$row1;
	}
    $num_rows=mysql_num_rows($result1);
	
	// selects venue list
	$sql3 = "SELECT venue_id FROM tbl_venues"; 
	$result3 = mysql_query($sql3) or die(mysql_error());
	$DATA1=array();
        while ($row3 = mysql_fetch_array($result3, MYSQL_ASSOC)) {
		$row3['title']=str_replace("-"," ",(ucwords($row3['venue_id'])));
		$row3['url']=str_replace(" ","-",(strtolower(ucwords($row3['venue_id']))));
		$DATA1[]=$row3;
		}
        $num_rows2=mysql_num_rows($result3);

<html>

showing on the page:

<h3>reviews</h3>
<? if($num_rows): ?>
<? foreach($DATA as $row1): 
$bg = ($bg=='odd' ? 'even' : 'odd'); ?>
        <ul class="reviews <? echo $bg ?>">
			<li><?=$row1['comments']?></li>
			<li><span class="fR"><strong>Rating:</strong> <?=$row1['rating']?>/10</span><strong>by:</strong> <?=$row1['publisher']?>, <strong><?=$row1['subdir']?></strong></li>
		</ul>
<? endforeach ?>
<? else: ?>
 <p>No reviews found</p>
<? endif ?>

…


<h3>choose your venue</h3>
		<? if($num_rows2): ?>
		<? foreach($DATA1 as $row3): ?>
			<ul>
				<li><a href="venue/<?=$row3['url']?>"><?=$row3['title']?></a></li>
			</ul>
		<? endforeach ?>
		
		<? else: ?>
		 <p>No data found</p>
		<? endif ?>

Thanks

Raju_Gautam · February 1, 2010, 2:30pm

Just to print the venue name/id and its number of reviews, will this way using sub query help you?


$sql = "SELECT 
			a.venue_id,
			(SELECT COUNT(venue_id) AS total_reviews tbl_reviews WHERE venue_id=a.venue_id) AS total_reviews
		FROM 
			tbl_venues AS a";
$result = mysql_query($sql) or die(mysql_error());
while($rows = mysql_fetch_object($result)){
	echo $rows->venue_id . ' (' . $rows->total_reviews . ')';
}

Haven’t tested the code but it should work.

system · February 1, 2010, 2:43pm

computerbarry, you need to replace your query that returns only venues, with query that returns venues and number of comments I gave you

Try to think of what you want.
Do you want to add number to the venues? So, replace query that you’re using for listing venues
isn’t it obvious?

system · February 1, 2010, 2:44pm

subqueries are always worst than joins because of index use

computerbarry · February 1, 2010, 2:56pm

Yes I understand now thanks I’ll see if I can put this together.

isn’t it obvious?
to you maybe but we’re not all php guru’s Shrapnel_N5

Raju_Gautam · February 1, 2010, 3:03pm

I know using sub queries in most cases is not good but as far as I know if the number of records are much in this case just to count the records would not make any issue.

computerbarry · February 1, 2010, 3:24pm

subqueries are always worst than joins because of index use
venue_id in tbl_reviews is an index.

Anyway. Error: Column ‘venue_id’ in field list is ambiguous ??

if I add - WHERE venue_id=‘" . str_replace(“-”," ",mysql_real_escape_string(strtolower($_GET[‘venue_id’]))) . "’"; to the end

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE venue_id=‘some venue’’ at line 1

Thanks:cool:

system · February 1, 2010, 6:21pm

just add ORDER BY category_id to your query
and put these titles in PHP

computerbarry · February 1, 2010, 3:48pm

Can you spot the problem? Errors above.

	$sql3 = "SELECT venue_id, count(review_id) FROM tbl_venues LEFT JOIN tbl_reviews ON tbl_venues.venue_id=tbl_reviews.venue_id GROUP BY tbl_venues.venue_id WHERE venue_id='" . str_replace("-"," ",mysql_real_escape_string(strtolower($_GET['venue_id']))) . "'";

And how do I output the no. of reviews?

Jaanboy · February 1, 2010, 3:57pm

I think the GROUP BY clause needs moving to the end of the query.

computerbarry · February 1, 2010, 4:03pm

Yes I’ve also tried:

$sql3 = “SELECT venue_id, count(review_id) FROM tbl_venues LEFT JOIN tbl_reviews ON tbl_venues.venue_id=tbl_reviews.venue_id GROUP BY tbl_venues.venue_id”;

Error: Column ‘venue_id’ in field list is ambiguous

And still gives an error, is that what you mean?