Can't order the posts correctly! Anyone can help?

web_designer_iq

Hello
I have 2 field in my posts table and i want to order the posts with "order_id" first then with "sent_date".
I'm using the following query but it will be order the "order_id" first (for ever)! until with a NULL field or with zero field !!

$sql_query = "
SELECT
	posts.post_id,

IF(posts.user_id != -1, users.full_name, posts.poster_name) AS name,
DATE_FORMAT(posts.post_time $get_time_dif_h INTERVAL $get_time_dif HOUR, '$date_format') AS post_time,
posts.title,
cats.title AS cat_title,
posts.head,

IF(posts.img != NULL && posts.img != '', NULL, posts.img ) AS img,
posts.img_width_thumb,
posts.img_height_thumb,
posts.sum_read,
posts.how_see,
users.user_id

FROM

portal_posts AS posts NATURAL LEFT JOIN portal_users AS users
INNER JOIN portal_categories AS cats ON cats.cat_id = posts.cat_id

WHERE

posts.visibility = 1 && posts.is_accept = 1
&& (posts.lang = 'all' || posts.lang = '$default_lang')

ORDER BY posts.order_id, posts.post_time DESC LIMIT $index_total_posts";

Anyone can help me how can i order the posts with order_id first then with sent_date?
NOTE: The type of the order_id field is a small_int.

sneakyimp

web.designer.iq;10907692 wrote:
Hello
I have 2 field in my posts table and i want to order the posts with "order_id" first then with "sent_date".
I'm using the following query but it will be order the "order_id" first (for ever)! until with a NULL field or with zero field !!

Not sure what the problem is exactly. Could you clarify ?

web_designer_iq

The problem in the:

ORDER BY posts.order_id, posts.post_time DESC

It can't order the posts correctly!
For example if the "order_id" contains "0" or "NULL" it will be order at the top.
My question is:
How can i order my posts with "order_id" first then with "sent_date" just with integer number in order_id?

sneakyimp

Well, SQL is just doing what you asked of it. It just so happens that nulls and zeros come first when sorting something in order. So the order is 'correct'. If you don't want to see zero or null posts, you need to alter your query to exclude them.

WHERE

posts.visibility = 1 && posts.is_accept = 1
&& (posts.lang = 'all' || posts.lang = '$default_lang')
&& posts.order_id != 0
&& posts.order_id IS NOT NULL
&& posts.post_time != 0
&& posts.post_time IS NOT NULL

Sxooter

web.designer.iq;10907723 wrote:
The problem in the:
ORDER BY posts.order_id, posts.post_time DESC
It can't order the posts correctly!
For example if the "order_id" contains "0" or "NULL" it will be order at the top.
My question is:
How can i order my posts with "order_id" first then with "sent_date" just with integer number in order_id?

You do know that that's implicitly ordering like this:

order by posts.order_id ASC, posts.post_time DESC

right?

Not sure if that's the problem or not. Still not sure what the problem is.

Sxooter

Could you make a simple test case displaying the behaviour, all in SQL? Like

create table ...
create table ...
insert into table ...
insert into table ...
insert into table ...
select * from joinedtables order by a,b DESC;