MySQL query question 1

[danima]

I have a small CMS on my site where myself or my partner are able to update content from a simple admin section. I went to the site and noticed the last article that was suppose to appear in the Monthly Article section was blank? I figured out the problem...but not the fix.

The article has a "displaydate" which was set for September 1, 2003. When I change the "displaydate" to October 1, 2003 it appears on the site. The problem is...I want the article the last month's article to remain on the site if there is no new article for the current month.

The query currently reads as follows:

function get_this_month_article()
{
$thismonth = mktime(0,0,0,date('m'),1,date('Y'));
$sql = "select * from articles where displaydate = $thismonth limit 1";
$result = mysqlQueryDb($sql);
$qdata = mysql_fetch_object($result);
return $qdata;
}

What would I change to ensure that the Sept. article would continue to be displayed if an Oct. article is not submitted?

Thanks in advance.

 

[DRJ478]

You can tweak your SQL and achieve what you want - it depends upon your table definition.
If displaydate is a date type column, you are all set. You can assume that when you order by display date in a descending order you will always receive the latest article available.

function get_this_month_article()
{
    $sql = "SELECT * FROM articles ORDER BY displaydate DESC limit 1";
    $result = mysqlQueryDb($sql);
    $qdata = mysql_fetch_object($result);
    return $qdata;
}

I believe this is an elegant and always working solution.

 

[danima]

That makes sense? I was trying to play with the programmers original query...but that makes way more sense...and it works!! Thanks for the quick response!

 

[danima]

Just got back from my run....good for clearing out the cob-webs....well, as they were clearing out, I realized that the above fix is okay except for the fact that we tend to stock pile articles. So, when I put a test in for November 1, 2003....this is the article that is diplayed.

I think that is why he wrote this:

$thismonth = mktime(0,0,0,date('m'),1,date('Y'));
$sql = "select * from articles where displaydate = $thismonth limit 1";

Hmmmmmm....now I'm really confused. Any way to just show the most current one without bringing up future articles.

"order by displaydate desc limit 1" obviously will bring up the November article if it's in the database.

Sorry to be a pest!

 

[DRJ478]

So, just combine the two statements:
1. get the latest available
2. make sure it is a date that is equal or less than the current.
The person that wrote the script actualy assumes that you have to have an exact match for the date. So, if you were to set the display date for the 5th instead of the 1st, it wouldn't work.
I recommend to always go from the current point in time and also have a flag that allows to switch from published to unpublished and vice versa.
Anyway, here's the SQL:

$sql = &quot;SELECT * FROM articles WHERE displaydate <= NOW() ORDER BY displaydate DESC limit 1&quot;;

This SQL selects the first article that has a display date equal or earlier than the current date.

 

[danima]

Funny, I have an article who's displaydate is Oct 1 and one that is Nov 1st. The Nov 1st one comes up instead of the Oct 1. The Nov 1st article is should turnover on that date?

Any ideas?

 

[DRJ478]

What is the column type for the dislaydate in the MySQL table exactly?
Does it contain the value that you expect?

DO you have a MySQL admin tool such as PHPMyAdmin at hand to inspect the table?

 

[danima]

Yes, I use PHPMyAdmin more than the command line. Here are the latest 2 entries....I want id 12 to show up...instead id 16 comes up. That is next month's article.

id | displaydate | title
12 | 1065931200 | It's Time To Get Listed
16 | 1068613200 | testing

Thanks for all of your help!

 

[DRJ478]

The UNIX timestamp you posted for record 16 corresponds to 11/12/2003 5:00 pm GMT.
There are only 2 reasons I can think of that it wouldn't work:
1. You are using something else but the SQL posted. The following should work correctly:

SELECT * FROM articles WHERE displaydate <= NOW() ORDER BY displaydate DESC limit 1

unless:

2. The server's time is completely wrong. If you run this on a local Windows machine, check the time and date.

Post the SQL in PHPMyAdmin and observe the result.

 

[danima]

Here's what I get

16 1068613200 testing

that's the november date? by the way....your talking to a guy who knows the term UNIX Timestamp and what it means...but definitely not how to read it

 

[DRJ478]

Ok, I should have realized this earlier:

It seems that your programmer has stored the literal UNIX timestamp in the table. The column type is probably not one of the date/time column types MySQL offers.

Then the SQL should be:

SELECT * FROM articles WHERE displaydate <= UNIX_TIMESTAMP() ORDER BY displaydate DESC limit 1

UNIX timestamp is based upon the UNIX epoch, i.e. seconds elapsed since 01/01/1970 at 00:00:00 GMT

 

[danima]

Awesome!! It works fine. Sorry for the confusion. And thanks for the UNIX explanation. I find that I try and learn so much that I never ask questions about the things I'm doing...I just do them and hope to God they work

Your diligence was greatly appreciated!