I'm looking for some PHP scripts that be able to search into the Database and retrieve all the records that matches the user input string in the search form.
Please advise.
lordx78 0 Junior Poster
JeniF 0 Junior Poster in Training
The script I am posting is a fairly advanced search page using left joins on tables, but I think you can grasp what the script is doing. This search allows for 3 different types of search methods (either used separately or in combination), and allows the user to sort the search results as well. Additionally, this script has restricted access to the page, but you can eliminate that if it is not necessary. I have the results in a repeating row (limited to 50 records per page)This is something I have used, adapted, and built upon several times. If you need the search to be less advanced, you can adapt to your needs as well. Granted, you probably didn't need the whole page of code, but I thought it would be helpful as well.
<?php require_once('connections.php'); ?>
<?php
if (!isset($_SESSION)) {
session_start();
}
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";
// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {
// For security, start by assuming the visitor is NOT authorized.
$isValid = False;
// When a visitor has logged into this site, the Session variable MM_Username set equal to their username.
// Therefore, we know that a user is NOT logged in if that Session variable is blank.
if (!empty($UserName)) {
// Besides being logged in, you may restrict access to only certain users based on an ID established when they login.
// Parse the strings into arrays.
$arrUsers = Explode(",", $strUsers);
$arrGroups = Explode(",", $strGroups);
if (in_array($UserName, $arrUsers)) {
$isValid = true;
}
// Or, you may restrict access to only certain users based on their username.
if (in_array($UserGroup, $arrGroups)) {
$isValid = true;
}
if (($strUsers == "") && true) {
$isValid = true;
}
}
return $isValid;
}
$MM_restrictGoTo = "sendbackto somepagehere.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {
$MM_qsChar = "?";
$MM_referrer = $_SERVER['PHP_SELF'];
if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0)
$MM_referrer .= "?" . $QUERY_STRING;
$MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
header("Location: ". $MM_restrictGoTo);
exit;
}
?>
<?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;
}
}
$maxRows_rsCriteria = 50;
$pageNum_rsCriteria = 0;
if (isset($_GET['pageNum_rsCriteria'])) {
$pageNum_rsCriteria = $_GET['pageNum_rsCriteria'];
}
$startRow_rsCriteria = $pageNum_rsCriteria * $maxRows_rsCriteria;
//$AppName=$row_rsCriteria['app_description'];
mysql_select_db($database_mambo, $mambo);
$query_rsCriteria = "SELECT * FROM users LEFT JOIN access ON users.id = access.user_idLEFT JOIN application_access on access.app_id = application_access.app_id WHERE access.app_id IS NOT NULL AND application_access.app_description IS NOT NULL ";
$query_limit_rsCriteria = sprintf("%s LIMIT %d, %d", $query_rsCriteria, $startRow_rsCriteria, $maxRows_rsCriteria);
$rsCriteria = mysql_query($query_limit_rsCriteria, $mambo) or die(mysql_error());
$row_rsCriteria = mysql_fetch_assoc($rsCriteria);
if (isset($_GET['totalRows_rsCriteria'])) {
$totalRows_rsCriteria = $_GET['totalRows_rsCriteria'];
} else {
$all_rsCriteria = mysql_query($query_rsCriteria);
$totalRows_rsCriteria = mysql_num_rows($all_rsCriteria);
}
$totalPages_rsCriteria = ceil($totalRows_rsCriteria/$maxRows_rsCriteria)-1;
?><!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>Search User Access</title>
<style type="text/css">
<!--
body,td,th {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
color: #000066;
}
-->
</style></head>
<body>
<form id="search" name="search" method="post" action="">
<?
if($_POST) {
$query_rsCriteria = "SELECT * FROM users LEFT JOIN access ON users.id = access.user_idLEFT JOIN application_access on access.app_id = application_access.app_id ";
if($_POST['find']!="" && $_POST['findfield']!="") {
$query_rsCriteria .= " WHERE ";
if($_POST['findfield']=="application_access.app_description") {
$query_rsCriteria .= "application_access.app_description LIKE '%" . $_POST['find'] . "%'AND access.app_id IS NOT NULL";
}
if($_POST['findfield']=="users.username") {
$query_rsCriteria.= "users.username LIKE '%" . $_POST['find'] . "%'AND access.user_id IS NOT NULL AND application_access.app_description IS NOT NULL";
}
/////////////////
if($_POST['find']!="") {
$query_rsCriteria.= "users.username LIKE '%" . $_POST['find'] . "%' AND application_access.app_description IS NOT NULL or application_access.app_description LIKE '%" . $_POST['find'] . "%'";
}
////////////////
}
if($_POST['find']!="" && $_POST['findfield']="") {
$query_rsCriteria .= " WHERE users.username LIKE '%" . $_POST['find'] . "%' or application_access.app_description LIKE '%" . $_POST['find'] . "%'";
}
if ($_POST['sortby']!=" ") {
$sort=$_POST['sortby'];
$query_rsCriteria .= " ORDER BY $sort";
}
if ($_POST['sortorder']=="Ascending" && $_POST['sortby']!=" ") {
$query_rsCriteria .= " ASC ";
}
if ($_POST['sortorder']=="Descending" && $_POST['sortby']!=" ") {
$query_rsCriteria .= " DESC ";
}
if ($_POST['sortby2']!=" ") {
$sort2=$_POST['sortby2'];
$query_rsCriteria .= " , $sort2";
}
if ($_POST['sortorder2']=="Ascending" && $_POST['sortby2']!=" ") {
$query_rsCriteria .= " ASC ";
}
if ($_POST['sortorder2']=="Descending" && $_POST['sortby2']!=" ") {
$query_rsCriteria .= " DESC ";
}
if ($_POST['ALL'])
{
$query_rsCriteria = "SELECT * FROM users LEFT JOIN access ON users.id = access.user_idLEFT JOIN application_access on access.app_id = application_access.app_id WHERE access.app_id IS NOT NULL AND application_access.app_description IS NOT NULL AND access.user_idIS NOT NULL ";
}
}
$rsCriteria = mysql_query($query_rsCriteria, $mambo) or die(mysql_error());
$row_rsCriteria = mysql_fetch_assoc($rsCriteria);
$totalRows_rsCriteria = mysql_num_rows($rsCriteria);
$found = mysql_num_rows($rsCriteria);
?>
<table width="583">
<tr valign="top">
<th colspan="4" scope="row">User Access to Applications </th>
</tr>
<tr>
<th colspan="2" scope="row"><div align="left">
<input type="button" name="Cancel" value="Return to Menu" onClick="self.location='index.php'" />
<input type="button" name="Cancel" value="Add" onClick="self.location='access.php'" /></div></th>
<td width="283" colspan="2"><div align="right"><a href="<?php echo $logoutAction ?>">Log out</a></div></td>
</tr>
</table>
<table width="583" border="1">
<tr valign="top">
<th width="148" scope="row"><div align="right">Primary Sort : </div></th>
<td width="165">
<select name="sortby" id="sortby">
<option value=" "> </option>
<option value="application_access.app_description">Application</option>
<option value="users.username">Username</option>
</select> </td>
<td width="242">
<input name="sortorder" type="radio" value="Ascending" checked="checked" />Ascending
<input name="sortorder" type="radio" value="Descending" />Descending </td>
</tr>
<tr valign="top">
<th scope="row"><div align="right">Secondary Sort: </div></th>
<td>
<select name="sortby2" id="sortby2">
<option value=" "> </option>
<option value="application_access.app_description">Application</option>
<option value="users.username">Username</option>
</select> </td>
<td>
<input name="sortorder2" type="radio" value="Ascending" checked="checked"/>Ascending
<input name="sortorder2" type="radio" value="Descending" />Descending </td>
</tr>
<tr valign="top">
<th scope="row"><div align="right">Find:</div></th>
<td>
<select name="findfield" id="findfield">
<option value=" "> </option>
<option value="application_access.app_description">Application</option>
<option value="users.username">Username</option>
</select> </td>
<td><input name="find" type="text" id="find" /></td>
</tr>
<tr valign="top">
<th colspan="3" scope="row">
<input type="submit" name="Search" value="Search" class="search">
<input type="reset" name="Reset" value="Reset" class="search">
<input type="submit" name="ALL" value="Search All" /></th>
</tr>
</table>
<table width="583" border="1" bordercolor="#000066">
<tr>
<td width="85" valign="top" scope="row"><div align="left"><b>Actions</b></div></td>
<td width="132" valign="top" scope="row"><div align="left"><b>Username</b></div></td>
<td width="344" valign="top"><div align="left"><b>Application Access </b>
</div>
</td>
</tr>
<?php do { ?>
<tr>
<td valign="top" scope="row"><div align="left">
<input name="id" type="hidden" id="id" value="<?php echo $row_rsCriteria['id']; ?>" />
[<a href="delete.php?id=<?php echo $row_rsCriteria['id'];?>&app_description=<? echo $row_rsCriteria['app_description'];?>&access_id=<? echo $row_rsCriteria['access_id']; ?>">Delete</a>]</div></td>
<td valign="top" scope="row"><div align="left"><?php echo $row_rsCriteria['username']; ?></div></td>
<td valign="top"><div align="left"><?php echo $row_rsCriteria['app_description']; ?>
<input name="user_access_ID" type="hidden" id="user_access_ID" value="<?php echo $row_rsCriteria['access_id']; ?>" />
</div></td>
</tr>
<?php } while ($row_rsCriteria = mysql_fetch_assoc($rsCriteria)); ?>
</table>
</form>
</body>
</html>
<?php
mysql_free_result($rsCriteria);
?>
ryan_vietnow 13 Posting Pro
try to alter this script for your use:
input page:
<p>Search:</p>
<form name="form1" action="post" action="process.php">
<input type="text" name="text1" value="">
<input type="submit" name="submit" value="Submit">
</form>process.php
//put your db connection here(for example localhost)....
$host="localhost";
$db_user="user";
$db_password="password";
$database="db";
$dbh=mysql_connect ($host, $db_user, $db_password) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$database");
//string to be searched from the input form...
$str=$_POST['text1'];
//query string...
$query="SELECT field1,field2 from table1
WHERE field1='$str'";
/* field1,field2 are fields you want to display,
table1 is the table where you want to search the data... */
$result=mysql_query($query);
$num=mysql_num_rows($result);
//display results...
while ($i<$num)
{
$field1=mysql_result($result,$i,"field1");
$field2=mysql_result($result,$i,"field2");
echo $field1;
echo ",";
echo $field2;
echo "</br>";
$i++;
}Basically that's it...
lordx78 0 Junior Poster
Thanks all, solved. used both the codes.
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.