How To Get A Where Filter Into A Select Query?

[kjm7267]

Good evening from rainy Palo Alto, where I'm stuck on what should be a simple problem. 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 modify the code to 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 what I have so far:

 

<?

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=<?echo $id;?>"><?

echo $star.$property.'</a><br><br>';

}

?>

 

Thank you very much for considering this!

[fenway]

Why not add this to a WHERE clause?