Searching multiple fields...

Question

monkeywright 0 Newbie Poster

17 Years Ago

I'm fairly new to PHP. Recently got a program to help me build a directory database for a werb client. I've been slowly customizing it to fit my design, but I can't get the search to behave the way I want it to. I need to search multiple fields in one form (ie - name AND city, or area code AND business type). Users should be able to search for as much or as few keywords as they want. My form code looks like this:

<form name="form1" method="POST" action="<?php $s = 'gtsearch/searchresults2.php'; echo "$path$s"; ?>">
  <table width="254" border="2" align="left" cellpadding="0" cellspacing="0" bordercolor="#ffffcc">
<tr>
<td bgcolor="#ffffcc"><table width="244"  border="0" align="center" cellpadding="3" cellspacing="0">
<tr>
<td colspan="3"><div align="center" class="style1">FIND A GYM </div></td>
</tr>
<tr>
<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>
</tr>
<tr>
<td><font size="2">Area Code</font></td>
<td colspan="2"><input type="text" name='phone' size="3"></td>
</tr>
<tr>
<td><font size="2">Zip Code</font></td>
<td colspan="2"><input name="zip" type="text" size="10"></td>
</tr>
<tr>
<td><span class="style4">State </span></td>
<td colspan="2"><select name="state" size="1">
<option value="STATEPROV" selected>Select a State</option>
                            <option value="AL" >Alabama </option>
                            <option value="AK">Alaska </option>
                            <option value="AZ">Arizona </option>
                            <option value="AR">Arkansas </option>
                            <option value="CA">California </option>
                            <option value="CO">Colorado </option>
</select></td>
</tr>
<tr bgcolor="#024282">
<td bgcolor="#FFFFCC">&nbsp;</td>
<td bgcolor="#FFFFCC"><INPUT TYPE="submit" NAME="Search" VALUE="Search" onclick="validate()">
</td>
</tr>
</table></td>
</tr>
</table></form>

And the search results age code looks like this in the header:

<?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 THIS in the search results form:

<h3>Your Search Results  </h3>
<body link="<?php echo $color3; ?>" vlink="<?php echo $color3; ?>" alink="<?php echo $color3; ?>">
<div align="center"> 
  <table border="0" cellspacing="0" cellpadding="10" align="center" width="100%">
    <tr> 
      <td valign="top" width="70%"> 
           
 
        
    <?php
if (empty($POST)) {$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>

When I try to search, the results page always says no keywords were entered. If I use the single field search that was included with the program, everything works fine.

HELP!
:'(

php

4 Contributors
11 Replies
253 Views
3 Years Discussion Span
Latest Post 14 Years Ago Latest Post by allenaj

UrbanSky 0 Light Poster

17 Years Ago

can you do an echo on the sql statment just before you call mysql_query please. At first glance in your mysql_query your have written $SQL rather that $sql. If you post the $sql statment then if will show you exactly what your are asking the db to query.

GliderPilot 33 Posting Whiz

17 Years Ago

Couple of things I noticed was:

You check to see if the varibale $POST is empty in your if statement. What you actualy want to do is you want to check the array of the submitted form which is $_POST.

if (empty($_POST))

One other thing would by when you first start the sql statement I would remove the space after WHERE, otherwise you will end up with a double space which could caus troubles.

Only other problem I see is that your $sql variable is in lower case when your building the query, but when you put it in the mysql_query() you used upper case. PHP variable are case sensitive so this would not work.

One other sugestion, not really a problem, at the line where you send your sql statement to MySQL I would add some error catching to ensure that the query worked to save you some troubles if you have problems.

So this would be an example of your new query statement.

$result = mysql_query($sql) or die ("Unable to search database. Please contact the webmaster." .  mysql_error());

you can modify that statement as you like the important part is the mysql_error() which will print out the error that mysql had when you tried to process the query.

If you do not want it to die, after you do the query you can check to see if $result contains any data, and if not use mysql_error again to print out an error message.

Hope this helps

GliderPilot 33 Posting Whiz

17 Years Ago

I think I see the problem on the line:

$sql="ORDER BY TITLE ASC LIMIT 50";

you replace the $sql variable rather than adding to it. it should be. Also note the space at the beggining of the var.

$sql . = " ORDER BY TITLE ASC LIMIT 50";

Some other spacing issues as well. To avoid double spaces in your query I would remove the spave on the evaluator in each if statement like this:

$sql .= $sep . "title like '%$title%'";

Let me know how it goes.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

monkeywright 0 Newbie Poster · Answer 1 · 2007-05-03T04:59:11+00:00

Theunderscore helped - I know it's passing along the search variables now, but it's still not working. If I go with the search code I posted, the screen just goes white on the search results page. If I try it with the code that came with the software:

<?php
if (empty($_POST)) {$message = "Your search contained no keywords"; include 'status.php';}else{
$keywords = explode(" ", $_POST);
foreach ($keywords as $value) {
$SQL = "Select * from LISTINGS WHERE $_POST 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;
?>
    <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 }
?>

The page loads up and it says it couldn't find anythign matching the query. I feel like we're close here...just not sure what to do next.

monkeywright 0 Newbie Poster · Answer 2 · 2007-05-03T05:02:26+00:00

can you do an echo on the sql statment just before you call mysql_query please. At first glance in your mysql_query your have written $SQL rather that $sql. If you post the $sql statment then if will show you exactly what your are asking the db to query.

When I add this line, the word "Array" appears on the search results screen. Does this give any clues?

monkeywright 0 Newbie Poster · Answer 3 · 2007-05-03T08:30:16+00:00

Okay. It just goes to a white screen. Doesn't seem to be processing any of the data on the results page now... If I view source, I can see that it gets this far:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=utf-8"></HEAD>
<BODY></BODY></HTML>

So it seems like it's reading through the entire page...where's my results? This is frustrating...

UrbanSky 0 Light Poster · Answer 4 · 2007-05-03T18:03:28+00:00

okay,

first
$SQL = "Select * from LISTINGS WHERE $_POST LIKE '%$value%' ORDER BY TITLE ASC LIMIT 50";

will actually be "Select * from LISTINGS WHERE array LIKE '%value%' ORDER BY TITLE ASC LIMIT 50

the $_POST value is an assoc array and you need to referance the element you want.

This explans why you do get any results becuase it is not a valid SQL statment and because you don't check for mysql
error after you query the database error is just lost.

I would edit you code to look something like this

?php
//Check at least on search value has been submitted
if (empty($_POST)) {
//No Search item return and error
$message = "Your search contained no keywords";
include 'status.php';
}else{
//Initalise Where Clause
$where_clause = "";
//Loop Through the $_POST array
//Note: I am assuming that the form input fields have the same name as the database field name
foreach ($_POST as $key => $value)
{
//If the where_clause is not empty add and AND to the end
if(!empty($where_clause)) $where_clause .= " AND ";
//Note: We ensure that are form field names are in uppercase to match (where looks like) your //database fieldname cases to elimnate browser qwerks and os qwerys
$where_clause .= strtoupper($key)." LIKE '%$value%'";
}
$SQL = "SELECT * FROM LISTINGS WHERE $where_clause ORDER BY TITLE ASC LIMIT 50";
$result = mysql_query( $SQL );
//Handle query errors
if(!$result) die("Database Error: ".mysql_error($results))
//Output the results as you had origanlly
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;
?>

monkeywright 0 Newbie Poster · Answer 5 · 2007-05-04T20:26:15+00:00

Still getting a white screen when I submit the search...

UrbanSky 0 Light Poster · Answer 6 · 2007-05-04T20:37:08+00:00

what error reporting level have you got switched on? Also if you veiw source on you white screen is there any text there?

monkeywright 0 Newbie Poster · Answer 7 · 2007-05-05T08:29:40+00:00

what error reporting level have you got switched on? Also if you veiw source on you white screen is there any text there?

Viewing page source, I get:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=utf-8"></HEAD>
<BODY></BODY></HTML>

I thought I had detailed error reporting turned on... it's given me better messages on other sites, those were more sql specific.... How can I check?

allenaj 0 Newbie Poster · Answer 8 · 2010-08-14T16:14:44+00:00

how do I make sure all textfields are field when submitted