[SOLVED] showing only current date results

[Onloac]

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]

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

 

change joindate to the timestamp field name

[Onloac]

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]

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]

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

[Onloac]

unix timestamp. example would be 1253394080

[MadTechie]

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]

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]

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]

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]

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

[Onloac]

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]

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]

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]

unix timestamp. example would be 1253394080

 

I did ask him this earlier, and he did answer 

[5kyy8lu3]

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]

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]

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]

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]

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]

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

[MadTechie]

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]

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]

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

[MadTechie]

I highly recommend you correct the field type,