[SOLVED] showing only current date results

Onloac · September 20, 2009

I've been trying to figure this out, i'm using a query to grabs from my database table. Within the row I have a timestamp and want to only display results that are from today. How would I go about doing that?

"SELECT * FROM myarticles ORDER BY date DESC LIMIT 20"

MadTechie · September 20, 2009

"SELECT * FROM myarticles WHERE date(joindate) = date(now()) ORDER BY date DESC LIMIT 20"

change joindate to the timestamp field name

Onloac · September 20, 2009

I went from showing the last 20 to a white space where the results use to be. I'm using:

"SELECT * FROM myarticles WHERE date(date) = date(now()) ORDER BY date DESC LIMIT 20"

date is the name of the column my timestamp is under. I simply want to check the current date and then pull any rows that have a timestamp from the same day. Right now its not showing anything, what am I doing wrong?

Onloac · September 20, 2009

this is the full code. I took out all the stuff within the while loop to make it easier on the eyes.

$rowDB = $db->query("SELECT * FROM myarticles WHERE date(date) = date(now()) ORDER BY date DESC LIMIT 20") or die("Not grabbing articles");
while($r = $db->fetch_array( $rowDB )) {
}

ozestretch · September 20, 2009

How is the 'date' field stored in the database?

Onloac · September 20, 2009

unix timestamp. example would be 1253394080

MadTechie · September 20, 2009

try this

$rowDB = $db->query("SELECT * FROM myarticles WHERE date(`date`) = date(now()) ORDER BY `date` DESC LIMIT 20") or die("Not grabbing articles");
while($r = $db->fetch_array( $rowDB )) {
}

also you sure you have record with today's date

Onloac · September 20, 2009

Yes, I created one to test it. Once I get it to actually display todays article i'll work on getting it to display a message when no articles are found. Still can't seem to get it working though. I'm using the exact code you gave me above and its still a blank page.

MadTechie · September 20, 2009

Table

CREATE TABLE `test` (
  `test1` varchar(2) NOT NULL,
  `joindate` timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`test1`, `joindate`) VALUES
('ol', '2009-09-18 03:59:52'),
('ne', '2009-09-20 04:21:18');

Query

 SELECT * FROM test WHERE date( `joindate` ) = date( now( ) ) ORDER BY `joindate` DESC LIMIT 20

Result

test1 joindate
ne 2009-09-20 04:21:18

Query Seams fine

can you manually test the query your end

Onloac · September 20, 2009

query works fine until i add WHERE date(`date`) = date(now()). Its blank if its there and if I take it away it shows the last 20 items.

5kyy8lu3 · September 20, 2009

SELECT * FROM myarticles WHERE DATE(date) = CURDATE() ORDER BY date DESC LIMIT 20

Onloac · September 20, 2009

Alright, I tried yours as well 5ky... and it came out blank. Is there another way to do what I'm looking for? I dont know why its not working for me. :S

5kyy8lu3 · September 20, 2009

Alright, I tried yours as well 5ky... and it came out blank. Is there another way to do what I'm looking for? I dont know why its not working for me. :S

like you copy pasted it word for word? i just changed the table name and column name so it works with my table and it worked just fine:

SELECT ID, Timestamp FROM IPLog WHERE DATE(Timestamp) = CURDATE() ORDER BY Timestamp DESC LIMIT 20

returned:

ID Timestamp

2586 2009-09-20 01:28:58

2584 2009-09-20 00:54:12

2585 2009-09-20 00:54:12

2583 2009-09-20 00:53:54

2581 2009-09-20 00:41:53

EDIT: by the way, what data type is your "date" column set to? I think it has to be DATETIME to work correctly, that or you have to put it into a VARCHAR column in a "suitable" format, like 20090920003900 or 2009-09-20 00:39:00. just a though

Onloac · September 20, 2009

ok. just wondering... why are your timestamps converted? mine are stored in the database as 1253394080 not 2009-09-20 00:41:53. Does that make a difference?

ozestretch · September 20, 2009

unix timestamp. example would be 1253394080

I did ask him this earlier, and he did answer :-[

5kyy8lu3 · September 20, 2009

ok. just wondering... why are your timestamps converted? mine are stored in the database as 1253394080 not 2009-09-20 00:41:53. Does that make a difference?

yup. for one, even if you don't use DATETIME as your column type, if you do year, month, day, hour, minute, seconds, you go biggest to smallest so you can literally sort by that number. (like today's timestamp in that format would be a bigger number than yesterday's timestamp)

but if you use DATETIME, you can even send a timestamp in like "20090920003900" and it'll automatically convert it to "2009-09-20 00:39:00"

but yea, if you want to use mysql's built in date functions, like NOW(), CURDATE(), DATE(), etc, you should really use DATETIME

Onloac · September 20, 2009

So I guess I'm failing to explain my setup correct the first time? Sorry, I'm still trying to learn the ropes. Right now the timestamps are saved as "1253394080" and not "2009-09-20 01:28:58". With that in mind, would the code your giving me still work correctly for me or do I have to find a way to do it outside the query? In the end I just want to check for articles posted under today's date, if articles are find display if none are found, say no new articles today.

5kyy8lu3 · September 20, 2009

So I guess I'm failing to explain my setup correct the first time? Sorry, I'm still trying to learn the ropes. Right now the timestamps are saved as "1253394080" and not "2009-09-20 01:28:58". With that in mind, would the code your giving me still work correctly for me or do I have to find a way to do it outside the query? In the end I just want to check for articles posted under today's date, if articles are find display if none are found, say no new articles today.

hmm I honestly don't know, I know mysql supports unix timestamp, but I just don't know if the functions used with DATETIME work with UNIX too without some conversion function (like php you can use strtotime()), wish I could tell ya.

if you can't figure out a way to make the stuff work with your unix timestamps, you could make a new column with DATETIME and write a quick maintenance script to pull and convert your timestamps for your new column. it's not as fun as finding a real solution but it's always an option =)

Onloac · September 20, 2009

Yeah, I just figured it would be a very simple task to complete. I'm surprised I'm having some much trouble with it.

$newsDB = $db->query("SELECT * FROM myarticles WHERE date('date') = CURDATE() ORDER BY date DESC LIMIT 20") or die(mysql_error());
while($news = $db->fetch_array($newsDB)) {
echo "<TABLE CELLSPACING=\"0\" CELLPADDING=\"1\" BORDER=\"0\">";

// CHECK TO SEE IF THE TITLE HAS MORE THEN 50 CHARACTERS AND EDIT AS NEEDED
echo "<TR><TD><a class=\"headline\" href=\"?id=".$news['id']."\">";
if(strlen($news['title']) > 45) { echo substr($news['title'],0,45)."..."; } else { echo $news['title']; }
echo "</a><br></TD></TR>";

echo "<TR><TD class=\"small\">".date("l, F jS, Y", $news['date'])." (<a href=\"".$news['link']."\">".$news['source']."</a>)</TD></TR>";
echo "<TR><TD class=\"small\">".rtrim(nl2br(substr($news['description'],0,115)))."...<br></TD></TR>";
echo "</TABLE><br>";

}

this is what I'm using. I know my code sucks and I know its not perfect. I just want to get an example working then I can work on cleaning it up and securing it. Does anyone know what I'm doing wrong? My query work is I dont include the WHERE (and the date stuff) but if its there I get a blank page. Help![/code]

5kyy8lu3 · September 20, 2009

Yeah, I just figured it would be a very simple task to complete. I'm surprised I'm having some much trouble with it.
$newsDB = $db->query("SELECT * FROM myarticles WHERE date('date') = CURDATE() ORDER BY date DESC LIMIT 20") or die(mysql_error());
while($news = $db->fetch_array($newsDB)) {
echo "<TABLE CELLSPACING=\"0\" CELLPADDING=\"1\" BORDER=\"0\">";

// CHECK TO SEE IF THE TITLE HAS MORE THEN 50 CHARACTERS AND EDIT AS NEEDED
echo "<TR><TD><a class=\"headline\" href=\"?id=".$news['id']."\">";
if(strlen($news['title']) > 45) { echo substr($news['title'],0,45)."..."; } else { echo $news['title']; }
echo "</a><br></TD></TR>";

echo "<TR><TD class=\"small\">".date("l, F jS, Y", $news['date'])." (<a href=\"".$news['link']."\">".$news['source']."</a>)</TD></TR>";
echo "<TR><TD class=\"small\">".rtrim(nl2br(substr($news['description'],0,115)))."...<br></TD></TR>";
echo "</TABLE><br>";

}
this is what I'm using. I know my code sucks and I know its not perfect. I just want to get an example working then I can work on cleaning it up and securing it. Does anyone know what I'm doing wrong? My query work is I dont include the WHERE (and the date stuff) but if its there I get a blank page. Help![/code]

try:

FROM_UNIXTIME(date) = CURDATE()

that's supposed to convert unix timestamp to date, so it should work in theory =)

ozestretch · September 20, 2009

was about to mention SELECT FROM_UNIXTIME... this should work

MadTechie · September 20, 2009

Infact it should be

date(FROM_UNIXTIME(`date`))

if that works then your field type is wrong, change from INT to timestamp

Saying,

timestamps are saved as "1253394080" and not "2009-09-20 01:28:58".

is very confusing, what's the field type timestamp or int ?

did you try my last example ?

Onloac · September 20, 2009

Okay, I changed it. Still getting a blank page. is there any way to get it to report why its blank and not displaying anything?

Onloac · September 20, 2009

AWSOME! Got it working after using date(FROM_UNIXTIME(`date`)). THANKS!!!

MadTechie · September 20, 2009

I highly recommend you correct the field type,

Sign In

[SOLVED] showing only current date results

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information