Search Pages

[mistys77]

Hello,

I have created a v.simple search page, which searchs on 1 field only.

I would like to enhance this by:

Showing a No Results page, if no entries are found
Showing a more advanced search page, i.e - allowing the user to search on more than 1 field, and allowing the user to specify 'AND' or 'OR' options.

I am using Dreamweaver MX , PHP , and MySQL.

If you have any ideas, I would be very greatful.

Many Thanks

SM

[Pig]

No results are easy. Check out mysql_num_rows() in the manual.

The other question is pure programming. Are you familiar with PHP, or are you using a pre built script?

[mistys77]

Thanks,

I'm prity new to PHP, so I would like to see the code and then work from there.

Is there anywhere I can get a sample script from

thanks again.

SM

[Pig]

Here's a quick example.

No results:

$result = mysql_query("SELECT * FROM table WHERE table_description LIKE '%" . $_POST['keyword'] . "%'");
if( mysql_num_rows == 0 ) echo("No matches were found.<br>");
else
{
//blah blah blah
}

Search options:
You have to build the SQL statement, and then run the query. For each option, check what they have specified. In this example, the user has been given a keyword field to search, which looks in the filed table.table_description and also can choose to limit their search to entries that are no more than a week old (table.table_createdate).

$sql = "SELECT * FROM table WHERE "
if( isset($_POST['keyword']) ) $sql .= " table_description LIKE '%" . $_POST['keyword'] . "%' ";
if( isset($_POST['last_week']) && isset($_POST['keyword']) ) $sql .= "AND table_createdate > '" . strtotime("now -1 week") . "'";
else $sql .= "table_createdate > '" . strtotime("now -1 week") . "'";
$result = mysql_query($sql);

[mistys77]

Thanks for this,

I am not sure where to place this code, my code is as follows:

Where do I put this code? and if there are no results, can this go to a differnt page altogether?

<?php require_once('../Connections/MySite1.php'); ?>
<?php
$currentPage = $HTTP_SERVER_VARS["PHP_SELF"];

$maxRows_rsProductListA = 3;
$pageNum_rsProductListA = 0;
if (isset($HTTP_GET_VARS['pageNum_rsProductListA'])) {
$pageNum_rsProductListA = $HTTP_GET_VARS['pageNum_rsProductListA'];
}
$startRow_rsProductListA = $pageNum_rsProductListA * $maxRows_rsProductListA;

$colname_rsProductListA = "1";
if (isset($HTTP_GET_VARS['varProdName'])) {
$colname_rsProductListA = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['varProdName'] : addslashes($HTTP_GET_VARS['varProdName']);
}
mysql_select_db($database_MySite1, $MySite1);
$query_rsProductListA = sprintf("SELECT PRODUCTCODE, PRODNAME, PRODDESCRIP, PRODDISC, PRODCOLOUR, PRODTEXTURE, PRODPRICE1, PRODSORT1, PRODSORT2, PRODSORT3, PRODSORT4, PRODSORT5, PRODSORT6, PRODSORT7, PRODSORT8, PRODSORT9, PROD_ID, PRODPATH, PRODCATEGORY FROM products WHERE PRODNAME LIKE '%s%%'", $colname_rsProductListA);
$query_limit_rsProductListA = sprintf("%s LIMIT %d, %d", $query_rsProductListA, $startRow_rsProductListA, $maxRows_rsProductListA);
$rsProductListA = mysql_query($query_limit_rsProductListA, $MySite1) or die(mysql_error());
$row_rsProductListA = mysql_fetch_assoc($rsProductListA);

if (isset($HTTP_GET_VARS['totalRows_rsProductListA'])) {
$totalRows_rsProductListA = $HTTP_GET_VARS['totalRows_rsProductListA'];
} else {
$all_rsProductListA = mysql_query($query_rsProductListA);
$totalRows_rsProductListA = mysql_num_rows($all_rsProductListA);
}
$totalPages_rsProductListA = ceil($totalRows_rsProductListA/$maxRows_rsProductListA)-1;

mysql_select_db($database_MySite1, $MySite1);
$query_rsNewProds = "SELECT PRODUCTCODE, PRODNAME, PROD_ID, PROD_NEWPRODUCT, PRODSHORTNAME FROM products WHERE PROD_NEWPRODUCT = 1";
$rsNewProds = mysql_query($query_rsNewProds, $MySite1) or die(mysql_error());
$row_rsNewProds = mysql_fetch_assoc($rsNewProds);
$totalRows_rsNewProds = mysql_num_rows($rsNewProds);

$colname_rsProductListB = "1";
if (isset($HTTP_GET_VARS['varProductDropList'])) {
$colname_rsProductListB = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['varProductDropList'] : addslashes($HTTP_GET_VARS['varProductDropList']);
}
mysql_select_db($database_MySite1, $MySite1);
$query_rsProductListB = sprintf("SELECT PRODUCTCODE, PRODNAME, PRODDESCRIP, PRODDISC, PRODCOLOUR, PRODTEXTURE, PRODPRICE1, PRODSORT1, PRODSORT2, PRODSORT3, PRODSORT4, PRODSORT5, PRODSORT6, PRODSORT7, PRODSORT8, PRODSORT9, PROD_ID, PRODPATH, PRODCATEGORY FROM products WHERE PRODCATEGORY = '%s'", $colname_rsProductList😎;
$rsProductListB = mysql_query($query_rsProductListB, $MySite1) or die(mysql_error());
$row_rsProductListB = mysql_fetch_assoc($rsProductList😎;
$totalRows_rsProductListB = mysql_num_rows($rsProductList😎;

$queryString_rsProductListA = "";
if (!empty($HTTP_SERVER_VARS['QUERY_STRING'])) {
$params = explode("&", $HTTP_SERVER_VARS['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_rsProductListA") == false &&
stristr($param, "totalRows_rsProductListA") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_rsProductListA = "&" . implode("&", $newParams);
}
}
$queryString_rsProductListA = sprintf("&totalRows_rsProductListA=%d%s", $totalRows_rsProductListA, $queryString_rsProductListA);
?>