•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the PHP section within the Web Development category of DaniWeb, a massive community of 426,180 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 1,819 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our PHP advertiser: Lunarpages PHP Web Hosting
Views: 2184 | Replies: 10
 |
•
•
Join Date: May 2007
Location: Los Angeles, CA
Posts: 7
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Searching multiple fields... 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:
And the search results age code looks like this in the header:
[PHP]<?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'];
?>[/PHP]
And THIS in the search results form:
[PHP]<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>[/PHP]
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!
<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"> </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]<?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'];
?>[/PHP]
And THIS in the search results form:
[PHP]<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>[/PHP]
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!
•
•
Join Date: Oct 2006
Location: London
Posts: 42
Reputation:
Rep Power: 2
Solved Threads: 4
Light Poster
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.
•
•
Join Date: Sep 2006
Location: Canada
Posts: 55
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster in Training
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.
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.
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
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
•
•
Join Date: May 2007
Location: Los Angeles, CA
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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] <?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 }
?>[/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.
[PHP] <?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 }
?>[/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.
•
•
Join Date: May 2007
Location: Los Angeles, CA
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by UrbanSky 
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?
•
•
Join Date: Sep 2006
Location: Canada
Posts: 55
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster in Training
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.
$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.
•
•
Join Date: May 2007
Location: Los Angeles, CA
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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:
So it seems like it's reading through the entire page...where's my results? This is frustrating...
•
•
•
•
<!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>
Last edited by monkeywright : May 2nd, 2007 at 10:41 pm.
•
•
Join Date: Oct 2006
Location: London
Posts: 42
Reputation:
Rep Power: 2
Solved Threads: 4
Light Poster
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;
?>
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;
?>
Last edited by UrbanSky : May 3rd, 2007 at 8:06 am.
|
•
•
•
•
•
•
•
•
DaniWeb PHP Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Similar Threads
- Problem searching multiple sheets in excel (Visual Basic 4 / 5 / 6)
- searching multiple fields (PHP)
Other Threads in the PHP Forum
- Previous Thread: Is ColdFusion and PHP coexisting on the same box possible?
- Next Thread: unblock filter
