SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Limits in MySQL

  1. #1
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey guys,

    I've got a question...

    I'm creating a memberlist, and of course, I only want to show so many users per page. Also, I want to create first page, previous page, next page, and last page buttons.

    Now, when I use something like this:

    Code:
    mysql_query("SELECT members.ID AS ID, LN, FN, members.City AS MCity, HE, WE, WP, HP, members.WID, work.ID, work.Name AS WName FROM users, members, work WHERE members.WID=work.ID");
    It works, but when I use:

    Code:
    if(!IsSet($num))$num=0;
    $num=$num*20;
    
    mysql_query("SELECT members.ID AS ID, LN, FN, members.City AS MCity, HE, WE, WP, HP, members.WID, work.ID, work.Name AS WName FROM users, members, work WHERE members.WID=work.ID LIMIT $num, ($num+20)");
    It doesn't work because I think there's "not enough for the limit" because I only have 1 in the database. How can I fix this?

    As for the links, how would I do it; here's what I'm trying (can't try now since can't get first part to work): ($result is where I store the sql qith mysql_fetch_array())

    Code:
    <?php
    $rows = mysql_num_rows($result);
    
    if($num!=0){
    echo("<a href='$PHP_SELF?num=0'>&lt;&lt; First Page </a>");
    }
    if($num!=0 && $num!=1){
    echo("<a href='$PHP_SELF?num=($num-1)'>&lt; Previous Page </a>");
    }
    echo("$num of $rows returned pages");
    if($num<$rows){
    echo("<a href='$PHP_SELF?num=($num+1)'>&gt; Next Page </a>");
    }
    if(($num-1)<$rows){
    echo("<a href='$PHP_SELF?num=$rows'>&gt;&gt; Last Page </a>");
    }
    ?>
    aDog
    Last edited by Arielladog; Apr 3, 2001 at 16:14.

  2. #2
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not sure what all those initials are in your SQL SELECT statement are for. One problem though is your WHERE statement
    Code:
    WHERE members.WID=work.ID
    Joins can only be performed on identical fields.
    Code:
    tblMembers.memberID = tblWorks.memberID
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  3. #3
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey westmich and thanx for the reply,

    I don't think the initials really matter for my question, though, I can explain some of them.

    MA- Mailing/Street Address
    HE - Home Email
    WE - Work Email
    HP -Home Phone
    WP - WOrk Phone

    and the corresponding stuff in work tables is their work address, etc...

    As for what you said, I'm going to have to respectfully disagree here You can look at Kevin's article here,
    http://www.webmasterbase.com/article.php/228/524 , and see how he does not use the same key field name. And, I can run that query (filling in the PHP variables) and taking out he LIMITS in MySQL, and it works.

    My problem is that if I say return 20 results with the LIMITS thing, it seems I must have 20 results there or else it's not going to work. What I want is to display as many as possible, up to 20 on one page and continue on the next page.

    aDog

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your approach is abit off I think. Normally what I do is run an initial query to get the total number of pages we will have then you can use that total number to make each page link. Have a look at this thread:

    http://www.sitepointforums.com/showt...4141#post97025

    and this one:

    http://www.sitepointforums.com/showt...threadid=19364




    Also LIMIT is used like this

    LIMIT offset, number

    So when you run $num = $num*20;

    You get a minimum of 20 no matter what so if your query has less than 20 records you are trying to call like LIMIT 20, 20 So it tries to start at row 20 when there aren't 20 rows to begin with. Hope that helps
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey freddy,

    Thanx a lot...here's what I finally came up with:

    LIMIT $num, 20

    And that works

    As for the next and previous links, I'll look at that tomorrow

    Thanx,
    aDog

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's the right idea. You go a set amount from the current offset. In this case 20.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •