SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Location
    Phoenix, Arizona
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    for() Loop/MySQL Help Please

    PHP Code:
    <? 
    $rows_per_page
    =6
    $lastpage ceil($numrows/$rows_per_page);//<-- came from a previous working query 

    $f=0
    for(
    $ii=0;$ii<$lastpage;$ii++){ 
        
    $pagei=$ii+1
        if(
    $pagei==1){ 
            
    $limit_left 0
        }else{ 
            
    $limit_left $f+$rows_per_page
        } 
        
    $fnqs "SELECT users.fn FROM users WHERE users.cc='abc' ORDER BY users.fn ASC LIMIT $limit_left,$rows_per_page"
        
    $fnquery mysql_query($fnqs,$conn); 
        while(
    $ln=mysql_fetch_array($fnquery)){ 
            if(
    $f==0){ 
                
    $from_ln "$ln[fn]"
                
    $f++; 
            }elseif(
    $f<=$rows_per_page){ 
                
    $to_ln "$ln[fn]"
                
    $f++; 
            }else{ 
                
    $from_ln "$ln[fn]"
                
    $f=0
            } 
        } 
        echo 
    "<option value=\"$pagei\">$limit_left $from_ln &mdash; $f $to_ln</option>\n"

    ?>
    For some reason the above code results with this:
    HTML Code:
    <option value="1">0 Ace - 6 Bleh</option>
    <option value="2">12 Packer - 0 Olsen</option>
    <option value="3">6 Davis - 6 Loblaw</option>
    What I want to happen is this:
    HTML Code:
    <option value="1">0 Ace - 6 Bleh</option>
    <option value="2">7 Davis - 13 Loblaw</option>
    <option value="3">14 Packer - 20 Olsen</option>
    The point is I would like the list to generate alphabetically. I can't figure out why they start in order but then are out of order. (The numbers in front of the names are for debugging purposes only.)

    I'm sure the answer is right in front of my face, but I'm an idiot.

    Thanks in advance for any help offered!

  2. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    There are a few problems with the way you're going about things - if your user base grows large, then this is going to be a performance drag, as it has to loop through ALL users in the table - and it's doing so over multiple queries!

    You could just select all the users in the first place and then in the loop check if the count has reach the 'per page' limit, then echo the option.

    I think you'd be better off trying to perform this in more simple query, for example getting finding out how many rows there are in the table then performing a query which selects on the xth and x+nth user names.

    Ok, so I created the SQL required to do this in one go, however it turned out a little harder than I had expected. I'm sure there could possibly be a better solution than this, but at least this one works. I've used a per page value of 4, so you will need to replace all 4's with $rows_per_page and all 3's with $rows_per_page-1.
    Code SQL:
    SELECT 
    	u1.name, 
    	u2.name,
    	(
    	SELECT 
    		COUNT(name) 
    	FROM 
    		users 
    	WHERE 
    		name < u1.name 
    	ORDER BY 
    		name
    	) AS POSITION 
    FROM 
    	users u1, 
    	users u2 
    WHERE
    	(u2.name = 
    	(
    		(
    		SELECT 
    			u3.name
    		FROM 
    			users u3
    		HAVING
    			((
    			SELECT 
    				COUNT(name) 
    			FROM 
    				users 
    			WHERE 
    				name >= u3.name 
    				AND name > u1.name
    			ORDER BY 
    				name
    			LIMIT 1
    			) % 4) = 3
    		ORDER BY 
    			name
    		LIMIT 1)
    	) 
    	OR
    	(
    		(SELECT 
    			COUNT(name) 
    		FROM 
    			users 
    		WHERE 
    			name > u1.name
    		ORDER BY 
    			name
    		LIMIT 1
    		) < 4) 
    		AND
    		u2.id = (
    		SELECT id FROM users  ORDER BY name DESC LIMIT 1
    		)
    	) 
    HAVING 
    	(POSITION % 4) = 0 
    ORDER BY 
    	u1.name

  3. #3
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Simplified a little, now you only need to change the perrow value once at the top.
    Code SQL:
    SELECT 
    	4 AS perpage,
    	u1.name, 
    	u2.name,
    	u2.id,
    	(
    	SELECT 
    		COUNT(name) 
    	FROM 
    		users 
    	WHERE 
    		name < u1.name 
    	ORDER BY 
    		name
    	) AS POSITION 
    FROM 
    	users u1, 
    	users u2 
     
    HAVING 
    	(POSITION % perpage) = 0 
    	AND
    	(u2.name = 
    	(
    		(
    		SELECT 
    			u3.name
    		FROM 
    			users u3
    		HAVING
    			((
    			SELECT 
    				COUNT(name) 
    			FROM 
    				users 
    			WHERE 
    				name >= u3.name 
    				AND name > u1.name
    			ORDER BY 
    				name
    			LIMIT 1
    			) % perpage) = (perpage-1)
    		ORDER BY 
    			name
    		LIMIT 1)
    	) 
    	OR
    	(
    		((SELECT COUNT(*) FROM users)-POSITION) < perpage) 
    		AND
    		u2.id = (
    		SELECT id FROM users  ORDER BY name DESC LIMIT 1
    		)
    	) 
     
    ORDER BY 
    	u1.name

  4. #4
    SitePoint Member
    Join Date
    Dec 2009
    Location
    Phoenix, Arizona
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow. My head hurts now.

    I'll give it a shot and let you know. Thanks!

  5. #5
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ericksonstudio View Post
    Wow. My head hurts now.
    Tell me about it...

  6. #6
    Resident Code Monkey Chris Corbyn's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    713
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What's the relationship between $from_ln and $to_ln supposed to be?

  7. #7
    SitePoint Member
    Join Date
    Dec 2009
    Location
    Phoenix, Arizona
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Chris Corbyn View Post
    What's the relationship between $from_ln and $to_ln supposed to be?
    $from_ln = "From this Last Name..."
    $to_ln = "...To this Last Name"
    (in alphabetical order).

    Is that what you meant?

  8. #8
    Resident Code Monkey Chris Corbyn's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    713
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm still a bit confused about the actual relationship (at the DB level) between $to_ln and $from_ln. I assume that if you had a User table that looked like this:

    Code:
    id      username
    --------------------
    1       A
    2       B
    3       C
    4       D
    5       E
    6       F
    7       G
    8       H
    Then you're looking for a set of <option>'s that look like this?

    Code:
    <option>A - B</option>
    <option>C - D</option>
    <option>E - F</option>
    <option>G - H</option>
    In which case there's no distinct relation in the DB between A and B etc, other than the fact they are 1 row apart from each other when sorted alphabetically. (You may want to re-think your schema so you can query it correctly).

    Thus (ignoring anything except the core problem here):

    PHP Code:
    $sql "SELECT name FROM users ORDER BY name ASC";
    $result mysql_query($sql$conn);
    while (
    $firstRow mysql_fetch_assoc($result))
    {
      if (!
    $secondRow mysql_fetch_assoc($result))
      {
        break; 
    // Not enough data to construct a new <option>
      
    }
      
      echo 
    '<option>' $firstRow['name'] . ' - ' $secondRow['name'] . '</option>;


  9. #9
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Chippie, my SQL sorts the whole thing out - I was just wondering if there's a simpler version of my SQL statement

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    ericksonstudio, can you post a "Create Table" for the users table?

    Let's get it straight as to what your trying to do, your trying to display a list of users, but with a certain number of users listed per page, correct?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  11. #11
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

    $perrow 
    4// Change this to the number of names per page you want
    $sql "SELECT 
        
    $perrow AS perpage,
        u1.name as fname, 
        u2.name as lname,
        u2.id,
        (
        SELECT 
            count(name) 
        FROM 
            users 
        WHERE 
            name < u1.name 
        ORDER BY 
            name
        ) AS position 
    FROM 
        users u1, 
        users u2 
     
    HAVING 
        (position % perpage) = 0 
        AND
        (u2.name = 
        (
            (
            SELECT 
                u3.name
            FROM 
                users u3
            HAVING
                ((
                SELECT 
                    count(name) 
                FROM 
                    users 
                WHERE 
                    name >= u3.name 
                    AND name > u1.name
                ORDER BY 
                    name
                LIMIT 1
                ) % perpage) = (perpage-1)
            ORDER BY 
                name
            LIMIT 1)
        ) 
        OR
        (
            ((SELECT count(*) FROM users)-position) < perpage) 
            AND
            u2.id = (
            SELECT id FROM users  ORDER BY name DESC LIMIT 1
            )
        ) 
     
    ORDER BY 
        u1.name"
    ;


    $result mysql_query($sql$conn);
    while (
    $row mysql_fetch_assoc($result))
    {
     
      echo 
    '<option>' $row['fname'] . ' - ' $row['lname'] . '</option>;

    This uses my sql statement, and will also be the most optimised as it performs only a single query - so that when the database is huge with your 1,000,000 users it won't be dogged down with multiple queries.

  12. #12
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Note: You'll definitely want to put in INDEX on the name column

  13. #13
    Resident Code Monkey Chris Corbyn's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    713
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mal Curtis View Post
    Chippie, my SQL sorts the whole thing out - I was just wondering if there's a simpler version of my SQL statement
    Often I consider post-processing a result set to be better than writing twisty SQL There's a slight tradeoff between performance and legibility of the code. In this case, I don't really think the performance hit using code like mine would be all that significant (you just need to apply the limits x 2 to account for the relationship between the rows). I didn't delve too far into your SQL, but given the relatively simple nature of the problem it probably can use a relatively simple solution

    PS: Sub-selects are expensive when used like this (they'll evaluate for each row in the outer query)... ideally you'd incorporate the sub-select into a derived table for optimal performance.

    EDIT : I wasn't intending my small query to be used inside a for loop like the OP's code... I'm not too sure what that loop was there for but the only requirement I was aiming for was to produce the <option> group. Hence, my code is the entire code.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Chris Corbyn View Post
    Often I consider post-processing a result set to be better than writing twisty SQL
    well said
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member
    Join Date
    Dec 2009
    Location
    Phoenix, Arizona
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This thread has totally confused me now.

    I can't post the table because of dependancies on 2 other tables, and it's proprietary. I am very grateful for all your help.


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
  •