speckytwat Posted February 7, 2011 Share Posted February 7, 2011 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? Link to comment https://forums.phpfreaks.com/topic/226965-getting-related-field-as-substitute-using-php-query/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.