There are 6 possible keys in a MySQL field. Lets call them types. Through PHP, I have defined an array, that is called $order, and arranges these types in order I want them to appear.
There is a table, articles, which has a field articles.type . Any article can have 0-6 types added to it. Now, what I want to do, is grab all of the articles, and order them from the prototype. What is the best way to do this? Can this be done in MySQL, since I suppose that would be faster? And if not, how can it be done in PHP?
Example:
Table:
id articleId type
1 3 type1
2 3 type2
3 3 type3
4 3 type4
5 4 type5
6 4 type6
7 5 type5
8 7 type1
9 7 type5
Order:
$order=array('type1','type2','type3','type4','type5','type6');
How do I fetch the results ordered by my $order variable?
You'd need to massage that array into a mysql-style if/case statement:
$order_by = "ORDER BY CASE";
$pos = 1;
foreach ($order as $clause) {
$order_by .= " CASE `type`='$clause' THEN " . $pos++;
}
$order_by .= " ELSE " . $pos++;
which would generate something like
ORDER BY CASE
WHEN `type`='type1' THEN 1
WHEN 'type`='type2' THEN 2
...
ELSE n
Can this be done in MySQL, since I suppose that would be faster?
Only if you allow MySQL to use an index
You can create a temp table:
$query = "CREATE TABLE IF NOT EXISTS test (
sort INT NOT NULL AUTO_INCREMENT,
`type` VARCHAR(20) NOT NULL,
PRIMARY KEY (sort),
KEY (`type`, sort)
ENGINE=MEMORY (SELECT 1,'other' <<-- see query below.
UNION SELECT 2,'type1' <<-- build this part using
UNION SELECT 3,'type2' <<-- Marc B's code.
UNION SELECT 4,'type3'
UNION SELECT 5,'type4'
UNION SELECT 6,'type5'
UNION SELECT 7,'type6' ";
Run this query.
Now you can link against this query using a join and use test.sort as your sortkey:
SELECT t1.id, t1.article_id, COALESCE(t.`type`,'other') as sort_type
FROM table1 t1
LEFT JOIN test t ON (t1.`type` = t.`type`)
WHERE ....
ORDER BY t.sort;
This query will be fully indexed and run as fast as possible.