Help with search system...

MySQL database
#1

Ok so I made this code that is searching thru a database with names and such, and if you enter a name like john and age 20, but there is no john that is 20 in the database it shows all the johns and so on with age if there is no john it shows all the people that are 20, well I think you get it.

BUT, if all fields aren’t filled in with something it shows all the persons in the database, so I would need a little bit of help…

This is the code:

[code]if(isset($_POST[‘screenname’])) {$a = mysql_query(“SELECT * FROM Persons WHERE ScreenName LIKE ‘%$_POST[screenname]%’”); }
if(isset($_POST[‘firstname’])) {$b = mysql_query(“SELECT * FROM Persons WHERE Firstname LIKE ‘%$_POST[firstname]%’”); }
if(isset($_POST[‘lastname’])) {$c = mysql_query(“SELECT * FROM Persons WHERE LastName LIKE ‘%$_POST[lastname]%’”); }
if(isset($_POST[‘age’])) {$d = mysql_query(“SELECT * FROM Persons WHERE Age LIKE ‘%$_POST[age]%’”); }
if(isset($_POST[‘country’])) {$e = mysql_query(“SELECT * FROM Persons WHERE Country LIKE ‘%$_POST[country]%’”); }
if(isset($_POST[‘other’])) {$f = mysql_query(“SELECT * FROM Persons WHERE Other LIKE ‘%$_POST[other]%’”); }

while($rowa = mysql_fetch_array($a))
{
echo $rowa[‘ScreenName’] . " " . $rowa[‘FirstName’] . " " . $rowa[‘LastName’]. " " . $rowa[‘Age’]. " " . $rowa[‘Country’]. " " . $rowa[‘Other’];
echo “
”;
}

while($rowb = mysql_fetch_array($b))
{
echo $rowb[‘ScreenName’] . " " . $rowb[‘FirstName’] . " " . $rowb[‘LastName’]. " " . $rowb[‘Age’]. " " . $rowb[‘Country’]. " " . $rowb[‘Other’];
echo “
”;
}

while($rowc = mysql_fetch_array($c))
{
echo $rowc[‘ScreenName’] . " " . $rowc[‘FirstName’] . " " . $rowc[‘LastName’]. " " . $rowc[‘Age’]. " " . $rowc[‘Country’]. " " . $rowc[‘Other’];
echo “
”;
}

while($rowd = mysql_fetch_array($d))
{
echo $rowd[‘ScreenName’] . " " . $rowd[‘FirstName’] . " " . $rowd[‘LastName’]. " " . $rowd[‘Age’]. " " . $rowd[‘Country’]. " " . $rowd[‘Other’];
echo “
”;
}

while($rowe = mysql_fetch_array($e))
{
echo $rowe[‘ScreenName’] . " " . $rowe[‘FirstName’] . " " . $rowe[‘LastName’]. " " . $rowe[‘Age’]. " " . $rowe[‘Country’]. " " . $rowe[‘Other’];
echo “
”;
}

while($rowf = mysql_fetch_array($f))
{
echo $rowf[‘ScreenName’] . " " . $rowf[‘FirstName’] . " " . $rowf[‘LastName’]. " " . $rowf[‘Age’]. " " . $rowf[‘Country’]. " " . $rowf[‘Other’];;
echo “
”;
}[/code]

#2

Looks like with your code you may have duplicate results displayed - because you make separate query for each of 6 search criteria. Why not combine all your queries into one? Like this:

[php]

<?php $criteria = ""; if(isset($_POST['screenname'])) $criteria.=($criteria==""?"":" OR ")."ScreenName LIKE '%".mysql_real_escape_string($_POST['screenname'])."%'"; if(isset($_POST['firstname'])) $criteria.=($criteria==""?"":" OR ")."FirstName LIKE '%".mysql_real_escape_string($_POST['firstname'])."%'"; if(isset($_POST['lastname'])) $criteria.=($criteria==""?"":" OR ")."LastName LIKE '%".mysql_real_escape_string($_POST['lastname'])."%'"; if(isset($_POST['age'])) $criteria.=($criteria==""?"":" OR ")."Age LIKE '%".mysql_real_escape_string($_POST['age'])."%'"; if(isset($_POST['country'])) $criteria.=($criteria==""?"":" OR ")."Country LIKE '%".mysql_real_escape_string($_POST['country'])."%'"; if(isset($_POST['other'])) $criteria.=($criteria==""?"":" OR ")."Other LIKE '%".mysql_real_escape_string($_POST['other'])."%'"; if($criteria!=""){ $a = mysql_query("SELECT * FROM Persons WHERE ".$criteria); } else{ die('Please specify search criteria!'); } ?>

[/php]

#3

Well I tried that, but it does not show any results… :confused:

I just copied the code… Or should I add something to it?

Sorry, I’m not to good with PHP…

#4

You also need to add output of query results below, just like you do in your code:

[php]
while($rowa = mysql_fetch_array($a))
{
echo $rowa[‘ScreenName’] . " " . $rowa[‘FirstName’] . " " . $rowa[‘LastName’]. " " . $rowa[‘Age’]. " " . $rowa[‘Country’]. " " . $rowa[‘Other’];
echo “
”;
}
[/php]

#5

Aha, ok will try it.

Thanks.

EDIT:
God dammit, it still shows all the persons in the database if a field is left blank.

#6

If all fields are left blank - script should now say: Please specify search criteria!
If at least one of fields is filled in ($criteria variable is not empty), then your query will return only results matching the criteria. Depending on criteria entered, it is still possible that all records returned - you need to analyze your data in the Persons table.

God dammit, it still shows all the persons in the database if a field is left blank.
What field do you mean?
#7

I would also suggest to check form fields like this:

[php]
if($_POST[‘screenname’]!=’’) $criteria.=($criteria==""?"":" OR “).“ScreenName LIKE '%”.mysql_real_escape_string($_POST[‘screenname’]).”%’";
[/php]

So, instead of checking variable with isset() function, you need to check if that variable is empty or not. You’ll need to do this for each of fields.

#8

Well here is the html files that sends the data to the PHP file:

Search

Search

Enter any info you know about the person you want to search for.

Screen Name:
First Name:
Last Name:
Age:
Country:
Other info:

and if any field is left blank, the search shows all the people in the database. :confused:

Sponsor our Newsletter | Privacy Policy | Terms of Service