Get from query and use on query

programming
1

Ok, I have something to do…
wp_post2cat is the table where im trying to get the post_ids from… Now…
I have only one value which i need to use to get the post ids… That value is the category id… because it’s on the link. Anyways… I need to use the category id to send it to wp_post2cat and get post ids… then i need to connect to wp_posts to get the post titles and dates using the post ids i got from the query before. **Problem: **The first query is only getting ONE value… which means that it’s only getting the title and date from ONE post. It’s really confusing… so here is the code:


$catID = $_GET['cat'];
$catPost_count = 0; 

$sqlNum = mysql_query("SELECT * FROM wp_post2cat WHERE category_id=$catID");
$sqlNumCats = mysql_fetch_row($sqlNum);

$sqlPostGet = "SELECT * FROM wp_posts WHERE ID=$sqlNumCats[0]";
$sqlPostGetFinal = mysql_query($sqlPostGet);


while ($postGet = mysql_fetch_assoc($sqlPostGetFinal)):
$catPost_row = ($catPost_count % 2) ? 'row2' : 'row1';
$monthWord[1] = "JANUARY";
$monthWord[2] = "FEBRUARY";
$monthWord[3] = "MARCH";
$monthWord[4] = "APRIL";
$monthWord[5] = "MAY";
$monthWord[6] = "JUNE";
$monthWord[7] = "JULY";
$monthWord[8] = "AUGUST";
$monthWord[9] = "SEPTEMBER";
$monthWord[10] = "OCTOBER";
$monthWord[11] = "NOVEMBER";
$monthWord[12] = "DECEMBER";

$date = $postGet['post_date'];
list($year, $month, $day, $hour, $minute, $second) = split('[-: ]', $date);
$month = ucwords($month);
echo "<div class=\"{$catPost_row}\"><a href=\"http://mynewdomainiscool.com/wordpress/?p={$postGet['ID']}\">{$postGet['post_title']} -- {$monthWord[$month]} {$day} {$year}</a></div>";
$catPost_count++;

endwhile;
2

Judging from your table names, I’m assuming you’re using WordPress… try this bit of SQL and see if you get what you need. It should return return the post titles and dates of all the posts in the category specified by $catID.

$catID = 1; // your category ID

$result = $wpdb->get_results("SELECT post_title, post_date FROM wp_post2cat, wp_posts WHERE wp_post2cat.category_id = {$catID} AND wp_posts.ID = wp_post2cat.post_id"); // get the stuff

var_dump($result);

Another note: If you are coding something for WordPress, you should be using the wpdb class like I did above to handle all your database actions.

3

Man… seriously… Thanks so much. I’ve been trying to get this to work for days!

4

I assume you got it to work?

Just out of curiosity… What is this for?

5

Yes I got it to work.
It’s a job. Man… This wordpress crap is complicated… Now I need to make an archives page.

6

Ummm… I didn’t really want to post another thread… so here we go with another problem… I really don’t know what’s wrong.


<? 
$archiveTitle = $_GET['m'];
$monthWord[200601] = "Jan 2006";
$monthWord[200602] = "Feb 2006";
$monthWord[200603] = "Mar 2006";
$monthWord[200604] = "Apr 2006";
$monthWord[200605] = "May 2006";
$monthWord[200606] = "Jun 2006";
$monthWord[200607] = "Jul 2006";
$monthWord[200608] = "Aug 2006";
$monthWord[200609] = "Sep 2006";
$monthWord[200610] = "Oct 2006";
$monthWord[200611] = "Nov 2006";
$monthWord[200612] = "Dec 2006";
?>

<h2>Archives for '<? echo $monthWord[$archiveTitle]; ?>' </h2><br /><br />

<?
global $wpdb;

$getMonth[200601] = "01";
$getMonth[200602] = "02";
$getMonth[200603] = "03";
$getMonth[200604] = "04";
$getMonth[200605] = "05";
$getMonth[200606] = "06";
$getMonth[200607] = "07";
$getMonth[200608] = "08";
$getMonth[200609] = "09";
$getMonth[200610] = "10";
$getMonth[200611] = "11";
$getMonth[200612] = "12";
$monthWord[1] = "JANUARY";
$monthWord[2] = "FEBRUARY";
$monthWord[3] = "MARCH";
$monthWord[4] = "APRIL";
$monthWord[5] = "MAY";
$monthWord[6] = "JUNE";
$monthWord[7] = "JULY";
$monthWord[8] = "AUGUST";
$monthWord[9] = "SEPTEMBER";
$monthWord[10] = "OCTOBER";
$monthWord[11] = "NOVEMBER";
$monthWord[12] = "DECEMBER";



$arPosts = "SELECT DISTINCT ID AS id, post_title AS postTitle, post_date AS date FROM $wpdb->posts WHERE MONTH(post_date)=$getMonth[$archiveTitle] ORDER BY post_date DESC";
$arGet = $wpdb->get_results($arPosts);

list($year, $month, $day, $hour, $minute, $second) = split('[-: ]', $arGet->date);

echo "<div class=\"row1\"><a href=\"http://mynewdomainiscool.com/wordpress/?p={$arGet->id}\">{$arGet->postTitle} -- {$monthWord[$month]} {$day} {$day}</a></div>";
?>

:jail:

And… is there any way to get the number of results?

7

I’m going to assume your code is correct as I’m dead tired but here’s one thing I spotted:

$arPosts = "SELECT DISTINCT ID AS id, post_title AS postTitle, post_date AS date FROM {$wpdb->posts} WHERE MONTH(post_date)={$getMonth[$archiveTitle]} ORDER BY post_date DESC";

You must put braces around arrays and such to ensure that the values are used instead of the literal variable name.

Was there an error message by any chance? If so post it.

8

No… All it shows in the row is " – " … Meaning it’s not getting anything… I tried that, and it doesn’t work either.

9

Anybody? Please… I need to know how to fix it before this weekend because I have to finish the job on the weekend. :frowning: