OK, So I want to list all of the records that I have in a particular column on a wp table. Essentially I'm trying to make a site map query that I can just output on a page. So, so far I have... $sql = 'SELECT `post_title` FROM `wp_posts` ORDER BY `post_status` ASC LIMIT 0, 1000 '; Now with this, I'm am going to be making the assumption that there will only ever be 1000 records to pull (Is there an infinite or something sign I can use instead?) So that will pull the page title. Then I need to pull the page url... $sql = 'SELECT `guid` FROM `wp_posts` ORDER BY `post_status` ASC LIMIT 0, 1000 '; I *think* this is all good so far... But I guess what I'm saying, is how do I a) Get these results to appear on a page and b) Make sure the Page title correlates to the page URL? I am trying *really* hard to learn some of this coding stuff - I just need a push in the right direction (Or maybe a slap to tell me that I'm doing it all wrong)... Cheers Oliver
First to do it infinite just remove "LIMIT 0, 1000" all together. To make the page title correlate with the url use one select statement: $sql = 'SELECT `post_title`, `guid` FROM `wp_posts` ORDER BY `post_status` ASC'; To make them appear on a page: $result = mysql_query($sql); $num = mysql_num_rows($result); $i=0; while ($i < $num) { $post_title = mysql_result($result,$i,"post_title"); $guid = mysql_result($result,$i,"guid"); echo ("<a href=\"$guid\">$post_title</a><br>"); $i++; } PHP:
Ah cool... So, sorry - Complete n00b at all this $result = mysql_query($sql = 'SELECT `post_title`, `guid` FROM `wp_posts` ORDER BY `post_status` ASC';); $num = mysql_num_rows($result); $i=0; while ($i < $num) { $post_title = mysql_result($result,$i,"post_title"); $guid = mysql_result($result,$i,"guid"); echo ("<a href=\"$guid\">$post_title<br />"); $i++; } Code (markup): Is that what I need to have then? It's throwing up an error...
Try this: $sql = "SELECT `post_title`, `guid` FROM `wp_posts` ORDER BY `post_status` ASC"; $result = mysql_query($sql); $num = mysql_num_rows($result); $i=0; while ($i < $num) { $post_title = mysql_result($result,$i,"post_title"); $guid = mysql_result($result,$i,"guid"); echo ("<a href=\"$guid\">$post_title</a><br />"); $i++; } PHP:
Wow - You are a-w-e-s-o-m-e OK, so I don't have to ask in the future, would you mind telling me how this actually works? I get the SQL bit
ok... well slow day at the office anyways //stores the sql statement in $sql $sql = "SELECT `post_title`, `guid` FROM `wp_posts` ORDER BY `post_status` ASC"; //runs the sql query and stores all the results in the $result variable $result = mysql_query($sql); //counts the numbers of rows from the results of the sql statement $num = mysql_num_rows($result); //counter used to see when you have gone through all the rows $i=0; //loop used to go through each row of your result while ($i < $num) { //these 2 lines select 1 row at a time from the results (since $i goes up once every time it loops) $post_title = mysql_result($result,$i,"post_title"); $guid = mysql_result($result,$i,"guid"); //prints the results to the users end echo ("<a href=\"$guid\">$post_title</a><br />"); $i++; } PHP:
OK, so I've done a bit of this myself, I'm now just a little stuck at one bit... $sql = "SELECT `post_title`, `guid`, `post_date`, `post_status` FROM `wp_posts` ORDER BY `post_status` DESC"; $result = mysql_query($sql); $num = mysql_num_rows($result); $i=0; while ($i < $num) { $post_date = mysql_result($result,$i,"post_date"); $post_status = mysql_result($result,$i,"post_status"); $post_title = mysql_result($result,$i,"post_title"); $guid = mysql_result($result,$i,"guid"); if ( $post_status == "static" ) { echo ("<li><a href=\"$guid\">$post_title</a></li>"); } elseif($post_status="publish"){ echo ("<li><a href=\"$guid\">$post_title</a> - $post_date</li>"); } $i++; } Code (markup): Now this currently means that it echo's a list, only of pages in the DB, who's post_status is either "static" or "publish" and orders them in groups together. How do I add a subheading to each group? So I currently have And what I want to have is Does that make sense?
You could do something like this, which basically prints a header whenever the post_status changes (untested but pretty simple). You also might want to use the PHP BBCode when posting as it's make it really really pretty $sql = "SELECT `post_title`, `guid`, `post_date`, `post_status` FROM `wp_posts` ORDER BY `post_status` DESC"; $result = mysql_query($sql); $num = mysql_num_rows($result); $i=0; $previousStatus = ""; while ($i < $num) { $post_date = mysql_result($result,$i,"post_date"); $post_status = mysql_result($result,$i,"post_status"); $post_title = mysql_result($result,$i,"post_title"); $guid = mysql_result($result,$i,"guid"); if($previousStatus != $post_status) { // Status has changed so print the header echo "<li><b>$post_status header</b></li>"; $previousStatus = $post_status; } if ( $post_status == "static" ) { echo ("<li><a href=\"$guid\">$post_title</a></li>"); } elseif($post_status="publish") { echo ("<li><a href=\"$guid\">$post_title</a> - $post_date</li>"); } $i++; } PHP:
Mate, that is f'in awesome! Will use the php bbcode in future... OK then... So for the headers, it is pulling the actual name in the DB out right?
Great cheers mate... I'm working on getting it to do this with the post category now... Hopefylly it will work right. Cheers
So... *cough*... How do I give them custom names rather than pulling them directly from the DB? *cough*
Do something like if($previousStatus != $post_status) { // Status has changed so print the header $previousStatus = $post_status; if ( $post_status == "static" ) { echo "<li><b>Lazy Bastard Static header</b></li>"; } else if ( $post_status == "publish" ) { echo "<li><b>Horribly hardcode publish header</b></li>"; } } PHP: Now hang your head in shame hard coder.
Hehe - yeah, man - I need help... You must spread some Reputation around before giving it to dct again.