Help with ORDER BY

shawjames

I have a select query, that currently orders by name... the table has name and date fields, and I want to order by date that is within the last month (but no more then 4), and then by name.

So currently, the select orders by name.

I want to order so that it shows the newest 4 by date first (but not past a month old), then the rest sorted by name.

Any ideas?

maybe if I cannot do this with one query, can I do the newest 4 by date, then by name... without the (not older then 1 month) requirement?

Thanks!

paulnaj

Hi,

Assuming your table has a primary key (called id say) then ...

<?php
$sql1 = "
SELECT *
FROM table
ORDER BY date DESC
LIMIT 0, 4
";
?>

You could produce an array of id's (called $ids, say) whilst looping through these results. Then ...

<?php
$sql2 = "
SELECT *
FROM table
WHERE id NOT IN(".join(',', $ids).")
ORDER BY name ASC
";
?>

... should do it!

Paul 🙂

shawjames

Okay, I got that to work. Not I just need to join the arrays... but I cannot seem to do it correctly.

So I have the first one $result1 and the second one $result2 and I want to join them into one array $result[result]

I tried this using
$result[result] = array_merge($result1, $result2);

But I get an empty result. Can I do it like this if the arrays are of SQL values?

liquorvicar

what do you mean by empty result? what's the output of print_r($result);?

also, did you mean

$result[result] = array_merge($result1, $result2);

when just

$result = array_merge($result1, $result2);

would do.

have you checked that $result1 and $result2 are both as expected before the merge?
you could do it all in the one array like this:

$sql = //first query;
$query = mysql_query($sql,$c);
while ($result=myql_fetch_assoc($query)) {
  $data[] = $result;
} 
$sql = //second query;
$query = mysql_query($sql,$c);
while ($result=myql_fetch_assoc($query)) {
  $data[] = $result;
}

you will end up with an array, $data, containing all your results in the correct order.