Forum Moderators: coopster
SELECT FROM table WHERE row = 'blah' ORDER BY id DESC AND ORDER BY name
?
Thanks.
This is my query:
$query = "SELECT * FROM covers ORDER BY id DESC, scanname ASC LIMIT 10";
$result = mysql_query($query) or die(mysql_error());
Would the limit clause affect anything?
ORDER BY id DESC, name ASC
This code will sort by ID, and if there are records with identical ID's, it will sort them by name. I don't think that's what you want. You're looking to get the highest 10 ID's, and then sort them by name, right?
I think what you may need is a subquery. This page may help:
[dev.mysql.com...]
The following SQL statement may work in MySQL 4.1 and newer (unfortunately I'm running 4.0 here so can't really check.)
SELECT * FROM covers
WHERE ID IN (select ID from covers order by id DESC limit 10)
ORDER BY scanname ASC
You're looking to get the highest 10 ID's, and then sort them by name, right?
Yep, thats exactly what I`m after timster. I tried your code but it gave me a syntax error. I looked at the link you gave me and did a search on google, but I`m not really understanding the sub query thing.
Will you have multiple id's for name?
- a SELECT INSERT of the first ten IDs into a temporary table and then another select ordered by name.
- or use array_multisort()
function get_name_list($col1='blah'){
$query = "SELECT id, name FROM table WHERE col1='$col1' ORDER BY id DESC LIMIT 10";
$result = mysql_query($query);
$i=0;
while ($row = mysql_fetch_assoc($result) ) {
$row_array[$i] = $row;
$name_array[$i] = $row['name']
$i++
}
array_multisort($name_array, $row_array);
return $row_array;
}
Or something along those lines
With the first method you need to create a temp table, select into it, get everything out of it, and then delete the table.
If not, you may be able to get by with...
SELECT
DISTINCT id,
scanname,
the_rest_of_your_columns
FROM covers
ORDER BY id DESC, scanname ASC LIMIT 10
;
One other note, if
scannameis of type
ENUM,
SET, etc. your
ORDER BYwon't work as you are expecting...
Won't that still give you a result set sorted first by id and then by name?
If there are no multiple ids (i.e. id is unique), won't select id and select distinct yield the same result set?
Sorry for the confusion. I did test it, though ;)
All-in-all, it depends on the dataset being used and the table structure. I am still uncertain as to the structure...
If you're interested in having MySQL do all the heavy lifting instead of PHP, something like this might be good. (For a change, these queries do work in MySQL 4.0.)
1. select id from tabe_name order by id DESC limit 10;
2. fetch the rows and feed the ID's into a string delimited by commas
3. select * from tabe_name where id in ($string) order by name
Yeah, the small record set is why I thought he might want to just do it with PHP. As you point out, the 'brute force' method isn't all that brute. It adds only four lines of php and saves at least one query. And the lines it adds, namely:
$i=0;
$name_array[$i] = $row['name']
$i++array_multisort($name_array, $row_array);
are just two assignments, an increment and a built-in function call. I would think it would benchmark at least as fast as any mysql-oriented solution.
If you want mysql to do it all, though, the temp table method should work pretty well, perhaps as fast as what Timster suggested. By default the table will only be created in memory, so it should be quite fast.
id in ('1', '2', '7')
would match if id=7, but not if id=6
so
$ids = "";
while ($row = mysql_fetch_array($q)) {
$ids .= "'" . $row['id'] . "',";
}
substr($ids, 0, -1); // to strip off the last comma
timster, can you just elaborate on "fetch the rows and feed the ID's into a string delimited by commas"
That work would be done in PHP. Sorry I don't have time to write real code, but it would look a little like this:
# Caution Pseudo Code -- syntax probably complete junk
$query1 = "select id from table order by id desc limit 10";
$result = mysql_query($query1);
$string = '';
while ($row = $fetchrow_arrayref($result)) { $string .= $row . "," }
$string = preg_replace("/\,$/","",$string);
$query2 = "select * from table where id in ($string);
Be aware there are probably better ways to write this PHP, as I'm still on the PHP learning curve, to say the least.
