Querying mysql database based on values from html form

cutegurl985

I want to build a simple form for querying database having business organization info. The form has four fields name, category, city and state. Users should be able to search based on any of these fields.

My form is as follows:

<tbody>
  <tr>
    <td>Enter Company Name:</td>
  </tr>
  <tr>
    <td ><input name="name"></td>
  </tr>
  <tr>
    <td >
    <select name="category">
    <option value="*" selected="selected">Select

a category</option>
<option value=""></option>
<option value="497-5100">497-5100</option>
<option value="911 Emergency Phones">911
Emergency Phones</option>
<option value="A">A</option>
<option value="Accordion Doors">Accordion
Doors</option>
<option value="Accounting Services">Accounting
Services</option>
<option value="Acoustical Ceiling">Acoustical
Ceiling</option>
</td>
</tr>
<tr>
<td >City:</td>
</tr>
<tr>
<td ><input name="city"></td>
</tr>
<tr>
<td >State:</td>
</tr>
<tr>
<td ><input name="state"></td>
</tr>
<tr>
<td ><input name="submit" value="Submit" type="submit"></td>
</tr>
</tbody>
</table>
<br>
</form>

My yellowresult.php is as follows:

<?php
$host = "localhost";
$user = "root";
$db = "yellowpages";
$name = mysql_escape_string($POST["name"]);
$category = mysql_escape_string($POST["category"]);
$city = mysql_escape_string($POST["city"]);
$state = mysql_escape_string($POST["state"]);
$conn = mysql_connect($host,$user,"") or die("Error Connecting:" .mysql_error());
$dbselect = mysql_select_db($db,$conn) or die("Error selecting:" .mysql_error());
if(!empty($name)) {
$query1= "select from yellowtable WHERE name LIKE '%$name%'";
}
elseif(!empty($category)) {
$query1= "select from yellowtable WHERE category LIKE '%$category%'";
}
elseif(!empty($city)){
$query1= "select from yellowtable WHERE city LIKE '%$city%'";
}
else {
$query1= "select from yellowtable WHERE state LIKE '%$state%'";
}
$results = mysql_query($query1) or die("Error querying:" .mysql_error());;
$rowcount = mysql_num_rows($results);
while($r=mysql_fetch_array($results))
{

$title=$r["name"];
$message=$r["category"];
$who=$r["address"];
$date=$r["city"];
$time=$r["state"];
$id=$r["phone"];

 echo "$title <br> $message <br> $who <br> $date | $time <br>";

}
?>

But it does not work. When I remove %% in any of the select statements, it gets me a blank page.
I also tried this:
$query1= "select * from yellowtable WHERE name LIKE '%$name%' OR category LIKE '%$category%' OR city LIKE '%$city%' OR state LIKE '%$state%'"; without the if(!empty). But it deos not seem to work.

The yellowtable has these fields id, name, address, city, state, phone, website.

Please help me what I am doing wrong or what is the alternative way to accomplish this.

nemonoman

It appears from your posted code that the query is being sent to the database, but not finding any results. Therefore I'd guess that there is a mismatch between what you expect to find in the data, and the actual values being inserted into your SELECT statement.

So, instead of
$results = mysql_query($query1) or die("Error querying:" .mysql_error());;

try

echo "<PRE>
$query1
</PRE>";
$results = mysql_query($query1) or die("Error querying:" .mysql_error());

Take a look carefully at values being searched. There may be unintended extra characters being inserted, etc.

cutegurl985

Thanks nemonoman. <PRE></PRE> did the trick.
Since Select a category option was selected, the query was not submitting an empty value for it.