SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    order mysql results by date_added and time_added?

    Ok, for my CMS. I noticed a problem:

    http://cms.jabird.com

    This post:


    fsasdfds
    gfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgfgf
    was added about a minute after:

    sdfasd
    asdfasdfsdf
    yet the newest one doesn't show up above the older one... I am ordering by a column named date_added. and I have another named time_added. So is there someway to order by both. that way, all the 7/29/05's are in the same group. but the newest comes first. how can this be done?

    I know alot of forums (if not all) are ordered by newest. Thats what I want.

    EDIT:

    I just tried ordering by id DESC... and got this error:

    Error performing query: Column 'id' in order clause is ambiguous


    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/local/apache2/htdocs/cms/index.php on line 102

    whats that mean?
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  2. #2
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you post the full query you are using?
    This was the closest I got to something appropriate from searching MySQL. Could it be because of an older version?

  3. #3
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My query:
    $content = @mysql_query('SELECT * FROM news, users ORDER BY date_added DESC');

    and no, its a pretty new version
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  4. #4
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hm... There doesn't seem to be a WHERE clause to match the two tables or any JOINs. This would result in a cartesian product (if I remember my lessons correctly.. ).

  5. #5
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    eh.... explain please? You lost me...

    ordering the way my query that I posted above works... except it doesn't put the VERY newest at the top.
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  6. #6
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT * FROM news, users ORDER BY date_added DESC

    You have two tables used in your query. That is <news> and <users>. How are these related?
    If you don't specify how they are related, (from what I recall) the DB would return each row of <news> having each row of <users>. Say <news> has 10 rows and <users> has 20 rows. So theoretically all you need is 10 rows of <news> with the apprpriate <user>. But since the relation is missing, you would get 10 x 200 => 200 rows instead.
    Additionally, you mentioned the use of <id>. I assume you removed it. Could you post your table structures as well?
    Basically, you'd need to add somthing like
    WHERE user.id = news.user_id
    or some such where it would match the user to the poster of the news (I assume this is what you want it to do).
    I suspect that this lack of a WHERE clause also might be the reason for the ambiguity.

  7. #7
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, I got it ordering by ID in the news table:

    PHP Code:
     <?php
             $content 
    = @mysql_query('SELECT * FROM news ORDER BY id DESC');
             if(!
    $content) {
             echo(
    '<p>Error performing query: ' mysql_error() .
                 
    '</p>');
             }
             while(
    $row mysql_fetch_array($content)) {
             
    // Find out the authors id.
             
    $authorid $row['authorid'];
             
    // Get his name!
             
    $authordetails = @mysql_query(
                 
    "SELECT * FROM users WHERE id='$authorid'");
             
    $author mysql_fetch_array($authordetails);
             
    $name $author['username'];
             
    // Set up the "pretties"
             
    echo '<div class="newscontent">';
             
    // Echo the news.
             
    echo '<span class="posted">&nbsp;&nbsp;Posted by: '.$name.' on: '.$row['date_added'].' at: '.$row['time_added'].'</span><br /><span>&nbsp;'.$row['newstitle'].'</span><br /><span>&nbsp;'.$row['newstext'].'</span><br /><br />';
             
    // Echo the Edited button (if it was edited)
             
    if($row['edited'] == '0000-00-00') {
             echo(
    '&nbsp;');
             } else {
             echo(
    '<span class="edited">&nbsp;&nbsp;This post has been edited '.$row['times_edited'].' time(s). It was last edited on: '.$row['edited'].' at: '.$row['time_edited'].'</span><br />');
             }
             
    // Echo the Edit, Delete, and Move Links...
             
    if($_SESSION['user_level'] == 2) {
             echo 
    '<span class="adminoptions">&nbsp;&nbsp;<a href="'.$domain.''.$path.'admin/edit.php?id='.$row['id'].'">Edit</a> | <a href="'.$domain.''.$path.'admin/delete.php?id='.$row['id'].'">Delete</a> | <a href="'.$domain.''.$path.'admin/move.php?id='.$row['id'].'&amp;pos='.$row['position'].'&amp;dir=up">Move Up</a> | <a href="'.$domain.''.$path.'admin/move.php?id='.$row['id'].'&amp;pos='.$row['position'].'&amp;dir=dwn">Move Down</a></span>'
             }
             
    // End the "pretties"
             
    echo '</div>&nbsp;';
             }
             if(
    $_SESSION['user_level'] == 2) {
                 echo 
    '<div class="newscontent"><a href="'.$domain.''.$path.'admin/add.php">Add News</a></div>&nbsp;';
             }
             
    ?>
    Does that all look good to you?
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  8. #8
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hm, I guess you could shorten it to a single query.
    Code:
    SELECT * FROM news, users WHERE user.id = news.authorid ORDER BY news.id DESC
    And I think I just realised why the ambiguity scenario appeared. It is because you didn't say *which* table's id you are supposed to order by.

    Did the code you posted run successfully and do what you want it to do?

  9. #9
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code I posted did do what I wanted to do:

    http://cms.jabird.com

    it is ordered by the id of the news table now. and it works great.
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  10. #10
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I took a look at the site. There is only 1 news post in there. If you want to really test if it works you should have multiple news posts with multiple users.
    Or am I missing something here?

  11. #11
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yea I deleted all my test posts... sorry bout that... I saw your post... how'd you get the password? or did I do my sessions wrong?
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  12. #12
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I clicked on the Login link. And just submitted the empty form. Voila~
    Think somethings up with your verification process. Better go check on that

    Edit: By the way, though your script works now, it does a lot of queries. Since after you executed the query to get the news, you have to execute the query to get user as many times as there are news items. So it is a far better to combine those queries together.

  13. #13
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah... I'll check it out... also how'd you make your referer:

    XXXX:+++++++++++++++++++++++++++++++++++++++++++++++++++

    ?
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  14. #14
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have no idea how the referer ended up looking like that. I simply clicked on the link in your post. Though I opened it in a new tab (using FireFox). So unless that's the cause, I have no clue.

  15. #15
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ic...

    alright, I edited my login script... and I'm not so great with if elseif else statements... so here's my script:

    PHP Code:
     session_start();
     include(
    '../includes.php');
     if(empty(
    $_POST['username']) {
     die(
    "You forgot to type your username");
     } elseif(empty(
    $_POST['password']) {
     die(
    "You forgot to type your password");
     } elseif(isset(
    $_POST['submit'])) {
     
    $username trim($_POST['username']);
     
    $password trim($_POST['password']);
     
    $securepass md5($password);
     }
     
    $sql = @mysql_query("SELECT * FROM users WHERE username = '$username' AND password = '$securepass' LIMIT 0,1");
     
    $result = ($sql);
     if ( !
    $result ) {
     die(
    'Database Error - Query');
     }
     
    $row mysql_fetch_array($sql);
     if(
    $row['user_level'] = 2) {
     
    $_SESSION['user_level'] = 2;
     
    $_SESSION['usr'] = ''.$username.'';
     
    $_SESSION['userid'] = ''.$row['id'].'';
     
    $loc "".$domain."".$path."";
     
    header("location:$loc");
     exit;
     } 
    it gives me:

    Parse error: parse error, unexpected '{' in /usr/local/apache2/htdocs/cms/login/process_login.php on line 4
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  16. #16
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
     if(empty($_POST['username'])) { 
    Missing ).
    Anyhow, I am not sure if empty works properly. I didn't get any messages. So perhaps you should do a == '' comparison?

    Edit:
    PHP Code:
     } elseif(empty($_POST['password'])) { 
    Another missing ). :P

  17. #17
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    alright, that fixed it =\ I tried your technique, and... it didn't work :P

    Thanks for all your help
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  18. #18
    *munch munch* killerkooki's Avatar
    Join Date
    Sep 2004
    Location
    Singapore
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, just checked it and it works for me too. So, Yay!
    Good Luck with the site


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
  •