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());
// selects venue reviews
$sql1 = "SELECT comments,
DATE_FORMAT(review_date, '%M %D %Y') AS subdir,
publisher, rating FROM tbl_reviews...