Hello, I need to create a search page with PHP and MySQL. I need to be able to search several different columns. For example i need to be able to search by Tech, Date, Problem or Resolution. Also if they enter more then one fields like tech and date, It will pull that. I have no clue where to even start with this. Im still pretty new with the whole web thing. I have been using dreamweaver CS3 but can't find a way to do a search page. I can also write out the code in a php editor if i had to, Can anyone help me write this or help me get started. step by step would be nice.
By the way im using PHP 5.2.4
Thanks
Chris
PHP Search page help
Moderator: General Moderators
Build your form to include the options you need. Use PHP to build your SQL query. Once you have your sql query built, then everything else is business as usual. 
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Everah | When posting PHP code, please use the PHP bbCode wrapper instead of CODE.
Ok I create a search form and i have a few problems. the problem type field and the Technician will not return any values. Also the Problem fields returns all values. Could someone take a look at the code to see what is wrong
Thanks for any help in this matter
Ok I create a search form and i have a few problems. the problem type field and the Technician will not return any values. Also the Problem fields returns all values. Could someone take a look at the code to see what is wrong
Thanks for any help in this matter
Code: Select all
<?php require_once('Connections/calls.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;
}
}
mysql_select_db($database_calls, $calls);
$query_rstec = "SELECT technicianname FROM technician";
$rstec = mysql_query($query_rstec, $calls) or die(mysql_error());
$row_rstec = mysql_fetch_assoc($rstec);
$totalRows_rstec = mysql_num_rows($rstec);
?>
<?php include("header.php"); ?>
<?php include("dataaccess.php"); ?>
<?php include("functions.php"); ?>
<link rel="stylesheet" type="text/css" href="callcenterreloaded.css">
<h3>Reports</h3>
<?php
$status = "";
$technician = "";
$categoryid = "";
$severity = "";
$firstname = "";
$lastname = "";
$email = "";
$description = "";
$i = 0;
if ((!empty($_POST)) || (!empty($_GET)))
{
if (!empty($_POST['technician']))
$technician = $_POST['technician'];
if (!empty($_POST['problemtype']))
$categoryid = $_POST['problemtype'];
if (!empty($_POST['status']))
$status = $_POST['status'];
if (!empty($_POST['severity']))
$severity = $_POST['severity'];
if (!empty($_POST['firstname']))
$firstname = $_POST['firstname'];
if (!empty($_POST['lastname']))
$lastname = $_POST['lastname'];
if (!empty($_POST['email']))
$email = $_POST['email'];
if (!empty($_POST['description']))
$email = $_POST['{description'];
if (!empty($_GET['technician']))
$technician = $_GET['technician'];
if (!empty($_GET['problemtype']))
$categoryid = $_GET['problemtype'];
if (!empty($_GET['status']))
$status = $_GET['status'];
if (!empty($_GET['severity']))
$severity = $_GET['severity'];
if (!empty($_GET['firstname']))
$firstname = $_GET['firstname'];
if (!empty($_GET['lastname']))
$lastname = $_GET['lastname'];
if (!empty($_GET['email']))
$email = $_GET['email'];
if (!empty($_GET['description']))
$email = $_GET['{description'];
dbconnect();
if(!empty($HTTP_GET_VARS))
{
if (!empty($HTTP_GET_VARS["orderby"]))
$orderby = $HTTP_GET_VARS["orderby"];
if (!empty($HTTP_GET_VARS["order"]))
$order = $HTTP_GET_VARS["order"];
}
if(empty($orderby))
{
$orderby = "severity";
}
if(empty($order))
{
$order = "asc";
}
if ($order == "asc")
$order = "desc";
else if ($order == "desc")
$order = "asc";
else
$order = "asc";
$whereClause = "";
if (!empty($status))
{
$whereClause = $whereClause . whereorand($i) . " status = '" . $status . "'";
$i = $i + 1;
}
if (!empty($technician))
{
$whereClause = $whereClause . whereorand($i) . " assignedto =" . $technician ;
$i = $i + 1;
}
if (!empty($categoryid))
{
$whereClause = $whereClause . whereorand($i) . " categoryid =" . $categoryid ;
$i = $i + 1;
}
if (!empty($severity))
{
$whereClause = $whereClause . whereorand($i) . " severity ='" . $severity . "'" ;
$i = $i + 1;
}
if (!empty($firstname))
{
$whereClause = $whereClause . whereorand($i) . " firstname like '%" . $firstname . "%'" ;
$i = $i + 1;
}
if (!empty($lastname))
{
$whereClause = $whereClause . whereorand($i) . " lastname like '%" . $lastname . "%'" ;
$i = $i + 1;
}
if (!empty($email))
{
$whereClause = $whereClause . whereorand($i) . " email like '%" . $email . "%'" ;
$i = $i + 1;
}
if (!empty($description))
{
$whereClause = $whereClause . whereorand($i) . " description like '%" . $description . "%'" ;
$i = $i + 1;
}
// Number of records to display
$nb = 20;
if (!isset($_GET['page'])) $page = 1;
// Used for paging
else $page = intval($_GET['page']);
if (!isset($_GET['total']))
{
$result = mysql_query("Select count(*) as count from calls where status='Open'". mysql_error());
$total = mysql_result($result,'0','count');
}
else $total = intval($_GET['total']);
$debut = ($page - 1) * $nb;
$result = mysql_query("select id, DATE_FORMAT(callstarted, '%m-%d-%y %h:%i %p') as callstarted, DATE_FORMAT(estimatedtime, '%m-%d-%y %h:%i %p') as estimatedtime, firstname, lastname, phonenumber, email, status, categoryid, severity, assignedto, description, resolution from calls " . $whereClause . " order by " . $orderby . " " . $order . mysql_error());
if (!$result) {
die('Invalid query: ' . mysql_error());
}
$noofrows = mysql_num_rows($result);
echo "<br><div align=center>Total Number of calls found in your reports : " . $noofrows . "<br>";
echo "<BR><table width=700 align=center border=0><tr bgcolor=#0066CC class=header><th class=header><a href='opencalls.php?orderby=callstarted&order=" . $order . "'>Call Started</a></th><th class=header><a href='opencalls.php?orderby=firstname&order=" . $order . "'>Full Name</a></th><th font class=header><a href='opencalls.php?orderby=severity&order=" . $order . "'>Severity</a></th><th class=header> <th class=header>Description</th></tr>\n";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<tr bgcolor='#99CCFF'><td><a href='report_page.php?id=$row[id]'>" . $row["callstarted"] ."</a></td><td>" . $row["firstname"] . " " . $row["lastname"] . "</td><td>" . $row["severity"] ."</td><td>" . $row["estimatedtime"] . "</td><td>" . $row["description"] . "</td></tr>\n";
}
echo "</table><br><br>";
$nbpages = ceil($total / $nb);
for($i = 1;$i <= $nbpages;$i ++){
echo '<a href="'.$_SERVER['PHP_SELF'].'?page='.$i. '&total='.$total.'&problemtype='.$categoryid. '&status='.$status.'&technician='.$technician. '&firstname='.$firstname.'&lastname='.$lastname. '&email='.$email.'&description='.$description. '">Page '.$i.'</a>';
if($i < $nbpages) echo ' - ';
}
echo "</div>";
mysql_free_result($result);
mysql_close();
}
else
{
?>
<div align=center><strong><em>Report By</em></strong></div>
<form action="reports.php" method="post">
<table align="center" width="480" border="0" cellspacing="1" cellpadding="1">
<tr>
<td bgcolor="#eeeeee">Status :</strong></td>
<td><select name="status">
<option value=""></option>
<option value="Open">Open</option>
<option value="Closed">Closed</option>
</select></td>
</tr>
<tr>
<td bgcolor="#eeeeee">Severity :</td>
<td><select name="severity">
<option value=""></option>
<option value="High">High</option>
<option value="Medium">Medium</option>
<option value="Low">Low</option>
</select></td>
</tr>
<tr>
<td bgcolor="#eeeeee">Technician :</strong></td>
<td><?php listdropdownwithempty("technician", "technician");?></td>
</tr>
<tr>
<td bgcolor="#eeeeee">Problem Type :</td>
<td><?php listdropdownwithempty("problemtype", "category");?></td>
</tr>
<tr>
<td bgcolor="#eeeeee">First Name :</td>
<td><input name="firstname" type="text"></td>
</tr>
<tr>
<td bgcolor="#eeeeee">Last Name :</td>
<td><input name="lastname" type="text"></td>
</tr>
<tr>
<td bgcolor="#eeeeee">Phone Number :</td>
<td><input name="phonenumber" type="text"></td>
</tr>
<tr>
<td bgcolor="#eeeeee">Email Address :</td>
<td><input name="email" type="text"></td>
</tr>
<tr>
<td bgcolor="#eeeeee">Problem :</td>
<td><input name="description" type="text"></td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td></td>
<td><input class="buttonclass" name="" type="submit" value=" Search"> <input class="buttonclass" name="" type="reset" value=" Reset"> </tr>
</table>
</form>
<h2>Reports by tech are still not availble. Please check back soon</h2>
<?php
}
?>
<?php //include("base.php");
mysql_free_result($rstec);
?>