[pcpro17]

Hey, does anyone know how to adapt this code so that it will pull entries from a specific date range? Preferably, I would like it to pull info for just today. Is there a function for that?

$sql = "SELECT * FROM wp_posts";
$result = mysql_query($sql,$link) or die("Unable to select: ".mysql_error());
print "<table>\n";
while($row = mysql_fetch_row($result)) {
    print "<tr>\n";
    foreach($row as $field) {
        print "<td>$field</td>\n";
    }
    print "</tr>\n";
}
print "</table>\n";

Here are the fields available in the table:

• post_author
• post_date
• post_date_gmt
• post_content
• post_title
• post_category
• post_excerpt
• post_status
• comment_status
• ping_status
• post_password
• post_name
• to_ping
• pinged
• post_modified
• post_modified_gmt
• post_content_filtered
• post_parent
• guid
• menu_order
• post_type
• post_mime_type
• comment_count

Thanks!

[brendandonhu]

Untested but this should work

PHP Code:

$sql = sprintf('SELECT * FROM wp_posts WHERE post_date BETWEEN %s AND %s', strtotime('today'), strtotime('today') + 86400); 


[pcpro17]

Thanks Brendan. I gave it a try. I'm not getting any errors, but it does not seem to be pulling any data in the query. Either that, or it's not printing the data. Any other suggestions?

[pcpro17]

Using Brendan's suggested modification and

print ($result);

$result apperantly equals "Resource id #3". Does anyone know what this means?

[cpscdave]

I could very easily be wrong but my experience with SQL is that DATE/DATETIME fields will not work if you pass into them unixtime stamps.
*edit* I tried this out briefly and could not get the query to return correct results using a unix timestamp isntead of actual date *edit*


You actually have to convert them into the correct format.

$start = strftime("%Y-%M-%D 00:00:00", time()); //will give you todays date at 12:00:00am
$end_timestamp = strtotime("+1 DAY",time()); //will give you time stamp 1 day from now
$end = strftime("%Y-%M-%D 00:00:00", $end_timestamp); //will give you tomoorows date at 12:00:00am

$theQuery = "SELECT * FROM wp_posts WHERE post_date BETWEEN '{$START}' AND '{$END}' ";

[brendandonhu]

That might be right, I'm not sure if post_date is a DATETIME or a Unix timestamp.

[pcpro17]

Thanks for the suggestion, Dave. A thought occured to me. It might be working (no errors), but the query range is off. I'm working from a dummy database right now, so how would I modify the query to pull everything in the last month (or two)? Thanks.

[pcpro17]

One more thought:
I checked the settings in the database, and it says that the default format for post_date is "0000-00-00 00:00:00". What is being generated is something like "2007-52-05/17/07 00:00:00". Are these formats the same?

[cpscdave]

Originally Posted by pcpro17

One more thought:
I checked the settings in the database, and it says that the default format for post_date is "0000-00-00 00:00:00". What is being generated is something like "2007-52-05/17/07 00:00:00". Are these formats the same?

are you using the same formating string?
to get something like 2007-52-05/25/07 00:00:00 your format string would look like

%Y-%U-%m/%D/%y (I think)

%Y-%m-%d 00:00:00 should give you 2007-05-16 00:00:00 (if today was May 16th 2007)

You can see all the formating elements at:
http://ca3.php.net/strftime

Also if you are using a framework that breaks up the sql statement and recombines it be careful. The one we use had an issue where it would interpret BETWEEN <blah> AND <blah> wrongly and would recombine it wonkly.

[pcpro17]

Thanks for the suggestion, Dave! It's now pulling data from the MySQL database! But, this is only half the battle. Next, I need to get it to pull only certain data. Currently, it's pulling everything in the database in the given date rage. Here is what my code looks like now:

<?php
  include ("__db_open.php");

  $start_timestamp = strtotime ("-30 DAY", time());
  $start           = strftime("%Y-%m-%d %H:%M:%S", $start_timestamp);
  $end_timestamp   = strtotime ("+1 DAY",time());
  $end             = strftime("%Y-%m-%d %H:%M:%S", $end_timestamp);

  $query = "SELECT * FROM wp_posts WHERE post_date BETWEEN '$start' AND '$end'";

  $result = mysql_query($query,$link) or die("Unable to select: ".mysql_error());

  print "<table>\n";
  while($row = mysql_fetch_row($result)) {
    print "<tr>\n";
    foreach($row as $field) {
        print "<td>$field<br>$row</td>\n";
      }
    print "</tr>\n";
    }
  print "</table>\n";

  mysql_close($link);
?>

If you'd like to see what it's doing, go here:
http://www.correctnesscommentary.com/__test.php

Any suggestions on how to get it to pull data for only post_date, post_content, and post_title?

Thanks again!

[cpscdave]

Originally Posted by pcpro17

Any suggestions on how to get it to pull data for only post_date, post_content, and post_title?

Thanks again!

What version of MySQL are you using?? I dont belive BETWEEN worked correctly with dates till 4.3.x
Also print out $query just before you send it to mysql it should look something like
SELECT * FROM wp_posts WHERE post_date BETWEEN '2007-04-16 15:41:37' AND '2007-05-17 15:41:37'

What format is post_date stored in the database? are they of type datetime? if they are a string or some other format this will not work (and you should porbally convert the field to a datetime)

as far as only having the data base return specific fields change your query from:

SELECT * FROM table WHERE blah;

TO

SELECT field1, field2, field3 FROM table WHERE BLAH

[pcpro17]

Hello Dave. The MySQL client version is 5.0.16. I've attached a screen shot of the wp_posts table. I think it has the formatting info you requested.

[pcpro17]

Oh, if I'd built this database myself, I'd have no issue with changing the formats of stuff, but this database was generated by the WordPress setup, and because I'm planning on using the default WordPress interface to make updates, I don't want to do anything that might break it.

[pcpro17]

Oops, I also noticed a typo on my previous post. The code posted does pull data without any errors, but it's pulling everything within the date range. I just want it to pull only post_date, post_content, and post_title for now.

[cpscdave]

Oh that makes life a lot easier

Change

 $query = "SELECT * FROM wp_posts WHERE post_date BETWEEN '$start' AND '$end'";

TO

 $query = "SELECT post_date, post_content, post_title  FROM wp_posts WHERE post_date BETWEEN '$start' AND '$end'";