order mysql results by date_added and time_added?

[jabird]

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?

[killerkooki]

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?

[jabird]

My query:
$content = @mysql_query('SELECT * FROM news, users ORDER BY date_added DESC');

and no, its a pretty new version

[killerkooki]

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.. ).

[jabird]

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.

[killerkooki]

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.

[jabird]

Alright, I got it ordering by ID in the news table:

 <?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?

[killerkooki]

Hm, I guess you could shorten it to a single query.

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?

[jabird]

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.

[killerkooki]

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?

[jabird]

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?

[killerkooki]

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.

[jabird]

ah... I'll check it out... also how'd you make your referer:

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

?

[killerkooki]

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.

[jabird]

ic...

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

 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

[killerkooki]

 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:

 } elseif(empty($_POST['password'])) { 

Another missing ). :P

[jabird]

alright, that fixed it =\ I tried your technique, and... it didn't work :P

Thanks for all your help

[killerkooki]

Well, just checked it and it works for me too. So, Yay!
Good Luck with the site