PHP Search page help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
clmowers
Forum Newbie
Posts: 4
Joined: Mon Oct 01, 2007 8:41 am

PHP Search page help

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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. ;)
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.
clmowers
Forum Newbie
Posts: 4
Joined: Mon Oct 01, 2007 8:41 am

Post 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>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
   <tr>
    <td></td>
    <td><input class="buttonclass" name="" type="submit" value="  Search">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<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);
?>
clmowers
Forum Newbie
Posts: 4
Joined: Mon Oct 01, 2007 8:41 am

Post 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
Post Reply