Jump to content

Special text sort of array?


Go to solution Solved by mac_gyver,

Recommended Posts

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?  

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • Solution

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')
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.