Help with MySQL query

[houndcomputers]

PHP version 5.1

 

I had a php page setup that was pulling info from a DB. This was working fine until the main Wordpress component was upgraded with major changes. So now I have to figure out a new way to do this as all of the fields are changed and some are not there at all.

 

My original query was pulling the last entry like this.

$result = mysql_query("SELECT * FROM  `wp_posts`  WHERE  `post_type` =  'gb_deal' AND `post_status` = 'publish' ORDER BY ID DESC LIMIT 1")
or die(mysql_error());
// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 
$post_id = $row['ID'];

 

This would give me the post Id and title before. I would then use this to pull all other info from another table 'wp_postmeta'

 

With the changes it pulls entries that are expired so i need to also check the 'wp_postmeta'  table with the same  post_id  and a 'meta_key' field result of  '_expiration_date' which is a timestamp i believe, the returned meta_value is like this 1309008900.

 

How do i combine these two searches so i get the most recent one that is not expired?

 

Here is the table setup of the second table.

 

 

 

wp_postmeta

field - value

meta_id   post_id - 145    meta_key - _expiration_date              meta_value - 1309008900

 

 

Note: ID from the first table and post_id from the second are the same and used to join the tables.

 

What i need the result to be is the ID of the most current one that is not expired. From there i should be able to pull the info i need once i figure out the current unexpired ID.

 

 

[AyKay47]

have you looked into using a Union ?

[houndcomputers]

I will give that a try. How would i check the timestamp to make sure it is not expired in the query though? I have done it using a date field but not when it is just a timestamp stored in a longtext type field.

[AyKay47]

hard to say, i'm not sure what format that time-stamp is in

[fenway]

Looks like a unix-timestampl

[AyKay47]

yes thank you Fenway

For comparison of the meta_value field, you can use the mysql function UNIX_TIMESTAMP()

[houndcomputers]

This is what I have come up with so far. It doesn't seem to be comparing the dates correctly, it pulls the same ID even if i change < to >. Am I missing something on how to compare the timestamp? The one it is pulling is expired and is in the middle of the table, not one of the ends like I would expect if something wasn't right.

 

$today = (current_time('timestamp')+24*60*60);

function current_time( $type, $gmt = 0 ) {

		return ( $gmt ) ? time() : time() + ( 0 * 3600 );
}

// Retrieve all the data from the "example" table
$result = mysql_query(
"(SELECT ID FROM  `wp_posts`  WHERE  `post_type` =  'gb_deal' AND `post_status` = 'publish'  ORDER BY ID DESC LIMIT 1)
UNION
(SELECT post_id FROM  `wp_postmeta`  WHERE  `meta_key` =  '_meta_deal_complete_status' AND '_expiration_date' < '" . $today . "')  "
)
or die(mysql_error());  

// store the record of the table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 

$post_id = $row['ID'];
echo $post_id;

[fenway]

Huh?  What's the "middle of the table"?

[houndcomputers]

I mean by sorting. Instead of the first or last result i would have expected if the time comparison wasn't working it pulls the very same ID from the middle each time.

[houndcomputers]

Either way that is not really important, that was just my thoughts. The issue seems to be comparing the timestamp. I believe i have the format right as i found that in the new code. What I need to know is am i comparing it correctly?

[fenway]

It would be preferable to compare them in mysql directly -- if you have a unix-timestamp on one side, then use the corresponding mysql function to convert the other side.

[houndcomputers]

This is what I ended up having to do to get it to work. Is there a more efficient way to accomplish this?

 

function current_time( $type ) {

		return time() + (1 * 3600 );
}

$today = (current_time('timestamp'));
echo $today . "<br>";
echo date('m/d/Y G:i', $today) . "<br><br>";

// Make a MySQL Connection
include 'config.php';
include 'opendb.php';

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT ID FROM  `wp_posts`  WHERE  `post_type` =  'gb_deal' AND `post_status` = 'publish' ORDER BY ID DESC")
or die(mysql_error());  

$found = false;

// store the record of the "example" table into $row
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
// Print out the contents of the entry 


$post_id = $row['ID'];
$result2 = mysql_query("SELECT meta_value FROM  `wp_postmeta`  WHERE  `meta_key` =  '_expiration_date' AND `post_id` = $post_id")
or die(mysql_error());

while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)) {
// Print out the contents of the entry 

$expiration_date = $row2['meta_value'];
if ($expiration_date > $today) {
echo $post_id . "<BR>";
echo $expiration_date  . "<BR>";
echo date('m/d/Y G:i', $expiration_date) . "<br>";
$found = true;
break;
}
}
if ($found) break; 
}

[fenway]

I suggested an alternative.