SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Location
    Raleigh, NC
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    displaying data from a database in pages

    Allright I'm reading rows from a database onto a table, I already got all the code and its working. But I cannot figure out how to make it so that if there are more than 30 users it goes to page 2, and then more then 60 goes to page 3.

    and just for reference this is my code. Is there anyway to shorten all this so I didn't have to type it 3 time like that? like using an array or something?

    PHP Code:
    //Displays all the members in he database

        //Include Files
            //Constants
                
    require_once("/home/techmtg/public_html/Functions/DefineGlobalConstants.php");
                
    defineconstants();
            
            
    //Functions
                
    require_once(FILECONNECTTODB);

        
    //Header
            
    require_once(FILEHEADER);

        
    //Connect to DB
            
    dbconnect(DBTMUSERS);

        
    //Grab all the users
            
    $Query "SELECT * FROM Users";
            
    $Result mysql_query($Query);
            
    $NumRows mysql_num_rows($Result);
            
        
    //Display the user information in a table
            
    displayusertable();

        
    //Close DB connection
            
    mysql_close();

        
    //Footer
            
    require_once(FILEFOOTER);

        
    /////////////////////////
        //Functions//////////////
        /////////////////////////
        
            //Display the user information in a table
                
    function displayusertable() {
                    
                    
    //Global Variables
                        
    global $Query$Result$NumRows;
                    
                    
    //Set up the Table
                    
    echo("<table>");
                    echo(
    "<tr>
                            <td width = \"100\">Username</td>
                            <td width = \"100\">Join Date</td>
                            <td width = \"100\">Email</td>
                            <td width = \"100\">AIM</td>
                            <td width = \"100\">MSN</td>
                            <td width = \"100\">ICQ</td>
                            <td width = \"100\">Yahoo</td>
                            <td width = \"100\">Real Name</td>
                            <td width = \"100\">Birthday</td>
                            <td width = \"100\">Location</td>
                            <td width = \"100\">Age</td>
                            <td width = \"100\">Avatar</td>
                            <td width = \"100\">User Level</td>
                            <td width = \"100\">Post Count</td>
                        </tr>"
    );
                    
                        
    //Loop through all the rows
                            
    $i 0;
                            while (
    $i $NumRows) {
        
                                
                                
    //Get the values into variables
                                    
    $LookupUser_Name            mysql_result($Result$i"Name");
                                    
    $LookupUser_CreationDate    mysql_result($Result$i"CreationDate");
                                    
    $LookupUser_Email            mysql_result($Result$i"Email");
                                    
    $LookupUser_AIM                mysql_result($Result$i"AIM");
                                    
    $LookupUser_MSN                mysql_result($Result$i"MSN");
                                    
    $LookupUser_ICQ                mysql_result($Result$i"ICQ");
                                    
    $LookupUser_Yahoo            mysql_result($Result$i"Yahoo");
                                    
    $LookupUser_RealName        mysql_result($Result$i"RealName");
                                    
    $LookupUser_DateOfBirth        mysql_result($Result$i"DateOfBirth");
                                    
    $LookupUser_Location        mysql_result($Result$i"Location");
                                    
    $LookupUser_Age                mysql_result($Result$i"Age");
                                    
    $LookupUser_Avatar            mysql_result($Result$i"Avatar");
                                    
    $LookupUser_Level            mysql_result($Result$i"Level");
                                    
    $LookupUser_PostCount        mysql_result($Result$i"PostCount");
                                
                                
    //Write these onto the table
                                    
    echo("<tr>
                                            <td>" 
    $LookupUser_Name "</td>
                                            <td>" 
    $LookupUser_CreationDate "</td>
                                            <td>" 
    $LookupUser_Email "</td>
                                            <td>" 
    $LookupUser_AIM "</td>
                                            <td>" 
    $LookupUser_MSN "</td>
                                            <td>" 
    $LookupUser_ICQ "</td>
                                            <td>" 
    $LookupUser_Yahoo "</td>
                                            <td>" 
    $LookupUser_RealName "</td>
                                            <td>" 
    $LookupUser_DateOfBirth "</td>
                                            <td>" 
    $LookupUser_Location "</td>
                                            <td>" 
    $LookupUser_Age "</td>
                                            <td>" 
    $LookupUser_Avatar "</td>
                                            <td>" 
    $LookupUser_Level "</td>
                                            <td>" 
    $LookupUser_PostCount "</td>
                                        </tr>"
    );
                                
                                
    $i++;
                            }
                    echo(
    "</table>");
            } 

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Boston, MA
    Posts
    188
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, that code is horrible. Now, to your question...

    For users 1-30 the mysql_query would look like "SELECT * FROM table ... LIMIT 0,30"
    For users 31-60 the mysql_query would look like "SELECT * FROM table ... LIMIT 31,60"

    You would just need to replace the LIMIT numbers with variables... say for example:
    $limit_lower = 0;
    $limit_upper = 30;

    Now, if $_GET['page'] is set maybe something like...
    $limit_lower = 30*($_GET['page']-1)+1;
    $limit_upper = $limit_lower + 29;

    Obviously, you need to make sure a proper number is entered and etc. To figure out how many pages there will be in all and make links for them, do a num_rows on the table and divide the number of results by 30 (there are 30 results per page), then you can create links in a for loop for the pages.

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Location
    Raleigh, NC
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    alright thanks, can you tell me why my code is horrible (I sorta had a feeling that was a bad way of dong that ><).

    edit...
    Actualy I think I figured it out myself, I'm putting all that into a function, For some reason I was thinking it had to be done in that order, but now looking at it it dosent. Annything else I'm doing wrong there?

    edit...
    Is this better?
    PHP Code:
    //Displays all the members in he database

        //Include Files
            //Constants
                
    require_once("/home/techmtg/public_html/Functions/DefineGlobalConstants.php");
                
    defineconstants();
            
            
    //Functions
                
    require_once(FILECONNECTTODB);

        
    //Header
            
    require_once(FILEHEADER);

        
    //Connect to DB
            
    dbconnect(DBTMUSERS);

        
    //Grab all the users
            
    $Query "SELECT * FROM Users";
            
    $Result mysql_query($Query);
            
    $NumRows mysql_num_rows($Result);
            
        
    //Display the user information in a table
            
    displayusertable();

        
    //Close DB connection
            
    mysql_close();

        
    //Footer
            
    require_once(FILEFOOTER);

        
    /////////////////////////
        //Functions//////////////
        /////////////////////////
        
            //Display the user information in a table
                
    function displayusertable() {
                    
                    
    //Global Variables
                        
    global $Query$Result$NumRows;
                    
                    
    //Set up the Table
                    
    echo("<table>");
                    echo(
    "<tr>
                            <td width = \"100\">Username</td>
                            <td width = \"100\">Join Date</td>
                            <td width = \"100\">Email</td>
                            <td width = \"100\">AIM</td>
                            <td width = \"100\">MSN</td>
                            <td width = \"100\">ICQ</td>
                            <td width = \"100\">Yahoo</td>
                            <td width = \"100\">Real Name</td>
                            <td width = \"100\">Birthday</td>
                            <td width = \"100\">Location</td>
                            <td width = \"100\">Age</td>
                            <td width = \"100\">Avatar</td>
                            <td width = \"100\">User Level</td>
                            <td width = \"100\">Post Count</td>
                        </tr>"
    );
                    
                        
    //Loop through all the rows
                            
    $i 0;
                            while (
    $i $NumRows) {
        
                                
                                
    //Get info from DB and write on table
                                    
    echo("<tr>");
                                            
    printfield(Name$i);
                                            
    printfield(CreationDate$i);
                                            
    printfield(Email$i);
                                            
    printfield(AIM$i);
                                            
    printfield(MSN$i);
                                            
    printfield(ICQ$i);
                                            
    printfield(Yahoo$i);
                                            
    printfield(RealName$i);
                                            
    printfield(DateOfBirth$i);
                                            
    printfield(Location$i);
                                            
    printfield(Age$i);
                                            
    printfield(Avatar$i);
                                            
    printfield(Level$i);
                                            
    printfield(PostCount$i);
                                    echo(
    "</tr>");
                                
                                
    $i++;
                            }
                    echo(
    "</table>");
            }
            
            
    //Prints off a row
                
    function printfield($FieldName$counter) {
                
                    
    //Global Variables
                        
    global $Query$Result$NumRows;            
                    
                    
    //Get info and print it
                        
    $LookupUser[$FieldName]        = mysql_result($Result$counter$FieldName);
                        echo(
    "<td>" $LookupUser[$FieldName] . "</td>");
                } 
    Last edited by ecensane; Oct 13, 2005 at 20:58.

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Boston, MA
    Posts
    188
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, for one, you're running a separate query for every table row, something that could easily be contained into a single SELECT statement... so, for a single record you're running 14 queries instead of 1, for two you're running 28 instead of 2, ... you can see how inefficient this is. Your function layout doesn't really make a lot of sense and you're using globals which isn't really a good practice either.

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Location
    Raleigh, NC
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how do you run that all in 1 select statement?

  6. #6
    SitePoint Member
    Join Date
    Oct 2005
    Location
    Pennsylvania
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To me, it looks like you are running it all in one select, but you're using mysql_result to get at the data. What's better to do is to use something like mysql_fetch_row() or the like to get all the data into an array.

    Also, there are some tutorials that explain how to paginate your data; and there is also a Pear class called Pager (which I generally use), that helps a lot in this process. Look it up at http://pear.php.net/manual/en/

  7. #7
    SitePoint Enthusiast thenetmaker's Avatar
    Join Date
    Sep 2004
    Location
    N.Ireland
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been trying to create multiple pages for an image gallery using the pagination tutorials on phpfreaks.com but like the example above the use the LIMIT function in the mysql_query line. Unfortunately this only returns a syntax error when run in my script:

    $result = mysql_query('SELECT gallery_customers.ID, AuthorID, Name, Camera, Accessories, Settings, photographers.ID, Author
    FROM gallery_customers, photographers LIMIT $from, $max_results
    WHERE AuthorID=photographers.ID
    ORDER BY gallery_customers.ID DESC');

    I've tried moving the LIMIT $from, $max_results around within this query but it still doesn't work. $from and $max_results are predefined above the mysql_query and I've tested them successfully, it's only the LIMIT function which is creating a problem.

  8. #8
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Sutton, Surrey
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you attempted to read the manual at http://dev.mysql.com/doc/refman/4.1/en/select.html you would see that the OFFSET and LIMIT clauses go after ORDER BY.

  9. #9
    SitePoint Enthusiast thenetmaker's Avatar
    Join Date
    Sep 2004
    Location
    N.Ireland
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Still no luck

    Sounds good in theory but as I mentioned, I've tried that too and got the same result:

    "You have an error in your SQL syntax near '$from, $max_results' at line 4"

    Complete Code:

    PHP Code:
    /* Number of records on each row */
    $in_rows 3;
    $count2 0;

    /* If current page number, use it, if not, set one! */
    if(!isset($_GET['page'])){ 
        
    $page 1
    } else { 
        
    $page $_GET['page']; 


    $max_results 12;    // Define the number of results per page 

    // Figure out the limit for the query based 
    // on the current page number. 
    $from = (($page $max_results) - $max_results);

    /* Retrieve Brand & Extract Table Headers & define $result as chosen Brand */
        
    $result mysql_query('SELECT gallery_customers.ID, AuthorID, Name, Camera, Accessories, Settings, photographers.ID, Author 
        FROM gallery_customers, photographers 
        WHERE AuthorID=photographers.ID 
        ORDER BY gallery_customers.ID DESC LIMIT $from, $max_results'
    );
        if (!
    $result) {
            die( 
    '<span class="resultbad">Error retrieving results from gallery database B.<br />' mysql_error().'</span>' );
        }

    /* Extract contents of $result array & format for viewing */
    while ( $row mysql_fetch_array($result) ) {
        
    $ID $row['0'];
        
    $AuthorID $row['AuthorID'];
        
    $Name $row['Name'];
        
    $Camera $row['Camera'];
        
    $Accessories $row['Accessories'];
        
    $Settings $row['Settings'];
        
    $Author $row['Author'];

        echo (
    '<td bgcolor="#FFFFFF" valign="top" align="center" width="178">
        <table width="176" bgcolor="#FF9900" cellspacing="1" cellpadding="4">
          <tr> 
           <td class="description" valign="top" align="center" bgcolor="#FFFFFF"><a href="gallery_customers_detail.php?id='
    .$ID.'"><img src="graphics/gallery/'.$ID.'.jpg" alt="'.$Name.' by '.$Author.'" width="150" border="0"></a></td></tr>
            <tr><td class="description" valign="top" align="center" bgcolor="#FFFFFF"><span class="descriptiontitle">'
    .$Name.'</span><br />
              '
    .$Author.'<br />
              <span class="descriptiontitle">'
    .$Camera.'</span><br />
              <a href="gallery_customers_detail.php?id='
    .$ID.'">Click Here for Full Details</a><br />
           </td>
          </tr>
        </table>
      </td>'
    );
    $count2++; 
        if (
    $count2 $in_rows == 0
        { 
          echo 
    "</tr><tr>"
        } 


  10. #10
    SitePoint Member
    Join Date
    Oct 2005
    Location
    Pennsylvania
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is because your query is in single quotes, so the variables are not parsed.

  11. #11
    SitePoint Enthusiast thenetmaker's Avatar
    Join Date
    Sep 2004
    Location
    N.Ireland
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, doesn't work either.

  12. #12
    SitePoint Member
    Join Date
    Oct 2005
    Location
    Pennsylvania
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thenetmaker
    Nope, doesn't work either.

    Hi,

    If you want help, you need to give more information than that. What is the exact error? What is the exact code? You posted the code before, but apparently you now made an update so you should post that.

    Brandon

  13. #13
    SitePoint Enthusiast thenetmaker's Avatar
    Join Date
    Sep 2004
    Location
    N.Ireland
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's exactly the same error, I just changed the quotes to double instead of single.
    The rest of the code hasn't changed.

  14. #14
    SitePoint Evangelist Will Kelly's Avatar
    Join Date
    May 2005
    Location
    London
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    after the WHERE statement you havent defined a table name for 'AuthorID'. maybe that would fix the problem? Same goes for the entries after SELECT. Always add the table name when doing a JOIN.

    if the table names are long just do this "FROM gallery_customers AS g, photographers AS p" so the select could then be "SELECT g.ID, p.AuthorID, " etc

  15. #15
    Non-Member Gator99's Avatar
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    $RPP=30; //30 results per page
    $sql="some statement without limit";
    $result=mysql_query($sql);
    $res_num=mysql_num_rows($result);
    mysql_free_result($result);
    $pages=ceil($res_num/$RPP);
    if($p>$pages || !$p)$p=1;//$p is the page number passed to the script
    $offset=($p-1)*$NR;
    $sql.=" Limit $offset,$RPP";
    $result=mysql_query($sql);

  16. #16
    SitePoint Enthusiast thenetmaker's Avatar
    Join Date
    Sep 2004
    Location
    N.Ireland
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It lives!! It lives!!! Woohoo!!
    After 4 weeks of mucking about it works - it's beautiful!
    Well it works anyway... beautiful will come later when I play with the formatting.

    Thanks to all who submitted ideas, hints, tips, code and answers!
    Once again sitepoint.com forum users solves another of my PHP newbie problems.
    Cheers All!

    Mike
    See the working pagination:
    http://www.bigbluesquid.com/gallery_...ers.php?page=1


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
  •