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 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    PHP MySQL Order By problem

    I have been working on building a blog by hand to gain more experience with PHP. I have completed most of the main webpage so far, but I am experiencing a problem with the Order By statement in the comments section.

    Here is the code that I believe is causing the problem:

    PHP Code:
    <ul>
         <?php
    //This will be a stacked query that will first grab the current blogid, then grab the username from the user table and use that username to replace the userid in the comment table, and finally post both the user name and the user's comment (5 most recent)
    //Start by grabbing the most recent blog
    $query1 "SELECT * FROM CurrentBlog ORDER BY BlogDate DESC LIMIT 1";
    $data1 mysqli_query($dbc$query1)
            or die(
    "MySQL error: " mysqli_error($dbc) . "<hr>\nQuery: $query1");
    while (
    $row1 mysqli_fetch_array($data1)) {
            
    $blogid $row1['BlogId'];
            
    //Now make a query to grab the userid and the username from the Users table
            
    $query2 "SELECT * FROM Users";
            
    $data2 mysqli_query($dbc$query2)
                or die(
    "MySQL error: " mysqli_error($dbc) . "<hr>\nQuery: $query2");
            while (
    $row2 mysqli_fetch_array($data2)) {
                
    $userid $row2['UserId'];
                
    $username $row2['UserName'];
                
    //Now make a query that grabs the 5 most recent comments, sets the username to the associated user id, and sorts the comments by date desc
                
    $query3 "SELECT * FROM Comments WHERE BlogID='$blogid' ORDER BY CommentDate DESC LIMIT 5";
                
    $data3 mysqli_query($dbc$query3)
                    or die(
    "MySQL error: " mysqli_error($dbc) . "<hr>\nQuery: $query3");
                while (
    $row3 mysqli_fetch_array($data3)) {
                    
    $commentuserid $row3['UserID'];
                    
    $commenttext $row3['CommentText'];
                    if (
    $userid == $row3['UserID']) {
                    
    $commentuserid $username;
                        echo 
    '<li>';
                            echo 
    '<h3>';
                                echo 
    $commentuserid;
                            echo 
    '</h3>';
                            echo 
    '<p>';
                                echo 
    $commenttext;
                            echo 
    '</p>';
                            echo 
    '<p>';
                                
    $Ctimestamp $row3['CommentDate'];
                                
    $Cdate date("d-m-Y"$Ctimestamp);
                                echo 
    $Cdate;
                            echo 
    '</p>';
                            echo 
    '</li>';
                    }
                }
            }
    }
     
         
    ?>
         </ul>
    And here are my results:
    User 1
    User 1's comment
    12-10-2011
    User 1
    User 1's second comment
    12-09-2011
    User 2
    User 2's comment
    12-10-2011
    User 3
    This might appear as it it user 3's second comment
    14-09-2011
    User 3
    User 3's comment
    12-09-2011

    Any help would be appreciated.
    Last edited by Spookster; 09-12-2011 at 10:47 PM. Reason: Added PHP code tags

  • #2
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    You've not really gone about this correctly. Did you plan your logic out before attempting the task? Currently what you're doing, is looking through the first 5 comments for EVERY user in your database, meaning if you have 20 users, you're looping through the same 5 comments 100 times. Obviously, very inefficient.

    I do see what you were trying to do though - you're trying to populate a list of userid's and usernames so that you can fill that information into your comments. Firstly, you could use a mysql join OR simply run the user query and while loop on its own, and populate an array like - $users['userid'] = 'username'. That way, when you get your 5 comments, all you need to do is:
    PHP Code:
    // Loop to populate users
    $users = array();
    while (
    $row2 mysqli_fetch_array($data2)) {
        
    $users[$row2['UserID']] = $row2['UserName'];
    }

    // Loop to get comments
    while ($row3 mysqli_fetch_array($data3)) {
        echo 
    '<li>';
        echo 
    '<h3>';
        echo 
    $users[$row3['UserID']]; // Echo's username for this comment
        
    echo '</h3>';
        echo 
    '<p>';
        echo 
    $row3['CommentText'];
        echo 
    '</p>';
        echo 
    '<p>';
        
    $Cdate date("d-m-Y"$row3['CommentDate']);
        echo 
    $Cdate;
        echo 
    '</p>';
        echo 
    '</li>';

    Of course, that's not the most efficient way of doing things. The most efficient way would be to use a mysql join to select everything you need from the Comments table, collecting the user ids and then using that in a SELECT query WHERE userid=id OR userid=id2 etc. etc. to get a list of users that only commented in that blog . That's something for you to play with

  • #3
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the quick reply BluePanther. I have limited experience with PHP, so I was just applying what I already knew. I had not yet learned to use an array like you showed me, so your code helped me out alot.

    Do you have any suggestions on books I can read to gain additional PHP knowledge?

  • #4
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    I never really used any books - I just used online resources and the PHP Manual to build on my previous programming experience in different languages. There's a good set of learning resources in a sticky at the top of this forum though, I'm sure there's plenty of gems in there .


  •  

    Posting Permissions

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