Jump to content

Getting related field as substitute using PHP query


speckytwat

Recommended Posts

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:

 

$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.

 

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?

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.