PDA

View Full Version : single search box searching multiple fields


kona72
01-18-2007, 12:28 PM
Hi Guys/Gals,

I have a search box that currently when submitted searches the content field of the DB.

Structure of DB is rec_id, title, date, content

I would like it to be able to search all fields except the rec_id field.

I thought I had it working a little while ago but apparently not.

Here is the search Box


<form id="search" name="search" method="get" action="search_results.php">
<p>
<input name="search" type="text" id="search" />
<br />
<input type="submit" name="Submit" value="Search Archives" />
</p>
</form>
<p>&nbsp;</p>


and here is the search_results.php page


<?php require_once('../Connections/rolheiser.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$colname_rsResults = "-1";
if (isset($_GET['search'])) {
$colname_rsResults = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_rolheiser, $rolheiser);
$query_rsResults = sprintf("SELECT * FROM archives WHERE content LIKE CONCAT('%%', %s, '%%')", GetSQLValueString($colname_rsResults, "text"));
$rsResults = mysql_query($query_rsResults, $rolheiser) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
<meta name="author" content="Ledbetter Communications" />
<title>RON ROLHEISER - Speaker, Columnist and Author</title>
<link href="../ron.css" rel="stylesheet" type="text/css" media="all" />
</head>

<body background="../images/background.jpg" link="#655c42" vlink="#655c42" alink="#655c42" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="800" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="400"><a name="top" id="top"></a><a href="../index.php"></a></td>
<td colspan="2" valign="top" width="400"><a href="../index.php"></a></td>
</tr>
<tr>
<td width="400" valign="top">
<p><img src="images/title-column.jpg" alt="" height="49" width="350" border="0" /></p>
<p>&nbsp; </p>

<?php if ($totalRows_rsResults > 0) { // Show if recordset not empty ?>
<table width="90%" border="0" align="center" cellpadding="5" cellspacing="0" class="body_text">
<tr>
<td width="68%"><a href="search_detail.php?rec_id=<?php echo $row_rsResults['rec_id']; ?>"><?php echo $row_rsResults['title']; ?></a></td>
<td width="32%"><?php echo $row_rsResults['date']; ?><a href="search_detail.php?rec_id=<?php echo $row_rsResults['rec_id']; ?>"></a></td>
</tr>
</table>
<?php } // Show if recordset not empty ?>
<?php if ($totalRows_rsResults == 0) { // Show if recordset empty ?>
<table width="90%" border="0" align="center" cellpadding="10" cellspacing="0">
<tr>
<td class="body_text">Sorry No Results... </td>
</tr>
</table>
<?php } // Show if recordset empty ?><p>&nbsp;</p>

</td>
<td valign="top" width="200">
<div align="center">
<p><br />
<?php include("search_box.php"); ?>
<p>&nbsp;</p>
</div> </td>
<td valign="top" width="200">
<p><br />
</p>
<h1>&nbsp;</h1>
</td>
</tr>
</table>
<p></p>
</body>
</html>
<?php
mysql_free_result($rsResults);
?>


Initially i thought i could just add to the query like such ( I added OR title OR date).... but no such luck...


mysql_select_db($database_rolheiser, $rolheiser);
$query_rsResults = sprintf("SELECT * FROM archives WHERE content OR title OR date LIKE CONCAT('%%', %s, '%%')", GetSQLValueString($colname_rsResults, "text"));
$rsResults = mysql_query($query_rsResults, $rolheiser) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);


Any suggestions would be appreciated....

davidj
01-18-2007, 12:58 PM
dreamweaver code rears its ugly head again

yyakkk.... horrible stuff

please allow me to rewrite this for you and to sort what you want

kona72
01-18-2007, 06:00 PM
much appreciated....

I know that DW barfs out all the ugly code... Sorry 'bout that....

I will watch for the 'updated' clean code.


Thanks Again...

davidj
01-18-2007, 06:34 PM
rollin sleeves up...

bring it on!

davidj
01-18-2007, 08:35 PM
ok

all on one page

see if you can follow it


<?php
require_once('../Connections/rolheiser.php');

// get value and set var
$search = $_GET['search'];

$field = array('car_model', 'car_model_id'); // << add all fields to search on

if ($search){

foreach($field as $value){

//query db
mysql_select_db($database_rolheiser, $rolheiser);
$query_rsResults = sprintf("SELECT * FROM archives WHERE $field LIKE CONCAT('%%', %s, '%%')", $search));
$rsResults = mysql_query($query_rsResults, $rolheiser) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);


if($totalRows_rsResults[$value]){

$result = $totalRows_rsResults[$value];

break;

}else{$result = "";}

}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<form id="search" name="search" method="get" action="search_results.php">
<p>
<input name="search" type="text" id="search" />
<br />
<input type="submit" name="Submit" value="Search Archives" />
</p>
</form>
<br />


<?php if ($result) { // Show if recordset not empty ?>

<table width="90%" border="0" align="center" cellpadding="5" cellspacing="0" class="body_text">
<tr>

<td width="68%">
<a href="search_detail.php?rec_id=<?php echo $result; ?>"><?php echo "Found $result"; ?></a>
</td>



</tr>
</table>
<?php } // Show if recordset not empty ?>


<?php if (! $result) { // Show if recordset empty ?>
<table width="90%" border="0" align="center" cellpadding="10" cellspacing="0">
<tr>
<td class="body_text">Sorry No Results... </td>
</tr>
</table>
<?php } // Show if recordset empty ?>

</body>
</html>

kona72
01-18-2007, 08:49 PM
AH HA!


$field = array('car_model', 'car_model_id'); // << add all fields to search on


this little bit o' code is the culprit!!!

I can definately see the benefit of the cleaned up code... I can read AND understand it!!!!!


I'll give it a shot and let you knwo how I make out


Thanks again David....

DavidJ.... Dreamweaver Slayer!!!

kona72
01-18-2007, 09:28 PM
Ok.... I thought I had it all figured out but now i get an array error....

To be exact i get this error....

Unknown column 'Array' in 'where clause'

I thought it may be because the date field is set to date VS varchar.. but no....


Here is the code now...


<?php
require_once('../Connections/rolheiser.php');

// get value and set var
$search = $_GET['search'];

$field = array('content', 'title', 'date'); // << add all fields to search on

if ($search){

foreach($field as $value){

//query db
mysql_select_db($database_rolheiser, $rolheiser);
$query_rsResults = sprintf("SELECT * FROM archives WHERE $field LIKE CONCAT('%%', %s, '%%')", $search);
$rsResults = mysql_query($query_rsResults, $rolheiser) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);


if($totalRows_rsResults[$value]){

$result = $totalRows_rsResults[$value];

break;

}else{$result = "";}

}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
<meta name="author" content="Ledbetter Communications" />
<title>RON ROLHEISER - Speaker, Columnist and Author</title>
<link href="../ron.css" rel="stylesheet" type="text/css" media="all" />
</head>

<body background="../images/background.jpg" link="#655c42" vlink="#655c42" alink="#655c42" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="800" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="400"><a name="top" id="top"></a><a href="../index.php"><img src="../images/index_01.jpg" alt="" height="41" width="400" border="0" /></a></td>
<td colspan="2" valign="top" width="400"><a href="../index.php"><img src="images/column-archive-top.jpg" alt="" height="33" width="400" border="0" /></a></td>
</tr>
<tr>
<td width="400" valign="top">
<p><img src="images/title-column.jpg" alt="" height="49" width="350" border="0" /></p>
<p>&nbsp; </p>

<?php if ($totalRows_rsResults > 0) { // Show if recordset not empty ?>
<table width="90%" border="0" align="center" cellpadding="5" cellspacing="0" class="body_text">
<tr>
<td width="68%"><a href="search_detail.php?rec_id=<?php echo $row_rsResults['rec_id']; ?>"><?php echo $row_rsResults['title']; ?></a></td>
<td width="32%"><?php echo $row_rsResults['date']; ?><a href="search_detail.php?rec_id=<?php echo $row_rsResults['rec_id']; ?>"></a></td>
</tr>
</table>
<?php } // Show if recordset not empty ?>
<?php if ($totalRows_rsResults == 0) { // Show if recordset empty ?>
<table width="90%" border="0" align="center" cellpadding="10" cellspacing="0">
<tr>
<td class="body_text">Sorry No Results... </td>
</tr>
</table>
<?php } // Show if recordset empty ?><p>&nbsp;</p>
<p><br />
</p>
<p><br />
<br />
<br />
<br />
<br />
<br />
<a href="#top">back to top<br />
</a><a href="indexold.html"><br />
</a></p>
<p></p> </td>
<td valign="top" width="200">
<div align="center">
<p><br />
<img src="images/columnyears-titles.jpg" alt="" height="20" width="170" border="0" />
<?php include("search_box.php"); ?>
<p>&nbsp;</p>
</div> </td>
<td valign="top" width="200">
<p><br />
<img src="images/inexile-titles.jpg" alt="" height="20" width="170" border="0" /></p>
<h1><span class="ron" style="font-family:sans-serif;">Ron Rolheiser's weekly column appears in more than 65 Catholic newspapers worldwide, reaching a million and a half readers each week. He began writing his column in 1982 (at first it was bi-weekly) under the title &quot;In Exile&quot;. </span></h1>
<h1><span class="ron" style="font-family:sans-serif;">Rolheiser says he chose this title because &quot;all of us live in exile in a real way. As St. Paul puts it, we see as 'through a glass darkly', through an enigma, separated always partially from God and each other&quot;. </span></h1>
<h1><span class="ron" style="font-family:sans-serif;">Rolheiser's column touches on many issues connected with spirituality and our relationship with God. Each year, he writes one column on suicide, which he says is the single most gratifying pieces of work he does because of the feedback he receives from people who have lost loved ones and are grateful for the hope and consolation that his column has provided them.</span></h1> </td>
</tr>
</table>
<p></p>
</body>
</html>

davidj
01-18-2007, 09:30 PM
swap WHERE $field with WHERE $value