...

View Full Version : Modyfying a PHP search script to search multiple rows in a MYSQL database



Columbian
08-30-2006, 06:10 PM
Hello.....i have a php script here that i am trying to modify so it will be able to do a keyword search of multiple rows in a database....but i'm stuck

when i search using specific values like "agency name" and "senior offices", the script works fine.....i am just lost as to how i can search all the rows in the database (like a keyword search)

this is the search.php


<?php
include 'includes/config.php';
?>
<form name="form1" style="margin:0px" method="post" action="<?php $s = 'searchresults.php'; echo "$path$s"; ?>">
<select name="2">
<option value="STATEPROV" selected>Keywords</option>
<option value="TITLE" >Agency Name</option>
<option value="OVERVIEW">All Services</option>
<option value="HOURS">Senior Officers</option>
</select>
<input type="submit" name="Search" value="Go">
</form>

this is my searchresults.php


<?php
/////////////////////////////////////////////////////////////////////////////////
include 'includes/config.php';
$a = $_POST['1'];
$b = $_POST['2'];
/////////////////////////////////////////////////////////////////////////////////
?>

<strong>Search Results</strong>
<?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%' 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"];
$overview = $row["OVERVIEW"];
$announcements = $row["ANNOUNCEMENTS"];
$hours = $row["HOURS"];
$cit = $row["CITY"];
$zip = $row["ZIP"];
if ($hold == $id) {} else {
$hold = $id;
?>
<table cellspacing="0" cellpadding="2" border="0" bordercolor="#FFFFFF" bgcolor="#CCCCCC" width="100%">
<tr bgcolor="#F9F9F9">
<td width="33%" bgcolor="#FFFFFF"> <font color="#FFFFFF"><font color="#000000"> <a style="font-weight:bold" href="item.php?id=<?php echo $id; ?>&dir=<?php echo $directory; ?>">
<?php echo $title; ?> </a> </font></font><br>
<?php echo $address; ?> <?php $qrst = none; ?>
<br>
<br></td>
</tr>
</table>
<?php } ?>
<?php } ?>
<?php }
?>
<?php }
?>
<?php if (empty($qrst)) {?>
<table cellspacing="0" cellpadding="2" border="0" bordercolor="#FFFFFF" bgcolor="#CCCCCC" width="100%">
<tr bgcolor="#F9F9F9">
<td width="33%"> <font color="#FFFFFF"><font color="#000000"> Sorry,
we couldn't find anything matching that criteria.</font></font></td>
</tr>
</table>
<?php } ?>
<br>
<br>
<br>
<br> </td>

</tr>
</table>

Fumigator
08-30-2006, 07:19 PM
You should really look into fulltext searching (http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html). MySQL pretty much handles all the grunt work for you.

Columbian
08-30-2006, 07:28 PM
yeh true...butthis code does the job nicely....i'm just wondering if i can modyfy it to search all fields instead of one particular field

guelphdad
08-30-2006, 08:01 PM
look into fulltext searching then you can easily search across multiple columns. you would not be changing anything in your script except the single database query. failing that chain a list of ORs together in the query.


select
foo,
bar,
qux
from yourtable
where foo like '%searchterm%'
OR
bar like '%searchterm%'
OR
qux like '%searchterm%'


note that the use of % before your searchterm precludes your query from using an index on any of those columns and thus your search time will be SIGNIFICANTLY slower than using FULLTEXT which is exactly why fulltext was suggested.

Columbian
08-30-2006, 08:28 PM
thanks i will mess around with this and let you guys know how it goes

Columbian
08-31-2006, 10:19 PM
got it working....thanks



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum