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

    Need help ordering data from mysql database.

    First, I will explain what my code is designed to do.

    This part selects all of the contacts (contact_id) on user 1's (my) friend list. It turns them into a single variable, $contacts.

    Code:
    $query = $DB->query( "SELECT contact_id FROM ibf_contacts WHERE member_id='1'");
    
    
    while ($row = mysql_fetch_assoc($query)) { 
    $contacts = $row['contact_id'];
    This next part displays all "posts" where the blog IDs (id_blog) are identical to my contacts IDs. In other words, it displays all blog entries of the people on my friends list. It orders them by "id", which is the id of the blog entry. (Chronological order)

    Code:
    $query2 = $DB->query( "SELECT post FROM mkp_blog_post WHERE id_blog='$contacts' ORDER BY 'id'");
    
    while ($row = mysql_fetch_assoc($query2)) { 
        echo $row["post"]; 
    }
    The problem is that it is not ordering them correctly. For example:

    Entry 1, Written By Person A
    "Hello, How are you!"

    Entry 2, Written By Person B
    "I am doing fine."

    Entry 3, Written By Person A
    "What are you doing today?"

    Entry 4, Written By Person B
    "Not much."
    The above is the order it should display in. However, this is how it is actually being displayed:

    Entry 1, Written By Person A
    "Hello, How are you!"

    Entry 3, Written By Person A
    "What are you doing today?"

    Entry 2, Written By Person B
    "I am doing fine."

    Entry 4, Written By Person B
    "Not much."
    So what it's doing, is ordering them first based on user ID, and THEN based on the ID of the entry. (Which is just another way of saying the chronological order). Also, I did not show this above, because I didn't want to make things confusing, but it is also ordering them from oldest to newest, and not newest to oldest. I'm sure there is a fix for this? Thanks!
    Last edited by Punk Rock Geek; 11-05-2006 at 08:23 PM.

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    ORDER BY 'id'
    means that your rows will be ordered by the string 'id', which is pretty meaningless.
    try it without the quotes.

  • #3
    New Coder
    Join Date
    Jun 2006
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I changed it, but there was no effect.

  • #4
    New Coder
    Join Date
    Oct 2006
    Posts
    82
    Thanks
    3
    Thanked 0 Times in 0 Posts
    You only need quotes in the query for ORDER BY if you are referring to a variable for instances '$id' in which case you can remove the quotes and refer to it simply as id. However, you've said this hasn't worked, so I would suggest you check your database to make absolutely certain that you have a field called 'id' in ibf_contacts and mkp_blog_post tables and that you have the letters in the right case. If not then there's your problem.

  • #5
    New Coder
    Join Date
    Jun 2006
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    You guys were right. It was just my code that was faulty. This is my new code, which combines everything into a single query:

    Code:
    $query = $DB->query( "SELECT mkp_blog_post.post FROM mkp_blog_post, ibf_contacts WHERE mkp_blog_post.id_blog=ibf_contacts.contact_id AND ibf_contacts.member_id='$idu' ORDER BY mkp_blog_post.id DESC");
    
    while ($row = mysql_fetch_assoc($query)) { 
    
        echo $row["post"]; 
    
    }
    Thanks.

    I just have one more small problem. How do I seperate each blog "post" by a vertical space? (<p>)
    Last edited by Punk Rock Geek; 11-06-2006 at 01:15 AM.

  • #6
    New Coder
    Join Date
    Oct 2006
    Posts
    82
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Ok, well if you assume that everything that goes on inside the While statement is looped, all you have to do is add a line break after the echo, and then every echoed blog post will be followed by a line break. So you could use...

    PHP Code:

    <?php

    while ($row mysql_fetch_assoc($query)) { 

        echo 
    $row["post"]."<br/>"

    }

    ?>
    Alternatively you could jazz things up a little bit and use a table...

    PHP Code:

    <table border="1">

    <?php


    while ($row mysql_fetch_assoc($query)) { 

    ?>

       <tr><td><?php echo $row["post"]."<br/>"?> </td></tr>


    <?php

    }

    ?>

    </table>
    There is a way to use /n inside php code as a line break instead of using html but the above should be fine.


  •  

    Posting Permissions

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