Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Oct 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation sort table using sql query

    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?????
    Code:
    <?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) ?>" />

  • #2
    New Coder bigtiger's Avatar
    Join Date
    Oct 2005
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you can have a look in mysql select statment.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •