Stuck Selecting For A Field With mySQL and PHP

Good morning from cloudy Palo Alto, where I’m puzzled. Two websites are powered by a single mySQL database. One website provides restaurant reviews, and the other provides golf course reviews. The database has two tables: “Properties” and “Reviews”. The Properties table has a field called “Type”, which for each record is either “restaurant” or “golf course”.

This code below succesfully generates a list of links to individual property subpages – restaurants and golf courses together – into three successive groups:

  1. Properties reviewed and starred
  2. Properties reviewed but not starred
  3. Properties neither reviewed nor starred

What’s needed, is to add a select for type, so that the list only includes restaurants. But for some reason, I can’t seem to be able to do this. Here’s the code:

<?
mysql_connect(“ipaddress”, “username”, “password”) or die(mysql_error());
@mysql_select_db(“databasename”) or die( “Unable to select database”);
$query=“SELECT p.id
, p.star
, p.property
FROM properties AS p
LEFT OUTER
JOIN ( SELECT DISTINCT link
FROM reviews ) AS r
ON r.link = p.id
ORDER
BY p.star DESC
, CASE WHEN r.link IS NULL
THEN ‘last’
ELSE ‘first’ END
, p.property”;
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$id = $row[‘id’];
$star = (‘’ === $row[‘star’]) ? ‘’ : ‘<img src=“star.jpg”>’;
$property = $row[‘property’];
?><a href=“http://www.nameofwebsite.com/property.php?id=&lt;?echo $id;?>”><?
echo $star.$property.‘</a><br><br>’;
}
?>

Thank you very much for considering this!

add this to your query –

WHERE p.type = 'restaurant'
Show <a href="?show=restaurant">restaurants</a> or <a href="?show=golf">golf courses</a>
<?
$filter="";
if (isset($_GET['show'])) {
  if ($_GET['show']=='restaurant') $filter="WHERE p.type = 'restaurant'";
  if ($_GET['show']=='golf') $filter="WHERE p.type = 'golf course'";
} 
$query="SELECT p.id
, p.star
, p.property
FROM properties AS p
LEFT OUTER
JOIN ( SELECT DISTINCT link
FROM reviews ) AS r
ON r.link = p.id
$filter
ORDER
BY p.star DESC
, CASE WHEN r.link IS NULL
THEN 'last'
ELSE 'first' END
, p.property"; 
?>

Please r937, where exactly would you insert that line? This, for example, returns an error:

<?
mysql_connect(“ipaddress”, “username”, “password”) or die(mysql_error());
@mysql_select_db(“databasename”) or die( “Unable to select database”);
$query=“SELECT p.id
, p.star
, p.property
WHERE p.type = ‘restaurant’
FROM properties AS p
LEFT OUTER
JOIN ( SELECT DISTINCT link
FROM reviews ) AS r
ON r.link = p.id
ORDER
BY p.star DESC
, CASE WHEN r.link IS NULL
THEN ‘last’
ELSE ‘first’ END
, p.property”;
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$id = $row[‘id’];
$star = (‘’ === $row[‘star’]) ? ‘’ : ‘<img src=“star.jpg”>’;
$property = $row[‘property’];
?><a href=“http://www.nameofwebsite.com/property.php?id=&lt;?echo $id;?>”><?
echo $star.$property.‘</a><br><br>’;
}
?>

dear kjm, if you’re going to build sites with a database backend, you simply ~must~ become a little more acquainted with SQL, at least to the point where you know the syntactic sequence of clauses within the SQL statement

alternatively, for every site you work on, for every program that uses SQL, you will have to come to a forum and beg someone to hand you the answer (like shrapnel did about twenty minutes before your last post)

Yes, Shrapnel_N5’s suggestion is terrific. Thank you Shrapnel_N5! But I have to assume that
WHERE p.type = ‘restaurant’
could somehow be inserted into my original code in such a way that it does the job.