Canman2005 Posted January 8, 2008 Share Posted January 8, 2008 Hi all I'm wondering if anyone can help, I have been trying to crack this for hours and cannot seem to find a way to solve it Basically i'm using WordPress but tweaking some of the code to fit the site I have built. Please bear with me, I am going to try and give as much information as I can. Let me start by giving you a quick overview of the databases. There are two main databases, one called "wp_postmeta" which contains the posts and then another table called "wp_posts" which holds the full details of the post. A small dump of the two databases are -------------------------------------------------------------- wp_postmeta meta_id | post_id | meta_key | meta_value 4 33 promo day event 5 34 promo weekend event 6 35 promo christmas event 7 36 promo day event -------------------------------------------------------------- wp_posts ID | post_author | post_date_gmt | post_title 33 dave smith 2007-01-08 08:17:52 test post 34 john wilks 2007-01-09 12:23:11 another post 35 dave smith 2007-01-11 13:21:43 another one 36 chaz dome 2007-01-11 14:54:02 my last post -------------------------------------------------------------- the following is my QUERY, I will explain how it works and the problem I have at the bottom, if you want to see more of the QUERY, please let me know. $sql = "SELECT * FROM `wp_postmeta` WHERE `meta_key` = 'promo' GROUP BY `meta_value` ORDER BY `meta_value` ASC"; $result = mysql_query($sql); while ($row = mysql_fetch_assoc($result)) { print $row['meta_value']; $sql1 = "SELECT * FROM `wp_postmeta` WHERE `meta_value` = '".$row['meta_value']."'"; $result1 = mysql_query($sql1); $rows2 = mysql_num_rows($result1); $i = 1; while ($row1 = mysql_fetch_assoc($result1)) { $querystr = "SELECT * FROM `wp_posts` WHERE `ID` = '".$row1['post_id']."'"; $pageposts = $wpdb->get_results($querystr, OBJECT); if ($pageposts): foreach ($pageposts as $post): setup_postdata($post); Okay, so what it does initally is look in the "wp_postmeta" table and returns all rows that have a "meta_key" value set as "promo", so the results look like day event weekend event christmas event day event it then does a GROUP BY meta_value which returns the results as day event weekend event christmas event (as day event is listed twice under "meta_value", the GROUP BY only shows one) The next QUERY looks in "wp_postmeta" and uses the "meta_value" from the last QUERY to grab the details of that row. Finally it does one last QUERY for each row returned, uses the "post_id" to look in "wp_posts" and grab the row with the "id" number which matches the "post_id" returned in the previous QUERY. So you end up with the results day event > test post (2007-01-08 08:17:52) > my last post (2007-01-11 14:54:02) weekend event > another post (2007-01-09 12:23:11) christmas event > another one (2007-01-11 13:21:43) Does that make sense? Maybe a little too detailed, but hopefully it will help you to make sense. Right, my problem. Because there might be multiple posts with the same "meta_value" (from "wp_postmeta") and the same "post_title" (from "wp_posts"), the resuls could look like day event > my post (2007-01-08 08:17:52) > my post (2007-01-11 14:54:02) As you can see, there is a "my post" twice, this is what I want to overcome. The way I want to overcome this, it by ordering them by date, so the above results would look like day event > my post (2007-01-11 14:54:02) > my post (2007-01-08 08:17:52) Showing the row with the latest date first. The problem is that the date information is stored in the table "wp_posts", but the QUERY which sorts the ORDER of the rows retured comes from the table "wp_postmeta", which doesnt contain any date information. Does this make any sense? Can anyone please help me? Any help would be ace Thanks in advance Dave Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted January 8, 2008 Share Posted January 8, 2008 Okay well in the query: $querystr = "SELECT * FROM `wp_posts` WHERE `ID` = '".$row1['post_id']."'"; You can select the post_title column and look through that entire table again to find the same names. Then order them by the date. Does this make sense? Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted January 8, 2008 Author Share Posted January 8, 2008 Hi Doesnt make sense, sorry, think its too long on this problem has fried my brain Could you explain further? Thanks Dave Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted January 8, 2008 Share Posted January 8, 2008 Right, my problem. Because there might be multiple posts with the same "meta_value" (from "wp_postmeta") and the same "post_title" (from "wp_posts"), the resuls could look like day event > my post (2007-01-08 08:17:52) > my post (2007-01-11 14:54:02) As you can see, there is a "my post" twice, this is what I want to overcome. I just went by that. So on your query: $querystr = "SELECT * FROM `wp_posts` WHERE `ID` = '".$row1['post_id']."'"; You can then say something like: <?php $blah = mysql_query($querystr) or die(mysql_error()); while ($bloh = mysql_fetch_assoc($blah)){ $my_query = mysql_query("SELECT * FROM wp_posts WHERE post_title='{$bloh['post_title']}' ORDER BY post_date_gmt DESC") or die(mysql_error()); while ($final = mysql_fetch_assoc($my_query)){ // do something // }} ?> Note: rather than mysql_fetch_assoc($blah), it's probably better to use mysql_fetch_row since you only need 1 thing. Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted January 8, 2008 Author Share Posted January 8, 2008 Hi So what do I change $querystr = "SELECT * FROM `wp_posts` WHERE `ID` = '".$row1['post_id']."'"; to?? i'm picking one row with the above query, therefore the code you showed would only run on that one row. Do I need to alter that query? Thanks Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted January 8, 2008 Author Share Posted January 8, 2008 the main problem is that in the table where the data that we need is, there is no reference back to the table which holds which category they are in. its most likley something simple, but so hard to explain Quote Link to comment Share on other sites More sharing options...
teng84 Posted January 8, 2008 Share Posted January 8, 2008 $query = " SELECT * FROM wp_postmeta INNER JOIN wp_posts ON post_id = wp_posts.ID WHERE wp_posts.ID = '".$row1['post_id']."' AND `meta_key` = 'promo'"; $result = mysql_query($query); $pageposts = $wpdb->get_results($result, OBJECT); may that is only what you need ! Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted January 8, 2008 Author Share Posted January 8, 2008 Thanks I can see where you going, but for some reason that is returning every row in the "wp_posts" table Any ideas? Quote Link to comment Share on other sites More sharing options...
teng84 Posted January 8, 2008 Share Posted January 8, 2008 can you tell me how do you want to limit your query? you might just need to ad some condition on that query! or tell me what output does that query produce and what output you're expecting Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted January 8, 2008 Author Share Posted January 8, 2008 basically I have a table which holds main categories and for each row in that table, there is an ID number, that ID number relates to another table which holds the articles relating to the category which its ID it stores. If that makes sense what i want to do is group all the categories together so that rather than repeating duplicate categories, it just shows one of each. i then want to pull all the articles relating to the category listed under it. so results would look like category 1 > article 1 > article 2 > article 3 category 2 > article 4 > article 5 category 3 > article 6 > article 7 the problem is that because I run a GROUP BY it only returns one ID number of each category grouped, so even if there were 10 categories listed, it would only return the first ID number, this means you only get one reference to the article ID number and the other ones are lost. does that make any sense Quote Link to comment Share on other sites More sharing options...
teng84 Posted January 8, 2008 Share Posted January 8, 2008 YOU ARTICLE AND CATEGORY REFER TO WHAT FILED OF YOUR TABLE? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted January 8, 2008 Share Posted January 8, 2008 YOU ARTICLE AND CATEGORY REFER TO WHAT FILED OF YOUR TABLE? ??? ??? ??? teng84, that question makes no sense. Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted January 8, 2008 Author Share Posted January 8, 2008 Hi I think its SELECT * FROM wp_postmeta INNER JOIN wp_posts ON post_id = wp_posts.ID WHERE wp_posts.ID = '75' AND `meta_key` = 'promo' AND `meta_value` = 'category title' Quote Link to comment Share on other sites More sharing options...
teng84 Posted January 8, 2008 Share Posted January 8, 2008 $sql = "SELECT DISTICNT(`meta_value`) AS metanewval, post_id FROM `wp_postmeta` WHERE `meta_key` = 'promo' ORDER BY `meta_value` ASC"; $result = mysql_query($sql); while ($row = mysql_fetch_assoc($result)){ print $row['metanewval']; print $row['post_id']; $querystr = "SELECT * FROM `wp_posts` WHERE `ID` = '".$row['post_id']."' AND wp_posts.ID =".$row['post_id']." "; //$pageposts = $wpdb->get_results($querystr, OBJECT); $query = mysql_query($querystr); while($rows = mysql_fetch_assoc($query)){ echo $rows['post_date_gmt']; } } maybe this @Ken2k7 why? Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted January 8, 2008 Author Share Posted January 8, 2008 Cracked it Thanks for the help, your query pretty much did what I wanted Peace out Quote Link to comment Share on other sites More sharing options...
teng84 Posted January 8, 2008 Share Posted January 8, 2008 i did?? hmm i'm expecting additional question or clarification on that code for i dont test it Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.