Hi, I'm trying to set up a page which first queries for mySQL record results matching a country that the user selects. This works fine, but in the event of there being no records for that country, I want it to look at another field, "Region" and pick the records matching that Region instead.
For example, a user searches for "Australia" but there are no matching records. So, I want it to pick all the records for the region of Australasia, and display records for Australia, New Zealand, Papua New Guinea and so on.
I had created the following:
[PHP]
$query = "SELECT * FROM specialists WHERE Country LIKE '$country' ORDER BY SpecialistName"; // specify the table and field names for the SQL query
//$query .= " limit $s,$limit";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
if ( $numrows == 0 ) {
echo '<p>We don\'t have any results for specialists in countries near to yours at the moment. Please try <a href="specialists.php" style="text-decoration:underline;">searching a different country</a></p>';
}
// get results
$result = mysql_query($query) or die("Couldn't execute query");
// display the results returned
while ($row= mysql_fetch_array($result)) {
$region = $row["Region"];
$count++ ;
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
echo '<table width="600" class="cardisplay"><tr>';
$count = 1 + $s ;
echo $region;
// Build SQL Query
$query2 = "SELECT * FROM specialists WHERE Region LIKE '$region' ORDER BY SpecialistName"; // specify the table and field names for the SQL query
//$query .= " limit $s,$limit";
$numresults=mysql_query($query2);
$numrows=mysql_num_rows($numresults);
// get results
$result = mysql_query($query2) or die("Couldn't execute query");
// display the results returned
while ($row= mysql_fetch_array($result)) {
$title1 = $row["ID"];
$specialistname = $row["SpecialistName"];
$address1 = $row["Address1"];
$address2 = $row["Address2"];
$address3 = $row["Address3"];
$address4 = $row["Address4"];
$address5 = $row["Address5"];
$postcode = $row["Postcode"];
$country = $row["Country"];
$region = $row["Region"];
$website = $row["Website"];
$email = $row["Email"];
$telephone = $row["Telephone"];
$businesstype = $row["BusinessType"];
//followed by echoing out the various data etc. etc.
[/PHP]
But the problem is that $region is always blank / empty in the cases where $query is empty, so I can't pull the value out and therefore $query2 is also empty...
Any ideas?
