...

View Full Version : sort table using sql query



wiccanwoman1103
10-25-2005, 08:36 PM
I need to order a table using sql I have multiple queries on a page. See code below. I need the form to resubmit to itself using a javascript onclick. The table headings need to be links to order the table. Any help?????


<?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>
<input type="hidden" name="user" value="<?php echo($user) ?>" />
<input type="hidden" name="summary" value="<?php echo($summary) ?>" />
<input type="hidden" name="department" value="<?php echo($department) ?>" />
<input type="hidden" name="problem" value="<?php echo($problem) ?>" />
<input type="hidden" name="campus" value="<?php echo($campus) ?>" />
<input type="hidden" name="building" value="<?php echo($building) ?>" />
<input type="hidden" name="room" value="<?php echo($room) ?>" />
<input type="hidden" name="description" value="<?php echo($description) ?>" />

bigtiger
11-04-2005, 09:23 AM
you can have a look in mysql select statment.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum