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:

 

 

<form method="post" action="yellowresult.php" name="searchform">

  <table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="2">

 

    <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. 

 

[teng84]

its working with the % now y do you wan to remove that

[hitman6003]

Check to see if the value is there before including it into the overall query.

 

$query = "select * from yellowtable ";

if (!empty($name)) {
  $query_parts[] = "name LIKE '%$name%'";
}

if (!empt($category)) {
  $query_parts[] = "category LIKE '%$category%'";
}

if (!empty($city)) {
  $query_parts[] = "city LIKE '%$city%'";
]

if (!empty($state)) {
  $query_parts[] = "state LIKE '%$state%'";
}

if (count($query_parts) > 0) {
  $query .= implode(" OR ", $query_parts);
}

$result = mysql_query($query) or die(mysql_error());

[cutegurl985]

As per hitman6003's guidance, I made the changes but still not working.

 

Now yellowresult.php looks like:

 

<?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());
$query = "select * from yellowtable ";

if (!empty($name)) {
  $query_parts[] = "name LIKE '%$name%'";
}

if (!empty($category)) {
  $query_parts[] = "category LIKE '%$category%'";
}

if (!empty($city)) {
  $query_parts[] = "city LIKE '%$city%'";
]

if (!empty($state)) {
  $query_parts[] = "state LIKE '%$state%'";
}

if (count($query_parts) > 0) {
  $query .= implode(" OR ", $query_parts);
}

$result = mysql_query($query) or die(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>";
}
?>

[hitman6003]

Need to put a WHERE in the query...

 

change:

 

if (count($query_parts) > 0) {
  $query .= implode(" OR ", $query_parts);
}

 

to

 

if (count($query_parts) > 0) {
  $query .= " WHERE " . implode(" OR ", $query_parts);
}