[SOLVED] Nearest to Date

[rascle]

Hi

I have a mysql database that has two columns:

name and dayadded. I want to display 5 rows that dayadded is most like todays day.

Eg. todays day is the 11th and i have a database like:

name      dayadded

rhys        9

stephen    6

bob          7

tony        7

ryan        8

rod          9

 

then i want the 5 rows thats date are nearest to todays to be displayed

(that would display  bob,tony,ryan,rod,rhys).

 

How would i do this?

I was using:

 

<?php
$day = date("j");
$connect = mysql_connect("localhost","username","password");  
mysql_select_db(b25rasc_userlogin) or die(mysql_error());  
$data = MYSQL_QUERY("SELECT * FROM `games` WHERE dayadded >= $day LIMIT 5");  

while($rows = mysql_fetch_array($data))
{
echo"<font size=\"4\">";
echo $rows['text'];
echo "</font><br />";        
}

?>

 

But that doesnt display anything.

What should i do???

Thanks

 

[PFMaBiSmAd]

See this thread - http://www.phpfreaks.com/forums/index.php/topic,250326.0.html

 

Look at my posts and the psot by kickstart, substitute your column and value for the references to 'age'

[rascle]

Thanks i had a look at the link but couldnt find the code.

 

[rascle]

Did you mean:

SELECT  Age, abs(Age - $PassedAge) AS AgeDifference
FROM SomeTable
Order By Age Difference
LIMIT 5

[rascle]

Cause i used the above code in this format:

$data = MYSQL_QUERY("SELECT  dayadded, abs(dayadded - $day) AS AgeDifference
FROM games
Order By Age Difference
LIMIT 5
");  

and it didnt display anything

[PFMaBiSmAd]

'AgeDifference' is not the same thing as 'Age Difference'

 

In programming all the small details matter because computers only do exactly what their code tells them to do.

[rascle]

Sorry i didnt see that there but:

<?php
$day = date("j");
$connect = mysql_connect("localhost","b25rasc_b25rasc","rasclewelsh");  
mysql_select_db(b25rasc_userlogin) or die(mysql_error());  
$data = MYSQL_QUERY("SELECT  dayadded, abs(dayadded - $day) AS difference
FROM games Order By difference LIMIT 5");  

while($rows = mysql_fetch_array($data))
{
echo"<font size=\"4\">";
echo $rows['text'];
echo "</font><br />";        
}

?>

Still doesnt work + i dont know what to do?

Thanks

[PFMaBiSmAd]

Define: Still doesnt work? What is it doing?

 

$rows['text'] that is being used in the while() loop does not exist because the text column no longer is being selected in the query. You would need to add text to the list of files columns in the SELECT part of the query.

[rascle]

Oh Ok

At the moment it is showing nothing

How would i list text in the select query??

Thanks

[PFMaBiSmAd]

I've got to ask if you have seen the definition of a SELECT query in the mysql documentation -

 

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [sTRAIGHT_JOIN]

     

[sql_SMALL_RESULT] [sql_BIG_RESULT] [sql_BUFFER_RESULT]
      [sql_CACHE | SQL_NO_CACHE] [sql_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [iNTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]][/quote]

In -

SELECT * FROM ...

and

SELECT dayadded, abs(dayadded - $day) AS difference FROM ...

 

The select_expr following the SELECT keyword are the items (columns) being selected. * means select all columns. A comma separated list means select the items in that list. You would include text in that comma separated list.

[rascle]

I have got it to work now

Thanks

[rascle]

So how would i add it so that it includes the month and the year.

I am currently using

<?php
$day = date("j");
$month = date("m");
$year = date("Y");
$connect = mysql_connect("localhost","b25rasc_b25rasc","rasclewelsh");  
mysql_select_db(b25rasc_userlogin) or die(mysql_error());  
$data = MYSQL_QUERY("SELECT  text,dayadded, abs(dayadded - $day) AS daydifference
FROM games Order By daydifference LIMIT 5");  

while($rows = mysql_fetch_array($data))
{
echo"<font size=\"4\">";
echo $rows['text'];
echo "</font><br />";        
}

?>

 

So how would i make it be to the nearest ,day ,month and year???

Thanks

[PFMaBiSmAd]

Do you have a column with the DATE (YYYY-MM-DD) it was added or is the day, month, and year separate and if so what exact format is each part?

[rascle]

i can change it too YYYY-MM-DD would that be easier than having it seperately?

[PFMaBiSmAd]

If you use a DATE data type, the query would simply be -

 

SELECT  text, dateadded, abs(datediff(dateadded, CURDATE())) AS daydifference
FROM games Order By daydifference LIMIT 5

[rascle]

Thanks so much PFMaBiSmAd