Extracting Latest Post from WordPress DB

skipcollege

I am attempting to build a script that extracts certain data out of a WordPress database from the most recent published entries for multiple blogs to display on my Non-WP index page. So I can show the last entries from all of my multiple WordPress blogs which WP does not natively support with its 'Loop'.

I'm working on finding the last post that has a status of published for a blog. My current approach is to find the total number of posts where that number will be the same as the ID of the last post. Then I will try to work back from the last ID to find the last post with a status of 'published'.

I've retrieved the last post ID. However, I am having a problem before I've even attempted to work back to the last 'published' ID. Here is my code...

//1. Gets Total Number of Posts by counting rows.
$query = "SELECT ID FROM skipcollege_posts";
$result = mysql_query($query);
if ($result) { $rows = mysql_num_rows($result); } else { mysql_error(); }
echo("<b>".$rows."</b>");
//1. Gets Total Number of Posts by counting rows.

//2. Attempting to get some data from the last post before even checking whether it is published or not.
$result2 = mysql_query("SELECT post_title FROM skipcollege_posts WHERE ID = $rows");
echo("<i>".$result2."</i>");
//2. Attempting to get some data from the last post before even checking whether it is published or not.

The script spits out the following...

9 Resource id #3

The "9" is the correct result from the first part of the script but I can't figure out why it is coming back with "Resource id #3" for the second part. I've tried the second part of the script with no quotes, single quotes & double quotes around the $rows variable without any success. Also, I've tried replacing the $rows variable with a number with out success. I may be using functions incorrectly?

Thanks.

Roger_Ramjet

Cos $results2 is the reference to the resultset, the resource no printed, not the contents found there. You need to process the results with mysql_fetch_array() or similar functions.

skipcollege

Thanks Roger. Got that part fixed up.

Now I've come to a new issue...

I have the script to the point where it finds the last ID number and if it doesn't have a status of "publish" then it subtracts 1 from the last ID. So if the last ID was 10 it would reduce the ID variable to 9. I am trying to make this into a loop at this point so it keeps doing this until it finds an ID a status of "publish" but I can't seem to get that working.

Here is my working code before trying to add the loop...

	$query = "SELECT ID FROM skipcollege_posts";
	$result = mysql_query($query);
	if ($result) { $rows = mysql_num_rows($result); } else { mysql_error(); };
	echo("Gets total number of post IDs from certain blog and displays it here: <b>".$rows."</b>");

echo("<br><br>");

$query11 = "SELECT post_status FROM skipcollege_posts WHERE ID = '$rows'";
$result11 = mysql_query($query11);
if ($result11) { $blob = mysql_fetch_array($result11, MYSQL_ASSOC); } else { mysql_error(); };
if ($blob['post_status'] != 'publish')
	{
	echo("Checked the last post ID in the blog and it was not published. That post ID was # ".$rows);
	$rows = ($rows - 1);
	echo("<br>Subtracted 1 from the rows variable and is now ".$rows);
	}
else {
	echo($rows." Post is 'published'");
	};

Here is the result in the browser...

Gets total number of post IDs from certain blog and displays it here: 10

Checked the last post ID in the blog and it was not published. That post ID was # 10
Subtracted 1 from the rows variable and is now 9

Here is my code after trying to add the loop...

	$query = "SELECT ID FROM skipcollege_posts";
	$result = mysql_query($query);
	if ($result) { $rows = mysql_num_rows($result); } else { mysql_error(); };
	echo("Gets total number of post IDs from certain blog and displays it here: <b>".$rows."</b>");

echo("<br><br>");

$good = "0";

while ($good = "0") {
$query11 = "SELECT post_status FROM skipcollege_posts WHERE ID = '$rows'";
$result11 = mysql_query($query11);
if ($result11) { $blob = mysql_fetch_array($result11, MYSQL_ASSOC); } else { mysql_error(); };
if ($blob['post_status'] != 'publish')
	{
	echo("Checked the last post ID in the blog and it was not published. That post ID was # ".$rows);
	$rows = ($rows - 1);
	echo("<br>Subtracted 1 from the rows variable and is now ".$rows);
	$good = "0";
	}
else {
	echo($rows." Post is 'published'");
	$good = "1";
	};
};

Here is the result in the browser...

Gets total number of post IDs from certain blog and displays it here: 10

I defined the variable $good = "0"; before my added While Loop. I tried wrapping the While loop around the part I want repeated. I set the While Loop to process as long as the $good variable = "0". In a following IF statement within the While Loop I set the $good variable to "1" if the post ID does have a status of "publish" so hopefully the loop would stop and then continue the script and if the post ID does not have a status of "publish" then the $good variable is set to "0" and then hopefully the While Loop will run itself again with the lowered ID variable that it uses.