PLEASE HELP! mutliple field search in PHP/mySQL

[monkeywright]

This is killing me. I ordered a program to help me build a website. The included search function only allows searching in a single field (Only name OR only city, etc). I need users to be able to search as many or few fields as they want from the following: name, city, area code, state, zip code.

 

I'm pretty decent with HTML and CSS, and I've been able to modify the other PHP to do what I need,  but the search feature is killing me. The code for my search form is standard, table based. Each field is named in the following manner:

                        <td width="72">Company</td>
                        <td colspan="2"><input type="text" name='1' id='TITLE' /></td>

 

And so on. The program named the text field '1' and the search option on the drop down menu as '2'. I'm sure part of the problem is that I need to change the name/id of each field. I'm posting properly, getting the data to the right place, but no results.

 

The search results page code is as follows:

<?php
session_start();
include 'includes/config.php';
include 'includes/warmup.php';
include "styles/$stylesheet";
$a = $_POST['1'];
$b = $_POST['2'];
?>

 

 

followed by my headers, some html, then the search function (as written by the company)

 

<?php
if (empty($a)) {$message = "Your search contained no keywords"; include 'status.php';}else{
$keywords = explode(" ", $a);
foreach ($keywords as $value) {

$SQL = "Select * from LISTINGS WHERE $b LIKE '%$value%' ORDER BY TITLE ASC LIMIT 50";
$result = mysql_query( $SQL );
while( $row = mysql_fetch_array( $result ) ) {
$id = $row["ID"];
$title = $row["TITLE"];
$directory = $row["DIRECTORY"];
$phone = $row["PHONE"];
$website = $row["WEBSITE"];
$email = $row["EMAIL"];
$address = $row["ADDRESS"];
$statepro = $row["STATEPROV"];
$cit = $row["CITY"];
$zip = $row["ZIP"];
$map = $row["MAP"];
if ($hold == $id) {} else {
$hold = $id;
?>

 

I'm sure that I just need to tweak those first two POST commands and the search line, but everything I've tried ends up with nothing. Can ANYBODY help? I've tried tutorials, other sites, etc. At my wits end!

 

???

[AndyB]

but no results ... and no error messages (from posted code that contains unclosed loops) ??

[btherl]

Yes, part of the problem is the name/id of each field.  You'll need to make seperate fields for each possible input, and name them appropriately.  Eg

 

<td width="72">Company</td>
<td colspan="2"><input type="text" name="title"></td>

 

That's the most straightforward anyway.

 

Then you can see which of your inputs are set, and construct the SQL.  It'll need to be constructed dynamically, such as:

 

$sql = "Select * from LISTINGS WHERE ";
$sep = '';
if (!empty($title)) {
  $sql .= $sep . "title like '%$title%' ";
  $sep = ' AND ';
}
if (!empty($website)) { ...

 

With one "if" for each possible input.  Well, there's other ways, but this way will work.

 

And don't worry if it's tough, it's not that simple

[monkeywright]

If there's an unclosed loop, it's probably because I forgot to copy/paste the whole code. Basically the search currently comes back with "your search contained no keywords".

 

btherl, I'll give that solution a shot. I live for complicated situations! It doesn't help that my client keeps coming back with more suggestions while I'm just trying to get the search working. One thing at a time...

[monkeywright]

So now the search breaks down like this...

 

The search box has each field identified as earlier...

                        <td width="72"><span ><font color="#000000">Company</font></span></td>
                        <td colspan="2"><input type="text" name='title'/></td>
                      </tr>
                      <tr>
                        <td><span ><font color="#000000">City</font></span></td>
                        <td colspan="2"><input type="text" name="city"></td>

 

The top of the reults page now looks like this...

 

<?php
session_start();
include 'includes/config.php';
include 'includes/warmup.php';
include "styles/$stylesheet";
$title=$_POST['title'];
$cit=$_POST['city'];
$phone=$_POST['phone'];
$statepro=$_POST['state'];
$zip=$_POST['zip'];
?>

 

And the search results code looks like this(in its entirety this time...)

    <?php
if (empty($a)) {$message = "Your search contained no keywords"; include 'status.php';}else{
$keywords = explode(" ", $title, $cit, $phone, $statepro, $zip);
foreach ($keywords as $value) {
$sql = "Select * from LISTINGS WHERE ";
$sep = '';
if (!empty($title)) {
  $sql .= $sep . "title like '%$title%' ";
  $sep = ' AND ';
}
if (!empty($city)) {
  $sql .= $sep . "city like '%$city%' ";
  $sep = ' AND ';
}
if (!empty($phone)) {
  $sql .= $sep . "phone like '%$phone%' ";
  $sep = ' AND ';
}
if (!empty($state)) {
  $sql .= $sep . "stateprov like '%$state%' ";
  $sep = ' AND ';
}
if (!empty($zip)) {
  $sql .= $sep . "zip like '%$zip%' ";
  $sep = ' AND ';
}
$sql="ORDER BY TITLE ASC LIMIT 50";
$result = mysql_query( $SQL );
while( $row = mysql_fetch_array( $result ) ) {
$id = $row["ID"];
$title = $row["TITLE"];
$directory = $row["DIRECTORY"];
$phone = $row["PHONE"];
$website = $row["WEBSITE"];
$email = $row["EMAIL"];
$address = $row["ADDRESS"];
$statepro = $row["STATEPROV"];
$cit = $row["CITY"];
$zip = $row["ZIP"];
$map = $row["MAP"];
if ($hold == $id) {} else {
$hold = $id;
?>
    <BR>
    <table cellspacing="1" cellpadding="2" border="0" bordercolor="#FFFFFF" bgcolor="#F0F0F0" width="100%">
     <tr bgcolor="#F9F9F9"> 
            
      <td width="33%"> <font color="#000000">» <a href="item.php?id=<?php echo $id; ?>&dir=<?php echo $directory; ?>"> 
       <?php echo $title; ?>
       </a> </font></td>
            
      <td width="33%"> 
       <?php echo $cit; ?>
       , 
       <?php echo $statepro; ?>
       , 
       <?php echo $map; ?>
      </td>
      <td width="33%"><a href="<?php echo $website; ?>">Website</a> | <a href="mailto:<?php echo $email ; ?>">Contact</a> 
       <?php $qrst = none; ?>
      </td>
          </tr>
        </table>
        
<?php } ?>
<?php } ?>
        
<?php }
?>
        
<?php }
?>
        
<?php if (empty($qrst)) {?>
        
    <table cellspacing="1" cellpadding="2" border="0" bordercolor="#FFFFFF" bgcolor="#F0F0F0" width="100%">
     <tr bgcolor="#F9F9F9"> 
            
<td width="33%"> <font color="#000000">» Sorry, 
              we couldn't find anything matching that criteria.</font></td>
          </tr>
        </table>
        <?php } ?>
      </td>
      

    </tr>
  </table>
  </div>

 

Still returns a result of "your search contained no keywords." What am I missing?

[monkeywright]

*bump* - it continues to mystify...

[monkeywright]

bump...