Query problem

[RyanReese]

$query1="Select *,DATE_FORMAT(date_posted,'%W,%d %b %Y') as thedate FROM article INNER JOIN categories ON categoryID=catid WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY)AND artchild='0' ORDER BY date_posted DESC LIMIT 10 ";

I use that and then I do

<?php    
if($num > 0) {
 
 
$rows = array();
while($row_articles = mysql_fetch_assoc($blogarticles)) {
	array_unshift($rows,$row_articles);
}

foreach($rows as $row_articles) { ?>

<div class="blog">
<span class="title"><?=$row_articles['title'];?></span>
<span class="author">Posted by:<?=$row_articles['name'];?></span>
<span class="date">The date posted: <?=$row_articles['date_posted'];?> </span>
<div class="post"><?=$row_articles['comments'];?><a href="comments.php?aid=<?=$row_articles['artid'];?>&cid=<?=$row_articles['categoryID'];?>"> Comments(<? //echo $row_articles['artid'];		  
		
		//$thenum=row_articles['artid'];
		$getcomments = "SELECT * FROM article WHERE artchild='".$row_articles['artid']."'";
		if(!$theResult=mysql_query($getcomments)){
		echo mysql_error();
		}else{
		$num_comments=mysql_num_rows($theResult);
		echo $num_comments;
		}
		?>) </a></div>
</div>

<?php } 

}
?>

devwebsites.com/sitedevs/

It is ordering by date and I need to order it by artid (a column, just like date_posted).

[guido2004]

change the ORDER BY in your query into:

ORDER BY artid

[RyanReese]

I tried that but it gave me an error because if you look earlier in the query it does some date...one second.

[RyanReese]

I tried my best to change the query but keep getting an error..

[guido2004]

What error?
Please post the corrected query.
And what is the idea behind this piece of query? :
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY)

[oddz]

$query1="Select *,DATE_FORMAT(date_posted,'%W,%d %b %Y') as thedate FROM article INNER JOIN categories ON categoryID=catid WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY)AND artchild='0' ORDER BY article.artid DESC LIMIT 10 ";

That is assuming the primary key column is named artid.

[guido2004]

That is assuming the primary key column is named artid.

Why would you have to assume that?

[RyanReese]

He is right. That is the primary key...

[RyanReese]

SQL is my absolute worst and I have no idea how to rewrite this, telling me to change a query is like asking a monkey how to use a chain saw, he might figure it out, but the collateral damage is too great.

[guido2004]

What error?
Please post the corrected query.
And what is the idea behind this piece of query? :
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY)

Please answer these questions

He is right. That is the primary key...

That has no importance (only from a performance point of view).

SQL is my absolute worst and I have no idea how to rewrite this, telling me to change a query is like asking a monkey how to use a chain saw, he might figure it out, but the collateral damage is too great.

Now ain't that what SP is for

Btw... oddz's query should work. He did what I told you to do, change the ORDER BY.

[oddz]

Well the column was not represented in the query so I was asking if the name was correct. I assumed it was the primary key because article id. In regards to whether it is a primary key or not I guess it doesn't matter though.

[RyanReese]

Alright maybe I did something wrong..testing again. WTF I TRIED THAT.

Ok, so now how do I reverse them AND display the 10 highest numbers in artid column (starts at 1, auto increments)

Here is how I display them.

 <?php    
if($num > 0) {
 
 
$rows = array();
while($row_articles = mysql_fetch_assoc($blogarticles)) {
	array_unshift($rows,$row_articles);
}
 
foreach($rows as $row_articles) { ?>
 
<div class="blog">
<span class="title"><?=$row_articles['title'];?></span>
<span class="author">Posted by:<?=$row_articles['name'];?></span>
<span class="date">The date posted: <?=$row_articles['date_posted'];?> </span>
<div class="post"><?=$row_articles['comments'];?><a href="comments.php?aid=<?=$row_articles['artid'];?>&cid=<?=$row_articles['categoryID'];?>"> Comments(<? //echo $row_articles['artid'];		  
 
		//$thenum=row_articles['artid'];
		$getcomments = "SELECT * FROM article WHERE artchild='".$row_articles['artid']."'";
		if(!$theResult=mysql_query($getcomments)){
		echo mysql_error();
		}else{
		$num_comments=mysql_num_rows($theResult);
		echo $num_comments;
		}
		?>) </a></div>
</div>
 
<?php } 
 
}
?>

[RyanReese]

Instead of DESC in the query, I made it ASC thus making the query the right direction. Now, how would I display the ten most recent blog entries (highest number in artid).

[oddz]

$query1="Select *,DATE_FORMAT(date_posted,'%W,%d %b %Y') as thedate FROM article INNER JOIN categories ON categoryID=catid WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY)AND artchild='0' ORDER BY article.date_posted DESC,article.artid ASC LIMIT 10 ";

http://dev.mysql.com/doc/refman/5.0/...imization.html

A large part of being a efficient,proactive and independent programmer is learning to use the docs. Its rare to come across a question that can't be solved either directly or indirectly by referring to the docs.

[RyanReese]

THANKS. Got it working.