Advanced Search

Results 1 to 8 of 8

Thread: Sorting My Database

  1. #1
    Join Date
    Apr 2006
    Posts
    584
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Sorting My Database

    Ok I have apage which displays exactly what I want it too the code is as follows
    Code:
    <?php
    $cat = $_GET['cat'];
    
    /* connect to the mysql database and use a query to get the members info */
    
    include 'library/config.php';
    include 'library/opendb.php';
    
    //navigation
    include("nav.php");
    
    
    //approved
    $info = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='$cat'");
    
    echo '<table border="1" cellpadding="3" cellspacing="1">
      <tr valign="top">
        <td>ID</td>
    	<td>First Name</td>
        <td>Last Name</td>
        <td>State</td>
    	<td>Application Date</td>
        <td>Last Login</td>
      </tr>';
    
    if (mysql_num_rows($info) < 1) {
    echo '<tr valign="top">
      <td colspan="6">There are no members that match the query. Please go back and try again</td>
        </tr>';
    }
    
    else {
       while ($qry = mysql_fetch_array($info)) {
    
    //create the layout
    ?>
    <link href="cs_style.css" rel="stylesheet" type="text/css" />
    
      <tr valign="top">
        <td><?php echo $qry['ID']; ?></td>
    	<td><?php echo $qry['FirstName']; ?></td>
        <td><?php echo $qry['LastName']; ?></td>
        <td><?php echo $qry['State']; ?></td>
    	<td><?php echo date('d/m/Y', strtotime($qry['JoinDate'])); ?></td>
        <td><?php echo($qry['loginDateTime']?date('d/m/Y H:i:s', strtotime($qry['loginDateTime'])):'N/A') ?></td>
      </tr>
    <?php
       }
    }
    
    echo '</table>';
    ?>
    I have been also able to build (with a little help) a page which just displays all my tblmembers seperately, the beauty of this page is the headings are automatically populated and I can click them to sort by FirstName, LastName etc... Here is the code for that page
    Code:
    <link href="cs_style.css" rel="stylesheet" type="text/css" />
    <?php
    
    /* connect to the mysql database and use a query to get the members info */
    
    include 'library/config.php';
    include 'library/opendb.php';
    
    /* set the allowed order by columns */
    $default_sort = 'LastName';
    $allowed_order = array ('JoinDate', 'FirstName','LastName');
    
    /* if order is not set, or it is not in the allowed
     * list, then set it to a default value. Otherwise, 
     * set it to what was passed in. */
    if (!isset ($_GET['order']) || 
        !in_array ($_GET['order'], $allowed_order)) {
        $order = $default_sort;
    } else {
        $order = $_GET['order'];
    }
    
    /* construct and run our query */
    $query = "SELECT * FROM tblmembers ORDER BY $order";
    $result = mysql_query ($query);
    
    /* make sure data was retrieved */
    $numrows = mysql_num_rows($result);
    if ($numrows == 0) {
        echo "No data to display!";
        exit;
    }
    
    /* now grab the first row and start the table */
    $row = mysql_fetch_assoc ($result);
    echo "<TABLE border=1>\n";
    echo "<TR>\n";
    foreach ($row as $heading=>$column) {
        /* check if the heading is in our allowed_order
         * array. If it is, hyperlink it so that we can
         * order by this column */
        echo "<TD><b>";
        if (in_array ($heading, $allowed_order)) {
            echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
        } else {
            echo $heading;
        }                
        echo "</b></TD>\n";
    }
    echo "</TR>\n";
    
    /* reset the $result set back to the first row and 
     * display the data */
    mysql_data_seek ($result, 0);
    while ($row = mysql_fetch_assoc ($result)) {
        echo "<TR>\n";
        foreach ($row as $column) {
            echo "<TD>$column</TD>\n";
        }
        echo "</TR>\n";
    }
    echo "</TABLE>\n";
    ?>
    If anyone at all could help me make the first lot of code do what the second does, but keeping all the info I need in the first bit of code would be great! I'm a newbie I've come this far but am hoping for any help! Thanks all in advance!

  2. #2
    Join Date
    Sep 2006
    Location
    St. George, UT
    Posts
    2,769
    Thanks
    3
    Thanked 157 Times in 155 Posts

    Default

    Try something like the following.

    Code:
    <?php
    $cat = $_GET['cat'];
    
    /* connect to the mysql database and use a query to get the members info */
    
    include 'library/config.php';
    include 'library/opendb.php';
    
    //navigation
    include("nav.php");
    
    
    /* set the allowed order by columns */
    
    $default_sort = 'LastName';
    $allowed_order = array ('JoinDate', 'FirstName','LastName');
    
    /* if order is not set, or it is not in the allowed
     * list, then set it to a default value. Otherwise, 
     * set it to what was passed in. */
    if (!isset ($_GET['order']) || 
        !in_array ($_GET['order'], $allowed_order)) {
        $order = $default_sort;
    } else {
        $order = $_GET['order'];
    }
    
    /* construct and run our query */
    $query = "SELECT * FROM tblmembers WHERE `MemberApproved`='$cat'   ORDER BY $order";
    
    $result = mysql_query ($query);
    
    /* make sure data was retrieved */
    $numrows = mysql_num_rows($result);
    if ($numrows == 0) {
        echo "No data to display!";
        exit;
    }
    
    /* now grab the first row and start the table */
    $row = mysql_fetch_assoc ($result);
    echo "<TABLE border=1>\n";
    echo "<TR>\n";
    foreach ($row as $heading=>$column) {
        /* check if the heading is in our allowed_order
         * array. If it is, hyperlink it so that we can
         * order by this column */
        echo "<TD><b>";
        if (in_array ($heading, $allowed_order)) {
            echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
        } else {
            echo $heading;
        }                
        echo "</b></TD>\n";
    }
    echo "</TR>\n";
    
    /* reset the $result set back to the first row and 
     * display the data */
    mysql_data_seek ($result, 0);
    while ($row = mysql_fetch_assoc ($result)) {
        echo "<TR>\n";
        foreach ($row as $column) {
            echo "<TD>$column</TD>\n";
        }
        echo "</TR>\n";
    }
    echo "</TABLE>\n";
    ?>
    That's basically it. Hope this helps.
    "Computer games don't affect kids; I mean if Pac-Man affected us as kids, we'd all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music." - Kristian Wilson, Nintendo, Inc, 1989
    TheUnlimitedHost | The Testing Site | Southern Utah Web Hosting and Design

  3. #3
    Join Date
    Apr 2006
    Posts
    584
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Yay well that worked! Is it possible to only make it show the FirstName, LastName, State, Application Date, LastLogin...

  4. #4
    Join Date
    Apr 2006
    Posts
    584
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Oh just clicked on any of the sort fields when I do that it reloads the page with only 2 members...

  5. #5
    Join Date
    Apr 2006
    Posts
    584
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    I looked through all the code but can't see why it would only show 2 members when I click on the sort function...

  6. #6
    Join Date
    Sep 2006
    Location
    St. George, UT
    Posts
    2,769
    Thanks
    3
    Thanked 157 Times in 155 Posts

    Default

    Ok, try this (notice the part in red that I added (or forgot to add before):

    Code:
    <?php
    $cat = $_GET['cat'];
    
    /* connect to the mysql database and use a query to get the members info */
    
    include 'library/config.php';
    include 'library/opendb.php';
    
    //navigation
    include("nav.php");
    
    
    /* set the allowed order by columns */
    
    $default_sort = 'LastName';
    $allowed_order = array ('JoinDate', 'FirstName','LastName');
    
    /* if order is not set, or it is not in the allowed
     * list, then set it to a default value. Otherwise, 
     * set it to what was passed in. */
    if (!isset ($_GET['order']) || 
        !in_array ($_GET['order'], $allowed_order)) {
        $order = $default_sort;
    } else {
        $order = $_GET['order'];
    }
    
    /* construct and run our query */
    $query = "SELECT * FROM tblmembers WHERE `MemberApproved`='$cat'   ORDER BY $order";
    
    $result = mysql_query ($query);
    
    /* make sure data was retrieved */
    $numrows = mysql_num_rows($result);
    if ($numrows == 0) {
        echo "No data to display!";
        exit;
    }
    
    /* now grab the first row and start the table */
    $row = mysql_fetch_assoc ($result);
    echo "<TABLE border=1>\n";
    echo "<TR>\n";
    foreach ($row as $heading=>$column) {
        /* check if the heading is in our allowed_order
         * array. If it is, hyperlink it so that we can
         * order by this column */
        echo "<TD><b>";
        if (in_array ($heading, $allowed_order)) {
            echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&cat=$cat\">$heading</a>";
        } else {
            echo $heading;
        }                
        echo "</b></TD>\n";
    }
    echo "</TR>\n";
    
    /* reset the $result set back to the first row and 
     * display the data */
    mysql_data_seek ($result, 0);
    while ($row = mysql_fetch_assoc ($result)) {
        echo "<TR>\n";
        foreach ($row as $column) {
            echo "<TD>$column</TD>\n";
        }
        echo "</TR>\n";
    }
    echo "</TABLE>\n";
    ?>
    Hope this helps.
    "Computer games don't affect kids; I mean if Pac-Man affected us as kids, we'd all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music." - Kristian Wilson, Nintendo, Inc, 1989
    TheUnlimitedHost | The Testing Site | Southern Utah Web Hosting and Design

  7. #7
    Join Date
    Apr 2006
    Posts
    584
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Ok yes that worked! I noticed though, for some reason I thought the sort had a DESC as well as ASC, so I could sort the reverse... And also how can I remove some of the fields, it shows all the columns of tblemembers...

  8. #8
    Join Date
    Apr 2006
    Posts
    584
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    You're gonna hate me! But it also lost the way I had the date displayed, can I get that back?
    Code:
    echo date('d/m/Y', strtotime($qry['JoinDate']));
    echo($qry['loginDateTime']?date('d/m/Y H:i:s', strtotime($qry['loginDateTime'])):'N/A')

Bookmarks

Posting Permissions

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