SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 68
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count records and display

    Hi all

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

    Code PHP:
    // 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.

    Code PHP:
    	// selects reviews from venue
    	$sql1 = "SELECT comments,
    	DATE_FORMAT(review_date, '%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?
    The more you learn.... the more you learn there is more to learn.

  2. #2
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code PHP:
    <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?

    Code PHP:
    	// 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);

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

    as above
    Code PHP:
    // selects venue reviews
    $sql1 = "SELECT comments,
    	DATE_FORMAT(review_date, '%M %D %Y') AS subdir,
    	publisher, rating FROM tbl_reviews...
    while (..

    Thanks
    The more you learn.... the more you learn there is more to learn.

  4. #4
    SitePoint Zealot Jaanboy's Avatar
    Join Date
    Sep 2007
    Location
    UK
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm thinking that you may be able to use a right join to do this. Something like:

    PHP Code:
    $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

  5. #5
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    The more you learn.... the more you learn there is more to learn.

  7. #7
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok thanks Shrapnel_N5 and thanks again Jaanboy (welcome to sitepoint)

    Just don't quite understand.

    Code MySQL:
    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
    The more you learn.... the more you learn there is more to learn.

  10. #10
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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) :

    Code PHP:
    // selects reviews from venue
    	$sql1 = "SELECT comments,
    	DATE_FORMAT(review_date, '%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:

    Code PHP:
    <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 ?>

    ....
    Code PHP:
    <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
    The more you learn.... the more you learn there is more to learn.

  11. #11
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to print the venue name/id and its number of reviews, will this way using sub query help you?
    PHP Code:
    $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.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  12. #12
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  13. #13
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    subqueries are always worst than joins because of index use

  14. #14
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    The more you learn.... the more you learn there is more to learn.

  15. #15
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  16. #16
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    The more you learn.... the more you learn there is more to learn.

  17. #17
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you spot the problem? Errors above.

    Code PHP:
    	$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?
    The more you learn.... the more you learn there is more to learn.

  18. #18
    SitePoint Zealot Jaanboy's Avatar
    Join Date
    Sep 2007
    Location
    UK
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by computerbarry View Post
    Can you spot the problem? Errors above.
    I think the GROUP BY clause needs moving to the end of the query.

  19. #19
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    The more you learn.... the more you learn there is more to learn.

  20. #20
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  21. #21
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Getting there... thanks Shrapnel_N5 looking good!
    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?

    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

    bars
    venue1
    venue2
    clubs
    venue3
    venue4
    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
    The more you learn.... the more you learn there is more to learn.

  22. #22
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't see connection between category and venue_id.
    how this line of code would help you with splitting?

  23. #23
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code MySQL:
    $sql = "SELECT venue_id,
    		photo_sml,
    		main_txt,
    		website,
    		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?
    The more you learn.... the more you learn there is more to learn.

  24. #24
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just add ORDER BY category_id to your query
    and put these titles in PHP

  25. #25
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ... ORDER BY category_id GROUP BY tbl_ ...

    and put these titles in PHP
    Is this what you mean?
    $row3['bars']=$row3['category_id'];
    $row3['clubs']=$row3['category_id'];
    ..
    $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?
    Code PHP:
    		<h3>choose your venues</h3>
    		<? if($num_rows2): ?>
    		<? foreach($DATA1 as $row3): ?>
    			<ul>
    				<li><a href="venue/<?=$row3['url']?>"><?=$row3['title']?></a> <?=$row3['review_count']?></li>
    			</ul>
    		<? endforeach ?>

    Thanks
    The more you learn.... the more you learn there is more to learn.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •