loop to find the date in MySQL

Question

I am new to php and mysql programing and I have a question in working with the date.

There is a date field that stores the date in the table

eg 2013-05-27, 2013-05-24, 2013-05-22

I need to retrieve the latest 3 days data for further processing, first is locate today's date, if cannot find, then will locate yesterday's data, if cannot find, then locate the date before yesterday's data etc. Then I have the below code:

$date1=date('Y-m-d');
$date1_1="";
$datefound="";
while( $datefound <> ""){
    $date1_1_1 = mysql_query("SELECT * FROM jom_c1 where date ='" .$date1. "'");
    if($date1_1_1){
    $datefound = $date1;
}else{
    $date1 = date('Y-m-d', strtotime($date . ' - 1 day'));
}
}
echo $datefound; 
?>

however, it seems like the loop is not running, much appreciated if someone can give me a hints with it.

podiluska · Accepted Answer · 2013-05-26 18:34:42Z

5

You may want to consider using SQL properly, ie

select * from jom_c1 where date <= curdate() order by date desc limit 3

rather than looping.

(Your loop doesn't run much, because as soon as date is found, it stops)

edited May 26, 2013 at 18:34

answered May 26, 2013 at 16:40

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

podiluska Over a year ago

@Mifeet Possibly not. I've added an explicit sort

Michael Roy · Accepted Answer · 2013-05-26 16:48:54Z

0

The reason that the while block is not executing is that you set $datefound="" but your condition is while ($datefound<>"").

answered May 26, 2013 at 16:48

Michael Roy

3491 gold badge3 silver badges7 bronze badges

Comments

Mifeet · Accepted Answer · 2013-05-26 17:23:42Z

0

If you're looking for the most current date that has a record in jom_c1, then try the following query:

SELECT * FROM jom_c1 WHERE date <= CURDATE() ORDER BY date DESC LIMIT 1

If you're looking for the latest three dates, then use LIMIT 3 instead of LIMIT 1.

The PHP code would look like this:

$datefound = "";
$result = mysql_query('SELECT * FROM jom_c1 WHERE date <= CURDATE() ORDER BY date DESC LIMIT 1');
if ($result && mysql_num_rows($result) > 0) {
    $row = mysql_fetch_assoc($result);
    $datefound = $row['date'];
}
echo $datefound;

Note that mysql_query is deprecated, however.

Edit: If there are multiple records for one day, then use the following query:

SELECT DISTINCT date FROM jom_c1 WHERE date <= CURDATE() ORDER BY date DESC LIMIT 1

Distinct will leave out duplicate records.

edited May 26, 2013 at 17:23

answered May 26, 2013 at 16:51

Mifeet

13.8k6 gold badges62 silver badges110 bronze badges

2 Comments

Calvin Tse Over a year ago

thank you very much for your help, i have tried. But one thing, we have 16 records in one day, can i group to just store 1 record in the array with the same "date" ?

Calvin Tse Over a year ago

thank you very much again, i have tried it but with LIMIT 3, then it only show 2 records Array ( [date] => 2013-05-22 ) Array ( [date] => 2013-05-21 ) it should have a 2013-05-23. have i done something wrong ?

Collectives™ on Stack Overflow

loop to find the date in MySQL

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related