Need some help with a search function please :)

[jbrill]

Hey guys,

 

Im trying to create a simple search function to sort through my database.

 

I have a form:

<table border="0" width="80%" bgcolor="#ffffff" class="MainBody1">
<tr>
<td>


Organize by:
<form action="organize.php" method="get">
<select name="status">
<option selected value=''>Select Status</option>
<?
$statussql = "SELECT DISTINCT status FROM status";
$cats = mysql_query($statussql);
$status = mysql_fetch_array($cats);
do
{
echo "<option value='".$status["status"]."'>".$status["status"]."</option>";
} while ($status = mysql_fetch_array($cats));
?>
</select>


</td>

<td>


Or Client:


<select name="customer">
<option selected value=''>Select Client</option>
<?

$clientsql = "SELECT DISTINCT name FROM dealers";
$cats = mysql_query($clientsql);
$client = mysql_fetch_array($cats);

do

{
echo "<option value='".$client["name"]."'>".$client["name"]."</option>";


} while ($client = mysql_fetch_array($cats));



?>
</select>



</td>

<td>


Or Search by:

<input class="text" type="text" name="keyword" size="20"/>
<input type="submit" class="go" value="Go!"/>
</form>
</td>
</tr>
</table>

This  form produces this URL once it is submitted(providing i haven't entered anything in the fields/dropdowns).

http://www.myurl.com/admin/organize.php?status=&customer=&keyword=

 

I need to create the page organize.php so that it sorts the correct results. could someone please help me write this script?

 

The display of the results looks like this:

<?
echo"<table border=\"0\" width=\"80%\" bgcolor=\"#ffffff\" class=\"MainBody1\" cellpaddin=\"0\" cellspacing=\"0\">";
echo"<tr>";
echo"<td class=\"underline\">Quote Number</td><td class=\"underline\">Job Number</td><td class=\"underline\">PO Number</td><td class=\"underline\">Customer</td><td class=\"underline\">Status</td><td class=\"underline\">Action</td>";
echo"<tr>";

$result = mysql_query("SELECT * FROM jobs WHERE status LIKE '%".$_REQUEST['status']."%'");

while($row = mysql_fetch_array($result))
{

  
  
  
  echo "<tr><td class=\"underlineLight\">" . $row['id'] .  "</td><td class=\"underlineLight\">"  . $row['job_number'] ."</td><td class=\"underlineLight\">" . $row['po_number'] . "</td><td class=\"underlineLight\">" . $row['customer'] . "</td><td class=\"underlineLight\">" . $row['status'] . "</td><td class=\"underlineLight\"> 
  
  
  <a class=\"link\" href=\"admin_modjob.php?idr=".$row['id']."&table=jobs\"><img src=\"images/actionEdit.gif\" border=\"0\"></a> <a class=\"link\" href=\"admin_delete.php?idr=".$row['id']."&table=jobs\"><img src=\"images/actionDelete.gif\" border=\"0\"></a>
  
  
  </td></tr>";
  
  
}
echo"</table>";
?>

 

 

 

[jbrill]

anyone?

[OLG]

What kind of sorting are you after?

 

GROUP BY column

 

In your MYSQL select statement will organize the data according to the column.

 

You need to be more specific.

[jbrill]

lets say i select the "status" to "completed" in the "status" drop down. once i click ok i would liek it to go to organize.php and only display items with the status = completed

[shamilton]

lets say i select the "status" to "completed" in the "status" drop down. once i click ok i would liek it to go to organize.php and only display items with the status = completed

 

Pretty simple...

 

If you take the URL:

 

http://www.myurl.com/admin/organize.php?status=&customer=&keyword=

 

and lets say you have status = completed -> http://www.myurl.com/admin/organize.php?status=completed&customer=&keyword=

 

Then say:

 

$status = (isset($_GET["status"])) ? $_GET["status"] : NULL;
$customer= (isset($_GET["customer"])) ? $_GET["customer"] : NULL;
$keyword= (isset($_GET["keyword"])) ? $_GET["keyword"] : NULL;

 

Then :

 

$myTable = "Your Table Goes Here";
$query = "";
$query = ($status != "" && $status != NULL) ? ($query = "") ? "WHERE status = '$status'" : " AND status = '$status'" : "";
$query = ($customer!= "" && $customer!= NULL) ? ($query = "") ? "WHERE customer= '$customer'" : " AND customer= '$customer'" : NULL;
$query = ($keyword!= "" && $keyword!= NULL) ? ($query = "") ? "WHERE keyword= '$keyword'" : " AND keyword= '$keyword'" : NULL;
$query = "SELECT * FROM $myTable " & $query

 

I hope that helps...

 

[jbrill]

that didnt seem to work.... any more ideas?

[shamilton]

Could you please tell me your sql,php and URL?