[RESOLVED] SQL Query for date

[NZ_Kiwis]

I have a SQL table with a list of dates

1/ 2006-15-04
2/ 2006-09-05
3/ 2006-15-05
4/ 2006-24-05

I have a code to see that the date today is 2006-07-05 how i write a code to select the closest date to today... ie: row 2

[bradgrafelman]

By "closest date to today", do you mean whichever date (either in the future OR in the past) is closest? If so, MySQL's DATEDIFF() is probably what you're looking for...

EDIT: Hrm... perhaps not... still working on this one...

EDIT2: D'oh! I'm stupid! I'm trying to think of all of these mathematical ways to figure this out and I forget about absolute value...

SELECT `date`, ABS( DATEDIFF(`date`, NOW()) ) AS `difference` FROM `myTable` ORDER BY `difference` ASC

[NZ_Kiwis]

$result48 = mysql_query ("SELECT * DATEDIFF(`group_date_new`, NOW()) AS `difference` ORDER BY `difference` ASC");
$row48 = mysql_fetch_array($result48);

echo $row48[group_desc];

so the date is group_date_new, why does this not work???

[bradgrafelman]

Couple of things...

1. I updated my query above. Added ABS() to fix handling of dates in the past.

2. You need to separate fields in SELECT statements with commas... so put a comma between the * and the ABS(DATEDIFF...) stuff.

EDIT: Also, this:

echo $row48[group_desc];

is not a good coding practice. Associative arrays have strings for indeces. In your code, 'group_desc' is not being specified as a string, but as a constant, when it needs to be a string. To solve this, use quotes when referencing array indeces, such as...

echo $row48['group_desc'];

[NZ_Kiwis]

$result48 = mysql_query ("SELECT `group_date_new`,  ABS( DATEDIFF(`group_date_new`, NOW()) ) AS `difference` FROM `results_group` where comp_run_id = $comp_run_id ORDER BY `difference` ASC");
$row48 = mysql_fetch_array($result48);

echo $row48['group_desc'];

This is now my code but it does not show anything... no errors either????

[bradgrafelman]

So let's add some debug info...

$result48 = mysql_query ("SELECT `group_date_new`,  ABS( DATEDIFF(`group_date_new`, NOW()) ) AS `difference` FROM `results_group` where comp_run_id = $comp_run_id ORDER BY `difference` ASC") or die('Error - MySQL said: ' . mysql_error());
echo 'Number of rows returned: ' . mysql_num_rows($result48);
$row48 = mysql_fetch_array($result48); 

echo $row48['group_desc'];

[NZ_Kiwis]

Number of rows returned: 18

What the hell! should only be one! This is my sql. I'm looking for the closest to the group_date_new only. there is different comps but i only want the one comp!

CREATE TABLE results_group (
group_id int(10) NOT NULL auto_increment,
comp_run_id int(10) NOT NULL default '0',
group_desc varchar(200) NOT NULL default '',
group_date_new date NOT NULL default '0000-00-00',
group_date_end_new date NOT NULL default '0000-00-00',
number_game int(10) NOT NULL default '0',
bye_exists int(2) NOT NULL default '0',
bye_number int(2) NOT NULL default '0',
regular int(2) NOT NULL default '0',
day_date int(2) NOT NULL default '0',
grouporder int(10) NOT NULL default '0',
KEY group_id (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=450 ;

[bradgrafelman]

What do you mean 'should only be one' ? You mean there is only one row in your table that matches the criteria? Or you only want the data for the first row returned? What MySQL is saying is that 18 rows match the SELECT criteria, though that doesn't mean you have to retrieve all 18.

So, now that we know you're actually getting rows in your result set, let's look at what all it returned... instead of your echo() statement, try this:

print_r($row48);

[NZ_Kiwis]

i only want the closest.. there should only be one record that is closest... the other 17 should not be

i get this

Number of rows returned: 18Array ( [0] => 2006-05-06 [group_date_new] => 2006-05-06 [1] => 1 [difference] => 1 )

[bradgrafelman]

Ah, I'm so stupid.

Your problem is you're trying to retrieve a value from the database (group_desc) but you didn't include it in your SELECT statement. Add 'group_desc' to your list of fields in the SELECT statement and go back to your echo() statement you were using earlier. Should work just fine.

In case it provides any memory/disk read improvements, you can add 'LIMIT 1' on to the end of your query since you only want one row.

[NZ_Kiwis]

works great thanks!!