[RESOLVED] Sort table by # and a-z

wiccanwoman1103 · Oct 20, 2005

I have a table that I would like sorted by the user. The table headings need to be links. When the user clicks on WO# for example it needs to sort numerically. When the user clicks on User it needs to sort alphabetically. Here is the code that I have already. The table headings are what need to be links and those are outside of the php tags. I really need help on this one this page is due today!!!!!

<?php
	$num = trim($_POST["num"]);
	$user = addslashes(trim ($_POST["user"]));
	$start_date = trim ($_POST["start_date"]);
	$end_date = trim ($_POST["end_date"]);
	$campus = trim ($_POST["campus"]);
	$building_alfred = addslashes(trim ($_POST["building_alfred"]));
	$building_wellsville = addslashes(trim ($_POST["building_wellsville"]));
	$room = addslashes(trim ($_POST["room"]));
	$problem = trim ($_POST["problem"]);
	$summary = addslashes(trim ($_POST["summary"]));
	$description = addslashes(trim ($_POST["description"]));
	$status = trim($_POST["status"]);
?>

<?php 
echo ("<span class='title'>Fields Searched:<br><br></span> ");
$sql = "";

if(!empty($num)){
	echo("<span class='title'>Work Order #: </span>".$num.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
	} 
	$sql = $sql." id LIKE '%$num%' ";
}

if(!empty($user)){
	echo("<span class='title'>Requestor: </span>".$user.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Requestor_Name LIKE '%$user%' ";
}	

if(!empty($campus)){
	echo("<span class='title'>Campus: </span>".$campus.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Campus LIKE '%$campus%' ";
}	

if(!empty($building_alfred)){
	echo("<span class='title'>Building: </span>".$building_alfred.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Building LIKE '%$building_alfred%' ";
}	

if(!empty($building_wellsville)){
	echo("<span class='title'>Building: </span>".$building_wellsville.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Building LIKE '%$building_wellsville%' ";
}			

 if(!empty($room)){
 	echo("<span class='title'>Room: </span>".$room.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Room LIKE '%$room%' ";
}	

 if(!empty($problem)){
 	echo("<span class='title'>Problem Type: </span>".$problem.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 	
		$sql = $sql." Problem LIKE '%$problem%' ";
}

if(!empty($summary)){
	echo("<span class='title'>Summary: </span>".$summary.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 	
		$sql = $sql." Summary LIKE '%$summary%' ";
}

if(!empty($description)){
	echo("<span class='title'>Description: </span>".$description.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 	
		$sql = $sql." Description LIKE '%$description%' ";
}

if(!empty($status)){
	echo("<span class='title'>Status: </span>".$status.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Status LIKE '%$status%' ";
}

if(!empty($start_date)){
	echo("<span class='title'>Start Date: </span>".$start_date.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 	
		$sql = $sql." Date_Submitted  >= STR_TO_DATE('$start_date',GET_FORMAT(DATETIME,'ISO')) ";
}

if(!empty($end_date)){
	echo("<span class='title'>End Date: </span>".$end_date.'&nbsp;&nbsp;&nbsp;&nbsp;');
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Date_Submitted  <= STR_TO_DATE('$end_date 23:59:59',GET_FORMAT(DATETIME,'ISO')) ";
}		

$sql = "SELECT * FROM workorder ".$sql.";";

echo ("<br>");

?>
<?php
$link = mysql_connect("localhost", "uname", "pass");
mysql_select_db("helpdesk", $link);

$results = mysql_query($sql, $link);

$num_rows = mysql_num_rows($results);

echo "<br> Records Found:\n $num_rows";

?> 

<div id="content">
	<table align="center" class="styled_results">
    <tr>
		<th class="clean">Edit</th>
		<th>W.O.#</th>
		<th>Requestor</th>
		<th>Date Submitted</th>
		<th>Location</th>
		<th>Problem Type</th>
		<th>Summary</th>
		<th>Status</th>
	</tr>
<?php
$myRow = 0;  

while ($row = mysql_fetch_assoc($results)) {
   $myClass = ($myRow % 2) ? "" : " class='odd'";   

   echo '<tr align="center">';
   echo '<td'.$myClass.'> <img src="../common/images/edit.gif"> </td>'; 
   echo '<td'.$myClass.'>'.$row["id"].'</td>';
   echo '<td'.$myClass.'>'.$row["Requestor_Name"].'</td>';
   echo '<td'.$myClass.'>'.$row["Date_Submitted"].'</td>';
   echo '<td'.$myClass.'>'.$row["Campus"].'<br>'.$row["Building"].'<br>'.$row["Room"];
   echo '<td'.$myClass.'>'.$row["Problem"].'</td>';
   echo '<td'.$myClass.'>'.$row["Summary"].'</td>';
   echo '<td'.$myClass.' class = "wrap">'.$row["Status"].'</td>';
   echo '</tr>';

   $myRow++; 
   }
mysql_free_result($results);
?>
	</table>
	<br>
<div align="center"><INPUT TYPE="button" onClick="parent.location='admin_search.php'" value="Start a New Search"></div>
</div>
Please help me ....  :confused:

bpat1434 · Oct 20, 2005

Just pass a $_GET variable through the URL and have it say:

?s=WO (for work order #)
?s=requestor (for requestor)
etc., etc.

Then just make you're query dynamic by:

SELECT .... ORDER BY $_GET['s'] ASC

Obviously, if your table headings are not wo, requestor and the like, you'll need to modify the $_GET['s'] before adding it to your SQL query.

<?php


$columns = Array('wo' => 'id', 
'requestor' => 'Requestor_Name', 
'date' => 'Date_Submitted', 
'location' => 'Campus, Building, Room', 
'problem' => 'Problem', 
'summary' => 'Summary', 
'status' => 'Status');
// Define the mySQL column heads associated with the $_GET['s'] values
// "s_value" => "mySQL_column_name"

if(!isset($_GET['s']) || empty($_GET['s']))
{
    $_GET['s'] == 'wo';
}

$query = "SELECT * FROM '.$workordertable.' ORDER BY '.$columns[$_GET['s']].' ASC";

// Do whatever else here

?>

 <th class="clean">Edit</th>
<th><a href="?s=wo">W.O.#</a></th>
<th><a href="?s=requestor">Requestor</a></th>
<th><a href="?s=date">Date Submitted</a></th>
<th><a href="?s=location">Location</a></th>
<th><a href="?s=problem">Problem Type</a></th>
<th><a href="?s=summary">Summary</a></th>
<th><a href="?s=status">Status</a></th>

<?php

// More php code....

?>

~Brett

wiccanwoman1103 · Oct 20, 2005

I have to keep all of the current code like the <td'.$myClass.'>. is that possible and if so how do i work the code in

bpat1434 · Oct 20, 2005

I answered your question. You add the code I posted above. I'm not going to finish your project for you. If you have other questions, I'll be happy to answer them, but I'm not going to write your script.

I've given plenty of "landmarks" of your code in my code that you can figure it out.

<?php
$num = trim($_POST["num"]);
$user = addslashes(trim ($_POST["user"]));
$start_date = trim ($_POST["start_date"]);
$end_date = trim ($_POST["end_date"]);
$campus = trim ($_POST["campus"]);
$building_alfred = addslashes(trim ($_POST["building_alfred"]));
$building_wellsville = addslashes(trim ($_POST["building_wellsville"]));
$room = addslashes(trim ($_POST["room"]));
$problem = trim ($_POST["problem"]);
$summary = addslashes(trim ($_POST["summary"]));
$description = addslashes(trim ($_POST["description"]));
$status = trim($_POST["status"]);
?>

<?php
echo ("<span class='title'>Fields Searched:<br><br></span> ");

// your SQL if() statements here

$columns = Array('wo' => 'id', 
'requestor' => 'Requestor_Name', 
'date' => 'Date_Submitted', 
'location' => 'Campus, Building, Room', 
'problem' => 'Problem', 
'summary' => 'Summary', 
'status' => 'Status');
// Define the mySQL column heads associated with the $_GET['s'] values
// "s_value" => "mySQL_column_name"

if(!isset($_GET['s']) || empty($_GET['s']))
{
    $_GET['s'] == 'wo';
}

$query = "SELECT * FROM workorder '.$sql.' ORDER BY '.$columns[$_GET['s']].' ASC";

echo ("<br>");

?>
<?php
$link = mysql_connect("localhost", "uname", "pass");
mysql_select_db("helpdesk", $link);

$results = mysql_query($sql, $link);

$num_rows = mysql_num_rows($results);

echo "<br> Records Found:\n $num_rows";

?>

<div id="content">
<table align="center" class="styled_results">
<tr>
<th class="clean">Edit</th>
<th><a href="?s=wo">W.O.#</a></th>
<th><a href="?s=requestor">Requestor</a></th>
<th><a href="?s=date">Date Submitted</a></th>
<th><a href="?s=location">Location</a></th>
<th><a href="?s=problem">Problem Type</a></th>
<th><a href="?s=summary">Summary</a></th>
<th><a href="?s=status">Status</a></th>
</tr>
<?php

// Display row info & finish table

?>

~Brett

wiccanwoman1103 · Oct 20, 2005

I am not asking you to do that I figured out that the way my pages are set up i need to use java script on click events for the sorts thank you

bpat1434 · Oct 20, 2005

you don't "need" to use javascript. You can always re-post the script back to itself, and do it that way.

~Brett

[RESOLVED] Sort table by # and a-z

Wwiccanwoman1103

bpat1434

Wwiccanwoman1103

bpat1434

Wwiccanwoman1103

bpat1434