SitePoint Sponsor |
|
User Tag List
Results 1 to 18 of 18
-
Jul 29, 2005, 07:51 #1
- 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
sdfasd
asdfasdfsdf
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?
-
Jul 29, 2005, 08:27 #2
- 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?
-
Jul 29, 2005, 08:32 #3
- 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
-
Jul 29, 2005, 08:52 #4
- 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..
).
-
Jul 29, 2005, 08:56 #5
- 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.
-
Jul 29, 2005, 09:20 #6
- 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.
-
Jul 29, 2005, 09:23 #7
- 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"> Posted by: '.$name.' on: '.$row['date_added'].' at: '.$row['time_added'].'</span><br /><span> '.$row['newstitle'].'</span><br /><span> '.$row['newstext'].'</span><br /><br />';
// Echo the Edited button (if it was edited)
if($row['edited'] == '0000-00-00') {
echo(' ');
} else {
echo('<span class="edited"> 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"> <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'].'&pos='.$row['position'].'&dir=up">Move Up</a> | <a href="'.$domain.''.$path.'admin/move.php?id='.$row['id'].'&pos='.$row['position'].'&dir=dwn">Move Down</a></span>';
}
// End the "pretties"
echo '</div> ';
}
if($_SESSION['user_level'] == 2) {
echo '<div class="newscontent"><a href="'.$domain.''.$path.'admin/add.php">Add News</a></div> ';
}
?>
-
Jul 29, 2005, 09:29 #8
- 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
Did the code you posted run successfully and do what you want it to do?
-
Jul 29, 2005, 09:44 #9
- 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.
-
Jul 29, 2005, 09:49 #10
- 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?
-
Jul 29, 2005, 09:55 #11
- 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?
-
Jul 29, 2005, 09:57 #12
- 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.
-
Jul 29, 2005, 09:58 #13
- 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:+++++++++++++++++++++++++++++++++++++++++++++++++++
?
-
Jul 29, 2005, 10:01 #14
- 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.
-
Jul 29, 2005, 10:05 #15
- 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;
}
Parse error: parse error, unexpected '{' in /usr/local/apache2/htdocs/cms/login/process_login.php on line 4
-
Jul 29, 2005, 10:10 #16
- Join Date
- Sep 2004
- Location
- Singapore
- Posts
- 405
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
PHP Code:if(empty($_POST['username'])) {
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'])) {
-
Jul 29, 2005, 10:17 #17
- 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
-
Jul 29, 2005, 10:23 #18
- 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