Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Unanswered: sorting using html forms?

    I am a newbi, but I have created a database for reports, and would like the user to be able to search them based off of species. here is a link with the drop down menu i would like to use, as well as a sample of reports using pagination (I have yet to enter all the reports into sql.)
    http://www.thetrophystalkers.com/aaaaa.php

    Here is the code
    Code:
     Species: <SELECT NAME="species">
    <OPTION VALUE="trout">Trout
    <OPTION VALUE="salmon">Salmon
    <OPTION VALUE="bass">Bass
    <OPTION VALUE="muskies">Muskies
    <OPTION VALUE="browntrout">Brown Trout
    <OPTION VALUE="biggame">Big Game
    <OPTION VALUE="saltwater">Saltwater
    <OPTION VALUE="waterfowl">Waterfowl
    <OPTION VALUE="pheasants">Pheasants
    <OPTION VALUE="icefishing">Ice Fishing
    </SELECT>
            
             <input type="submit" name="submit" value="Search!" />
          </form>
          
          <br><br><br>
         <?php
    // Connects to your Database
    mysql_connect("localhost", "xxxxxxxxx", "xxxxxxxx") or die(mysql_error());
    mysql_select_db("xxxxxxxxxxxxx") or die(mysql_error());
    
    //This checks to see if there is a page number. If not, it will set it to page 1
    if (!(isset($pagenum)))
    {
    $pagenum = 1;
    }
    
    //Here we count the number of results
    //Edit $data to be your query
    $data = mysql_query("SELECT * FROM Reports") or die(mysql_error());
    $rows = mysql_num_rows($data);
    
    //This is the number of results displayed per page
    $page_rows = 5;
    
    //This tells us the page number of our last page
    $last = ceil($rows/$page_rows);
    
    //this makes sure the page number isn't below one, or more than our maximum pages
    if ($pagenum < 1)
    {
    $pagenum = 1;
    }
    elseif ($pagenum > $last)
    {
    $pagenum = $last;
    }
    
    //This sets the range to display in our query
    $max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;
    //This is your query again, the same one... the only difference is we add $max into it
    $data_p = mysql_query("SELECT * FROM Reports ORDER BY id DESC $max") or die(mysql_error());
    
    //This is where you display your query results
    while($info = mysql_fetch_array( $data_p ))
     {
      echo $info['month'];
      echo $info['year'];
          echo "<b>";   echo "<br />";  
       echo $info['title'];    echo "</b>";echo "<br />"; 
       echo $info['prostaff'];
       echo "<br />";   echo "<br />";
     echo "<br />";
       
       echo $info['report'];
      echo "<br />";echo "<br />"; echo "<br />"; echo "<br />"; 
      }
    echo "<p>";
    echo "<center>";
    
    // This shows the user what page they are on, and the total number of pages
    echo " --Page $pagenum of $last-- <p>";
    
    // First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
    if ($pagenum == 1)
    {
    }
    else
    {
    echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
    echo " ";
    $previous = $pagenum-1;
    echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
    }
    
    //just a spacer
    echo " ---- ";
    
    //This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
    if ($pagenum == $last)
    {
    }
    else {
    $next = $pagenum+1;
    echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
    echo " ";
    echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
    echo "</center>";
    }
    ?>
    is this possible and can anyone help me. thanks

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The header for your post says you want to sort by fields while the body of the post looks like you want to restrict by certain fields. To sort by different fields just have a link at the top of each column that allows you to change the value of the order field.
    Code:
    <a href=aaaa.php?order_field=title>Title</a>
    I'd suggest using a table rather than just echoing the values out. I'd also use a scrollable table rather than grabbing the data again. Using select * isn't good because it makes it difficult to see what's being pulled from the database and tends to cause errors if the table changes.

    To restrict by certain species just add a where clause to your select statement and pass this field back via your form.

    Code:
    $sql = "SELECT * FROM Reports";
    
    if ( "$species" > "" ) {
        $sql .= " where species = '$species'";
    }
    
    if ( "$order_field" > "" ) {
        $sql .= " order by $order_field";
    }
    
    $data = mysql_query($sql) or die(mysql_error());
    PS your web link doesn't work.

    Mike

Posting Permissions

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