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 3 of 3
  1. #1
    New Coder
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Need php/mysql help

    Need help fixing some php code. I have code which paginates output from a mysql database (sort of like a guest book). I am trying to sort data on a particular database column--rather than print all entries-- and paginate output. My code works for the first page of output, but when the page is reloaded to process second page the rest of the database entries are listed. There should be an easy fix, but it hasn't come to me yet. Any thoughts?


    Here is the code:

    <?php
    $msg = "<h2>Select the archive you wish to view.</h2>";
    #create archive viewing button
    $msg.= "<form action=\"guestbook_view.php\" method=\"post\">";
    $msg.="<select name=\"choice\">";
    $msg.="<option value=\"All\">All Archives</option>";
    $msg.="<option value=\"Male\">Male</option>";
    $msg.="<option value=\"Female\">Female</option>";
    $msg.="<option value=\"Alabama\">Alabama</option>";
    $msg.="<option value=\"Alaska\">Alaska</option>";
    $msg.="<option value=\"Arizona\">Arizona</option>";
    $msg.="<option value=\"Arkansas\">Arkansas</option>";
    $msg.="<option value=\"California\">California</option>";
    $msg.="<option value=\"Colorado\">Colorado</option>";
    $msg.="<option value=\"Connecticut\">Connecticut</option>";
    $msg.="<option value=\"Delaware\">Delaware</option>";
    $msg.="<option value=\"Florida\">Florida</option>";
    $msg.="<option value=\"Georgia\">Georgia</option>";
    $msg.="<option value=\"Hawaii\">Hawaii</option>";
    $msg.="<option value=\"Idaho\">Idaho</option>";
    $msg.="<option value=\"Illinois\">Illinois</option>";
    $msg.="<option value=\"Indiana\">Indiana</option>";
    $msg.="<option value=\"Iowa\">Iowa</option>";
    $msg.="<option value=\"Kansas\">Kansas</option>";
    $msg.="<option value=\"Kentucky\">Kentucky</option>";
    $msg.="<option value=\"Louisiana\">Louisiana</option>";
    $msg.="<option value=\"Maine\">Maine</option>";
    $msg.="<option value=\"Maryland\">Maryland</option>";
    $msg.="<option value=\"Massachusetts\">Massachusetts</option>";
    $msg.="<option value=\"Michigan\">Michigan</option>";
    $msg.="<option value=\"Minnesota\">Minnesota</option>";
    $msg.="<option value=\"Mississippi\">Mississippi</option>";
    $msg.="<option value=\"Missouri\">Missouri</option>";
    $msg.="<option value=\"Montana\">Montana</option>";
    $msg.="<option value=\"Nebraska\">Nebraska</option>";
    $msg.="<option value=\"Nevada\">Nevada</option>";
    $msg.="<option value=\"New Hampshire\">New Hampshire</option>";
    $msg.="<option value=\"New Jersey\">New Jersey</option>";
    $msg.="<option value=\"New Mexico\">New Mexico</option>";
    $msg.="<option value=\"New York\">New York</option>";
    $msg.="<option value=\"North Carolina\">North Carolina</option>";
    $msg.="<option value=\"North Dakota\">North Dakota</option>";
    $msg.="<option value=\"Ohio\">Ohio</option>";
    $msg.="<option value=\"Oklahoma\">Oklahoma</option>";
    $msg.="<option value=\"Oregon\">Oregon</option>";
    $msg.="<option value=\"Pennsylvania\">Pennsylvania</option>";
    $msg.="<option value=\"Puerto Rico\">Puerto Rico</option>";
    $msg.="<option value=\"Rhode Island\">Rhode Island</option>";
    $msg.="<option value=\"South Carolina\">South Carolina</option>";
    $msg.="<option value=\"South Dakota\">South Dakota</option>";
    $msg.="<option value=\"Tennessee\">Tennessee</option>";
    $msg.="<option value=\"Texas\">Texas</option>";
    $msg.="<option value=\"Utah\">Utah</option>";
    $msg.="<option value=\"Vermont\">Vermont</option>";
    $msg.="<option value=\"Virginia\">Virginia</option>";
    $msg.="<option value=\"Washington\">Washington</option>";
    $msg.="<option value=\"West Virginia\">West Virginia</option>";
    $msg.="<option value=\"Wisconsin\">Wisconsin</option>";
    $msg.="<option value=\"Wyoming\">Wyoming</option>";
    $msg.="</select>";

    $msg.="<input type=\"submit\" name=\"submit\" ";
    $msg.="value=\"Visit Archive\"> </form>";
    echo($msg);?>


    <?php

    $self = $_SERVER['PHP_SELF'];
    $referer = $_SERVER['HTTP_REFERER'];
    $choice = $_POST['choice'];

    #connect to MySQL
    $rs = @mysql_connect( "localhost", "xxxx", "xxxxxx" )
    or die( "Could not connect to MySQL" );

    #select the database
    $rs = @mysql_select_db( "my_database" )
    or die( "Could not select database" );



    //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
    if (empty($choice) or $choice=="All")
    {
    $data = mysql_query("SELECT * FROM guestbook") or die(mysql_error());
    $rows = mysql_num_rows($data);
    }
    elseif ($choice=="Male")
    {
    $data = mysql_query("SELECT * FROM guestbook WHERE sex='M' or sex='m'") or die(mysql_error());
    $rows = mysql_num_rows($data);
    }
    elseif ($choice=="Female")
    {
    $data = mysql_query("SELECT * FROM guestbook WHERE sex='F' or sex='f'") or die(mysql_error());
    $rows = mysql_num_rows($data);
    }
    else
    {
    $data = mysql_query("SELECT * FROM guestbook WHERE location='$choice'") or die(mysql_error());
    $rows = mysql_num_rows($data);
    }

    //How many rows retrieved
    //echo($rows);

    //Printing archive search for categories with 0 entries is avoided
    if ($rows==0)
    echo "<h3>There are no entries in this category</h3>";
    else
    { //starts loop for printing entries


    //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 range that we will 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
    if (empty($choice) or $choice=="All")
    $data_p = mysql_query("SELECT * FROM guestbook order by time desc $max") or die(mysql_error());
    elseif ($choice=="Male")
    {
    $data_p = mysql_query("SELECT * FROM guestbook WHERE sex='M' or sex='m' order by time desc $max") or die(mysql_error());
    $rows = mysql_num_rows($data_p);
    }
    elseif ($choice=="Female")
    {
    $data_p = mysql_query("SELECT * FROM guestbook WHERE sex='F' or sex='f' order by time desc $max") or die(mysql_error());
    $rows = mysql_num_rows($data_p);
    }
    else
    {
    $data_p = mysql_query("SELECT * FROM guestbook WHERE location='$choice' order by time desc $max") or die(mysql_error());
    $rows = mysql_num_rows($data_p);
    }

    //This is where you display your query results
    while($row = mysql_fetch_array( $data_p ))
    {
    ?>

    <table class="archive" border="1" width="500">
    <tr>
    <td>you are? <?php echo $row["name"]; ?></td>

    <td>slapping whom? <?php echo $row["slappee"]; ?> </td>
    <td>when? <?php echo $row["day"]; ?></td>
    </tr>
    <tr>
    <td colspan="3">where? <?php echo $row["location"]; ?></td>
    </tr>


    <tr>
    <td colspan="3">and most importantly, why? :<?php echo $row["comments"]; ?></td>
    </tr>
    </table>
    <br />

    <?php } ?>

    <?php
    echo "<p>";

    // 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> ";
    }


    //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> ";
    }
    } //ends else loop for printing entries
    ?>

  • #2
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,096
    Thanks
    2
    Thanked 23 Times in 23 Posts
    I haven't done an in-depth analysis of your code, but one thing I could suggest is that you put some echo statements in strategic places to see what the code is doing. For example, where you are building your query with the limit, add an

    Code:
    echo $data_p;
    into your code. Does your query contain the right limit information? Perhaps your formula for calculating that is incorrect.

    Hope this helps.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #3
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,096
    Thanks
    2
    Thanked 23 Times in 23 Posts
    I received the following PM from the OP concerning this problem:

    Thanks for looking at my code. I have in fact used echo statements in an attempt to find my error. Here's what I have found: I have 9 rows in my database. 6 rows have "Colorado" in the location column. I am outputting 5 entries per page. When I sort on "Colorado," 6 rows are found and the first 5 are printed. When I click the "next" anchor, the 6th, 7th, 8th, and 9th entries in my table are printed. By clicking "next" all rows are re-read and everything past the first 5 count is printed. This is my problem--I'm losing the sorted array by clicking "next." Any ideas on these issues?
    You're not trying to sort the data after selecting it from the database, are you? That may be the source of your problem. You can have your query do that for you. For example:

    Code:
    SELECT * FROM guestbook ORDER BY state
    or whatever the field name is that you want to sort on.

    Hope this helps.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!


  •  

    Posting Permissions

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