so what i figured i need to do is get the post_id, and create an small array for each one, that contains the a meta_key. something like this.
$get_data = “SELECT post_id FROM gh_postmeta WHERE metakey = ‘_affid_field’ AND meta_value = $AffID”;
this would have to run for each $affID first though… then I can run get the post_id and retrieve each meta_value for the meta_keys I need. but how do I do that vertical array?
I figured i could simply do the foreach $affid, get the post ID then do a query for each meta_key that I need. but there must be a more simplified way to do this?
I’m wondering if you could do it with a sub-query, but a quick dirty way might be like this pseudo-code
select * from whatever order by post_id
full_array = array()
prev_postid = "";
sub_array = array()
while (row = fetch() ) {
if row[post_id] != prev_postid {
full_array[] = sub_array; // add the post details to the full array
sub_array = array(); // clear the sub-array
}
prev_postid = row[post_id]
sub_array[$row[meta_key]] = $row[meta_value] // build the sub-array for each post
}
no i meant the database is structured vertically instead of each item in a column this is so you can add whatever meta you want of course… so i have to get a post id. then several metakeys for that post id with the values… so should be done by finding first the post id with the I need then get the meta ID for each metakey i need so i can then get the value … lol i know a very long and silly way to code it out but wanted to do something a bit more clean. anyways thanks for the advice.
I think separating this into two queries like are doing is your best bet. There is a way to do it in a single query but it requires grouping and a having clause which will probably be less efficient than just grabbing the IDs with one query and loading all the fields for those posts with another query. Something ike this is what you could do but I don’t think it would be as efficient/scalable as two separate queries, especially if you need pagination.
SELECT post_id, meta_key, meta_value, COUNT(CASE WHEN meta_key = ‘affidfield’ AND meta_value = $AffID" THEN 1 ELSE 0 END) matches FROM gh_postmeta GROUP BY post_id HAVING matches <> 0;
I don’t think that will be very efficient though and for pagination it won’t work.
Drupal 6 cck uses a similar architecture and it uses the approach you suggest. One query to grab IDs and another to load all the fields associated with those entities. Another approach is to use flat tables where the dynamic structure is converted to a real table. That is what Magento does.
Doesn’t wp provide a function to load posts with all the fields? Drupal does and it actually caches those things so sometimes the extra query doesn’t even result in an extra query.
