Page 1 of 1
PHP Search page help
Posted: Mon Oct 01, 2007 8:48 am
by clmowers
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
Posted: Mon Oct 01, 2007 8:56 am
by s.dot
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.

Posted: Mon Oct 01, 2007 1:18 pm
by clmowers
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
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);
?>
Posted: Mon Oct 01, 2007 1:49 pm
by clmowers
Also here is how my database is setup. Just incase if you need it
id int 10
firstname varchar50
lastname varchar50
phonenumber varchar50
email varchar50
categoryid varchar50
severity varchar50
status varchar50
assignedto varchar50
description longtext
resolution longtext
the ID feild is PK and auto