Special text sort of array?

[Strahan]

Hi.  I have a web app to track shows I watch.  I have it pulling data from a provider who gives API access to their database.  The character database has a field to determine a character's status in the show.  These are the values it sends:

 

main character in

secondary cast in

appears in

cameo appearance in

 

I have them in my local database using the same value.  I just went to view characters for a show and "cameo" is at the top of the list.  That is annoying, as cameo characters are of low relevance.  I'd like to sort the SQL result in the order I listed them above.  I'm doing:

$sql = $pdo->prepare("SELECT csname,csgender,csimage,csdesc,cstype,(SELECT GROUP_CONCAT(CONCAT(s.syid, CONCAT(':', seiyuu))) FROM anidb_seiyuu_cast sc INNER JOIN anidb_seiyuu s ON sc.syid = s.syid WHERE ca.csid = sc.csid) AS seiyuu FROM anidb_cast_aid ca INNER JOIN anidb_cast c ON ca.csid = c.csid WHERE ca.aid = ?");
$sql->execute(array($aid)); 
$rows = $sql->fetchAll();

Which gives me the $rows array with all the data.  I thought about looking up some way to sort it in SQL but I have no idea how to do that.  On the PHP side I figured I could sort $rows, but the only way I can come up with is very inefficient. 

 

What would be the best way to accomplish this?  

[mac_gyver]

you can either use FIELD() or FIND_IN_SET() in an ORDER BY term in the query to do this.

[Strahan]

I'm reading the docs for field, I'm a little lost.  I don't want to pull back just one type, I want all the records I just want them to sort by cstype in the order listed.  Those SQL commands do that kind of thing?  I guess I need to keep reading heh.

[mac_gyver]

the examples in the documentation, SELECTing the value returned by the FIELD() statement are only to demonstrate what value is returned for each example. imagine calculating that value for each row of data in the result set and ordering the data by that value - 

ORDER BY FIELD(cstype,'main character in', 'secondary cast in', 'appears in', 'cameo appearance in')

[Strahan]

Thanks a lot, that's just what I needed