Searching mySQL database using PHP

[bodycount]

I have got the basic search elements working, but what is happening is this, if you don't enter any search criteria into any of the search fields the search returns all records, which i don't want to happen, if nothing is entered in to the search field I don't want any thing displayed. Below is a snippet of the code I am using

<table>
<tr><td align=right>Search Birth Name :</td><td> <input type="text" name="FULLNAME"></td></tr>
<tr><td align=right>Search Married Name :</td><td> <input type="text" name="MARRIED_NAME"></td></tr>
<tr><td><input type="submit" name="submit"></td></tr>
</table>
</form>
</center>

<?php
include ("connection.php");

$FULLNAME = htmlspecialchars($FULLNAME);
$MARRIED_NAME = htmlspecialchars($MARRIED_NAME);
$query = "SELECT * FROM ADDRESSES WHERE FULLNAME LIKE \"%$FULLNAME%\" LIMIT 200";
$query2 = "SELECT * FROM ADDRESSES WHERE MARRIED_NAME LIKE \"%$MARRIED_NAME%\" LIMIT 200";

[Anyango]

$FULLNAME = htmlspecialchars($FULLNAME);
$MARRIED_NAME = htmlspecialchars($MARRIED_NAME);
if ($FULLNAME!="" && $MARRIED_NAME!="")
{
$query = "SELECT * FROM ADDRESSES WHERE FULLNAME LIKE \"%$FULLNAME%\" LIMIT 200";
$query2 = "SELECT * FROM ADDRESSES WHERE MARRIED_NAME LIKE \"%$MARRIED_NAME%\" LIMIT 200";
}

// that if statement will check for values in both variables $MARRIED_NAME and $FULLNAME and if any of them or both of them are empty, it will not run that query.

[bodycount]

Thanks for that.

If i wanted to do a search on just one of the fields how would that be done

[Anyango]

You are most welcome.

Could you explain your question a bit please.

[bodycount]

Yep no problem

What I wanted to do is either enter just one search or into both search entires

Birth Name : LEFT BLANK
Married Name : LEFT BLANK

No Record Displayed
-------------------------------
or

Birth Name : Ellen Glover
Married Name : LEFT BLANK

returns records
-------------------------------
or

Birth Name : Ellen Glover
Married Name : Ellen

returns records

I also so want to do a search on Year Of Birth , which I would use a drop down list.

<select><option>$YOB</option></select> or somting like that how would i do this?

[bodycount]

I have sorted my first problem out, but i cant figure out how to creat a pull down list which gets all it option's from a field in my database.

[Anyango]

That's nice you figured it out. creation of drop down list from database entries is simple, i am actually short on time today that 's why i am pasting one of my codes for you, it does exactly what you want, just the query and field values are different, you can just simply change query and values as your environment and it will make you drop down box as you said.

$sQuery="SELECT articleid,articletitle FROM article where domainid='$domainid'";
$sResult=mysql_query($sQuery) or die (mysql_error());
$sRecordCount=mysql_num_rows($sResult);

echo '<select name=article>';
echo '<option value=0></option>';
for($i=0;$i<$sRecordCount;$i++)
{
$sRecordSet=mysql_fetch_assoc($sResult);
$articleid=$sRecordSet['articleid'];
$articletitle=$sRecordSet['articletitle'];
echo "<option value=$articleid>$articletitle</option>";
}
echo '</select>';

Please leme know if you can't get it to work

[bodycount]

I have manged to get a drop down menu to work(well it list the right data from the database) but when I try and do a seach using the drop down menu no record are returned, It is proberly some noobish thing i have done or not dont.

<?php

include ("connection.php");

?>

<center>
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
<table>
<tr><td align=right>Search Birth Name :</td><td> <input type="text" name="FULLNAME"></td></tr>
<tr><td align=right>Search Married Name :</td><td> <input type="text" name="MARRIED_NAME"></td></tr>
<tr><td align=right>Search Birth Year : </td><td>
<?php

$query5 = "SELECT DISTINCT YOB FROM ADDRESSES ORDER BY YOB";

echo "<select name=\"YOB\">";

$result5 = mysql_query($query5);
echo "<OPTION SELECTED>$YOB";

while ($row=mysql_fetch_array($result5))

{
$YOB=$row["YOB"];
echo "<option>$YOB";
}
echo "</select>";
?>

</td></tr>
<tr><td align=right>Search Place of Birth :</td><td> <input type="text" name="PLACEBIRTH"></td></tr>
<tr><td><input type="submit" name="submit"></td></tr>

</table>

</form>

</center>

<?php

//include ("connection.php");

$FULLNAME = htmlspecialchars($FULLNAME);
$MARRIED_NAME = htmlspecialchars($MARRIED_NAME);
$YOB = htmlspecialchars($YOB);
$PACEBIRTH = htmlspecialchars($PLACEBIRTH);

// FULLNAME search start

if ($FULLNAME!="" )

{

$query = "SELECT * FROM ADDRESSES WHERE FULLNAME LIKE \"%$FULLNAME%\" LIMIT 200";

$result = mysql_query($query);

if($num=mysql_numrows($result))

{

echo "<table align=\"center\" border=\"8\">";
echo "<tr><th colspan=4><b><center>Birth Name</th></tr>";
echo "<tr><th>Birth Name</th><th>Married Name</th><th>Year Of Birth</th><th>Place of Brith</th></tr>";
while ($record = mysql_fetch_array($result))
{
$FULLNAME = $record["FULLNAME"];
$MARRIED_NAME1 = $record["MARRIED_NAME"];
$YOB1 = $record["YOB"];
$PLACEBIRTH1 = $record["PLACEBIRTH"];

echo "<tr><td><font size=-1><center>$FULLNAME</td><td><font size=-1><center>$MARRIED_NAME1</td><td><font size=-1><center>$YOB1</td><td><font size=-1><center>$PLACEBIRTH1</td></tr>";

}
}
}
// FULLNAME search END

// MARRIED_NAME search start

if ( $MARRIED_NAME!="")

{

$query2 = "SELECT * FROM ADDRESSES WHERE MARRIED_NAME LIKE \"%$MARRIED_NAME%\" LIMIT 200";
$result2 = mysql_query($query2);

if($num=mysql_numrows($result2))

{
echo "<table align=\"center\" border=\"8\">";
echo "<tr><th colspan=4><b><center>Married Name</th></tr>";
echo "<tr><th>Birth Name</th><th>Married Name</th><th>Year Of Birth</th><th>Place of Brith</th></tr>";
while ($record = mysql_fetch_array($result2))
{

$FULLNAME = $record["FULLNAME"];
$MARRIED_NAME2 = $record["MARRIED_NAME"];
$YOB2 = $record["YOB"];
$PLACEBIRTH2 = $record["PLACEBIRTH"];

echo "<tr><td><font size=-1><center>$FULLNAME</td><td><font size=-1><center>$MARRIED_NAME2</td><td><font size=-1><center>$YOB2</td><td><font size=-1><center>$PLACEBIRTH2</td></tr>";

}
}
}

// MARRIED_NAME search finish

// YOB search start

if ($YOB!="" )

{

$query3 = "SELECT * FROM ADDRESSES WHERE YOB LIKE \"%$YOB%\" LIMIT 200";

$result3 = mysql_query($query3);

if($num=mysql_numrows($result5))

{
echo "<table align=\"center\" border=\"8\">";

echo "<tr><th colspan=4><b><center>Birth Year</th></tr>";
echo "<tr><th>Birth Name</th><th>Married Name</th><th>Year Of Birth</th><th>Place of Brith</th></tr>";

while ($record = mysql_fetch_array($result5))
{

$FULLNAME = $record["FULLNAME"];
$MARRIED_NAME = $record["MARRIED_NAME"];
$YOB = $record["YOB"];
$PLACEBIRTH3 = $record["PLACEBIRTH"];

echo "<tr><td><font size=-1><center>$FULLNAME</td><td><font size=-1><center>$MARRIED_NAME</td><td><font size=-1><center>$YOB</td><td><font size=-1><center>$PLACEBIRTH3</td></tr>";

}
}
}
// YOB search END

// PLACEBIRTH search start

if ($PLACEBIRTH!="" )

{

$query4 = "SELECT * FROM ADDRESSES WHERE PLACEBIRTH LIKE \"%$PLACEBIRTH%\" LIMIT 200";

$result4 = mysql_query($query4);

if($num=mysql_numrows($result4))

{
echo "<table align=\"center\" border=\"8\">";

echo "<tr><th colspan=4><b><center>Place of Birth</th></tr>";
echo "<tr><th>Birth Name</th><th>Married Name</th><th>Year Of Birth</th><th>Place of Brith</th></tr>";

while ($record = mysql_fetch_array($result4))
{

$FULLNAME4 = $record["FULLNAME"];
$MARRIED_NAME4 = $record["MARRIED_NAME"];
$YOB4 = $record["YOB"];
$PLACEBIRTH4 = $record["PLACEBIRTH"];

echo "<tr><td><font size=-1><center>$FULLNAME4</td><td><font size=-1><center>$MARRIED_NAME4</td><td><font size=-1><center>$YOB4</td><td><font size=-1><center>$PLACEBIRTH4</td></tr>";

}
}
}

?>

[bodycount]

What I have noticed with the pull down menu is that the last option, is always the one searched for and not the one that you have selected e.g last option is 1789 and I select 1901 i brings back all records with 1789 instead.