Jump to content

Archived

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

celestineweb

Retrieving info from MYSQL database

Recommended Posts


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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

×

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.