Count records and display

same field names must be preceeded with table name to avoid being ambigous
SELECT tbl_venues.venue_id

to make an easy name for count, use an alias
"SELECT tbl_venues.venue_id, , count(review_id) as review_count FROM…

Getting there… thanks Shrapnel_N5 looking good! :smiley:

to make an easy name for count, use an alias
"SELECT tbl_venues.venue_id, , count(review_id) as review_count FROM…
Thanks, I’ve just added <?=$row3[‘review_count’]?> where I need it, is this what you meant? :slight_smile:

Ok working so far, but if I bring back in:
WHERE venue_id=‘" . str_replace(“-”," ",mysql_real_escape_string(strtolower($_GET[‘venue_id’]))) . "’ it gives an error?

This is basically to return only the venues from that category being shown, I’d also like split this list into separate category’s as below.

select your venue







and so on…

Another problem is: the review count shows ok, but some reviews aren’t shown because they haven’t been ‘confirmed’ yet, but the count still counts them, so how do I only count reviews that have been confirmed?

Thanks again all, much appreciated :cool:

I can’t see connection between category and venue_id.
how this line of code would help you with splitting?

Yes sorry I’m referring to another page that shows the venues from a certain category, which then links to the venue page(what where working on) but the same concept anyway.

tbl_venues has a column called ‘category_id’ which as you can guess is used to select the venues from that category, like below:

$sql = "SELECT venue_id,
		address FROM tbl_venues WHERE category_id='" .mysql_real_escape_string($_GET['category_id'])."'";

Don’t get confused, this is a separate snippet on a different page I’m just showing how I’ve been using category_id else where.

So, how do I add the category_id to our current snippet and instead of showing a big list of the venues, break the list up into the separate category_id’s with a header (as shown in the example above).

bars, clubs, restaurants = different category_id’s

Making sense? :slight_smile:

… ORDER BY category_id GROUP BY tbl_ …

and put these titles in PHP

Is this what you mean?

$row3[‘title’]=str_replace(“-”," “,(ucwords($row3[‘venue_id’])));
$row3[‘url’]=str_replace(” “,”-",(strtolower(ucwords($row3[‘venue_id’]))));

If so, how do I replace/add to the existing list?

		<h3>choose your venues</h3>
		<? if($num_rows2): ?>
		<? foreach($DATA1 as $row3): ?>
				<li><a href="venue/<?=$row3['url']?>"><?=$row3['title']?></a> <?=$row3['review_count']?></li>
		<? endforeach ?>

Thanks :cool:

you have to add some conditions to this code.
to output category title only if it was changed

:slight_smile: what sort of conditions? your probably getting feed up with this thread now Shrapnel_N5, I do appreciate the help thanks

sort of :slight_smile:
it looks like your task far beyond your knowledge
And I don’t feel like to write entire application for you

a long day :slight_smile: if you can just get us started, and I’ll see if I can finish the rest myself, maybe continue tomorrow if I have any problems with a fresh head :cool:

$arrVenues = array(
	,array('category_id'=>21,'category'=>'Something Else','venue'=>'venue45','venue_id'=>324)
	,array('category_id'=>48,'category'=>'Something Else 2','venue'=>'venue12','venue_id'=>1213)

$intCategory = null;
foreach($arrVenues as $arrVenue) {

	if($intCategory != $arrVenue['category_id']) {
		if($intCategory !== null) echo '</ul></div>';
		echo '<div><h2>',$arrVenue['category'],'</h2><ul>';
		$intCategory = $arrVenue['category_id'];

	echo '<li>',$arrVenue['venue'],'</li>';

Thanks oddz

So what do the numbers mean and exactly whats happening here? :slight_smile:

array(‘category_id’=>45 ?

That would be a fake result set to replaced by the an actual result set.

The rest of code is used to iterate the result set and output valid html without missing closing or opening elements.

When the loop begins no category exists which is identified by $intCategory being null.

Upon iteration the last category is checked for equality with the current rows category. When the category is not the same the new category is printed out and the previous div and ul is closed. After which the current category is set to that rows category and the venue is printed out. The first line in the condition makes sure that the first row never has closing elements printed out. If the item is first nothing is before it, thus no need to close an element that doesn’t exist.

Cheers oddz, Ill try an digest this tomorrow, bit late me now thanks again :slight_smile:

Are you about Shrapnel_N5. What do you think off oddz post?


venue1 (does he mean type in all the venues manual? What do the numbers at the end mean?)

Cheers CB :slight_smile:

It is merely an example. To understand and devise further.
He wrote this array to substitute one you getting from the database.
So, you just have to use your own instead of oddz’s one.

I see. I’ll try and get my head around it, thanks again :slight_smile:

I hope you sorted things out.
But just to be sure.
You need ONE query to gel ALL venues. Without all these trims, str_replaces and other ridiculous stuff. Without even WHERE clause.
One plain and simple query that lists all venues. Ordered by category.
Once you get all it’s rows into array, you can use oddz’s code to output it in the way you want it. I didn’t check this code yet but it looks like usable one

Is this what you mean?

$arrVenues = ("SELECT category_id, venue_id FROM tbl_venues ORDER BY category_id", ARRAY_A);

So how do I can display the output then below?

<? if($num_rows4): ?>
<? foreach($DATA3 as $row4): ?>
<? endforeach ?>

Any help or direction would be great, thanks :slight_smile:

your ***_id always confuse me. because term id used in the mysql for the numeric identifiers.

but if you have all necessary data with this query, you can use oddz’s code now for output it.

Thanks Shrapnel_N5, “…always confuse me” everyone has there own way :slight_smile:

$arrVenues = ("SELECT category_id, venue_id FROM tbl_venues ORDER BY category_id", ARRAY_A);
$intCategory = null;
foreach($arrVenues as $arrVenue) {

    if($intCategory != $arrVenue['category_id']) {
        if($intCategory !== null) echo '</ul></div>';
        echo '<div><h2>',$arrVenue['category'],'</h2><ul>';
        $intCategory = $arrVenue['category_id'];

    echo '<li>',$arrVenue['venue'],'</li>';

I’ll give this a try and get back to you :cool: