Can you give a real example of what you are trying to do?
Sure thing.
Basically we have a product shelf that members can add stuff to. When a product is added to a member’s shelf, it shows on their personal shelf, but also on the public community shelf.
The issue is that when a member adds a product, it is stamped with a publish date and shows appropriately on their shelf according to the order they added it. However if a second member adds that same product, then the publish date gets renewed and it changes the order of the first members shelf.
What we want to do, is basically have an additional field attached to a product that is a member specific sequence number. The problem is that right now, since the shelf shares the same data as the community shelf, every member’s sequence code gets added as a value under the “Sequence” field. For example, a “Book 1” can have a member sequence value of “123_01, 143_14, 154_02, etc” where the first part of that number is the member id “123_” and the second part is the sequence to appear on their personal shelves. We can use PHP to separate out the values, then populate all the data back into an array, sort, then return it in a new order, but that seems likes a work around and not a long term solution. Ideally we want to create another simple table that would store and relate the sequence information. We done that on other databases we’ve built from scratch, but not with WordPress. I hope that helps.
Thanks.
I don’t believe that you need a separate table, but I need more detail about the code you use to select the records for an individual member.
For example, suppose the member number is in a variable named $member. Then a query similar to this should get the records in the proper order (UNTESTED):
$sql = " SELECT p.* FROM $wpdb->posts p
JOIN $wpdb->postmeta pm ON (p.ID = pm.post_id)
WHERE pm.meta_key = 'Sequence'
AND pm.meta_value LIKE '$member%'
ORDER BY pm.meta_value";
If you want to stick with query_posts, the same thing can be accomplished using filters to alter the query.
