Retrieving info from MYSQL database

[celestineweb]

Hi
Search form has three values it submits to my php script. It searches the database and produces results found.

I want to check that each value submitted by the form actually exists in the database and if not throw you back to search form.


<?
$username="******";
$password="*****";
$database="******";

//Retreive data from form
$catagory = $_POST['catagory'];
$town = $_POST['town'];
$country = $_POST['country'];

//Error check form input
if ($town=="")
{
echo "You must enter a town near you for an accurate search - Press back button to try again";
}

if ($country=="")
{
echo "Please enter the country you wish to search in - Press back button to try again";
}

//trim
trim ($catagory || $town || $country);

//Test input for debugging only
echo $catagory;
echo $town;
echo $country;

//Connect to database
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

//query database
$query=("SELECT * FROM listings WHERE
therapist_catagory='$catagory'and
town='$town' and
country='$country'");
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();


echo "<b><center><h1>Search Results</h1></center></b><br><hr><br>";

$i=0;
while ($i < $num) {

$first_name=mysql_result($result,$i,"first_name");
$last_name=mysql_result($result,$i,"last_name");
$company_name =mysql_result($result,$i,"company_name");
$phone_number=mysql_result($result,$i,"phone_number");
$mobile=mysql_result($result,$i,"mobile");
$email=mysql_result($result,$i,"email");
$therapist_catagory =mysql_result($result,$i,"therapist_catagory");
$website =mysql_result($result,$i,"website");
$town =mysql_result($result,$i,"town");
$country=mysql_result($result,$i,"country");
$description=mysql_result($result,$i,"description");
$email_repeat=mysql_result($result,$i,"email_repeat");

//error checking
//if ($therapist_catagory!=$catagory)
//{
//echo "we do not have a listing for this catagory yet - please press back button to make another selection";
//}
?>
<link href="/css_files/wolo.css" rel="stylesheet" type="text/css">

<table width="70%" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#000000">
<tr>
<td width="38%"><h3 align="left">Name</h3></td>
<td width="62%"><p align="left"><? echo "$first_name $last_name" ?></p></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Company Name</h3></td>
<td width="62%"><p align="left"><? echo "$company_name" ?></p></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Phone Number</h3></td>
<td width="62%"><p align="left"><? echo "$phone_number" ?></p></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Mobile</h3></td>
<td width="62%"><p align="left"><? echo "$mobile" ?></p></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Email</h3></td>
<td width="62%"><a href="mailto:<? echo "$email"?>"><p><? echo "$email"?></p></a></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Therapist Catagory</h3></td>
<td width="62%"><p align="left"><? echo "$therapist_catagory" ?></p></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Website Address</h3></td>
<td width="62%"><a href="<? echo $website;?>"><p><? echo "$website"?></p></a></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Town</h3></td>
<td width="62%"><p align="left"><? echo "$town" ?></p></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Country</h3></td>
<td width="62%"><p align="left"><? echo "$country" ?></p></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Description Of Service Offered</h3></td>
<td width="62%"><p align="left"><? echo "$description" ?></p></td>
</tr>
<tr>
<td width="38%"><h3 align="left">Email Confirmation</h3></td>
<td width="62%"><a href="mailto:<? echo "$email_repeat"?>"><p><? echo "$email_repeat"?></p></a></td>
</tr>
</table
><br><hr><br>
<?
$i++;
}

?>

Database fields (reproduced here as best I can):-
Field Type Null Default
id int(7) Yes NULL
first_name varchar(50) Yes NULL
last_name varchar(50) Yes NULL
company_name varchar(50) Yes NULL
phone_number varchar(11) Yes NULL
mobile varchar(11) Yes NULL
email varchar(50) Yes NULL
therapist_catagory varchar(50) Yes NULL
website varchar(50) Yes NULL
town varchar(50) Yes NULL
country varchar(50) Yes NULL
description longtext Yes NULL
email_repeat varchar(50) Yes NULL

Any suggestions greatfullt recieved.

[ober]

All you have to do is run a seperate query for each with the field in the WHERE clause. Then run mysql_num_rows on it and if you get a value more than 0, then something exists matching that input. After each query, set a boolean variable depending on the result of the mysql_num_rows(). If it's false at any point (meaning one of your mysql_num_rows() results was 0, then skip the rest of the processing and show the form again.

[celestineweb]

Thanks for pointing me in the right direction I had gone round this problem so many time I could not see the wood for the trees.
Changed Query section to this:-

//$result=mysql_query($query);
$query="SELECT * FROM listings WHERE therapist_catagory='$catagory'";
$result=mysql_query($query);
$num=mysql_numrows($result);
echo "num=$num";
//error checking catagory
if ($num<=0)
{
echo "we do not have a listing for this catagory yet - please press back button to make another selection";
exit;
}

$query="SELECT * FROM listings WHERE town='$town'";
$result=mysql_query($query);
$num=mysql_numrows($result);
//error checking town
if ($num<=0)
{
echo "we do not have a listing for this catagory yet - please press back button to make another selection";
exit;
}

$query="SELECT * FROM listings WHERE country='$country'";
$result=mysql_query($query);
$num=mysql_numrows($result);
//error checking country
if ($num<=0)
{
echo "we do not have a listing for this catagory yet - please press back button to make another selection";
exit;
}


and it does everything I want it to. Will change the echo "we do not..... to link to an error page later when I build the site around this database.

[celestineweb]

On further checking if I put in the search:-

Catagory=A
Town=B
Country=C

It finds records that equal A, B & C but it also finds records with A & C where be = something else but it includes this record because it hase something in the field and nurrows is greater than 0.

Do I have to live with this or is there a better solution than the one I found?