CODEWITHSUNDEEP
mysqlwordpresssql-order-by
Jonah
Jonah

Reputation: 31

MySQL ORDER BY FIELD Containing a String of Numbers

I'm trying to modify the get_previous_post_sort/get_next_post_sort order for a custom post type post navigation based on the order of these posts stored in a field in the database (that is set through another custom field in another place).

By default get_previous_post_sort/get_next_post_sort order is just going off the date these posts were entered but I need them to order by the custom field in the database.

The field in the database stores the post id's of the posts in the order I need them to be in like so: 1746,1741,1751,1755,1758,1761,1749,1764,1776,1767,1781,1770,1773,1784,1787,1790,1793,1798,1801,1804,1807,1810,1813,1816,1819,1822,1825,1828,1831,1834,1837,1840,1843,1846

I've been able to pull in this string of numbers from the custom field but I'm having trouble with it actually affecting my prev/next links. The query in a debug output looks solid:

SELECT p.*  
FROM wp_posts AS p  
INNER JOIN wp_postmeta AS m ON p.ID = m.post_id  
WHERE p.post_date < '2011-11-09 17:08:32'  
AND p.post_type = 'story-images'  
AND p.post_status = 'publish'  
AND m.meta_key = 'editorial'  
AND m.meta_value = 771  
ORDER BY FIELD(p.ID,1627,1624,1630,1633,1636,1639,1642,1645,1648,1653,1656,1659,1662,1665,1668,1671,1694,1730,1697,1700,1703,1706,1709,1712,1715,1718,1721,1724,1727)

Here's my actual code that I'm using to filter everything (that's spitting out this query): http://pastebin.com/ceiib5Hf

That should make this clearer. Thanks for the replies so far!

Upvotes: 1

Views: 821

Answers (2)

Konerak
Konerak

Reputation: 39773

Put these "magic" numbers in a second table, that has two rows:

  • Magic Number
  • Sort order

Now join your table against this second table, and order by sort order. This will allow for easier maintenance of your query and numbers.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 839114

You can use FIELD(...):

ORDER BY FIELD(p.ID,1746,1741,...,1840,1843,1846)

Upvotes: 2

Related Questions