date_format

[Alkimuz]

hi,

 

would like to select stuff on a specific month number and/or year where their date is stored as a DATE (0000-00-00), so i came up with this:

 

$dateresult = mysql_query("SELECT *, DATE_FORMAT(date, '%m') as month, DATE_FORMAT(date, '%Y') as year FROM table WHERE month = '10' AND year = '2011' ") 
or die ("could not execute dateresult!");

 

but it seams not to work.. is it wrong to select on colums that are newly defined? if so, what is the right method?

 

thanks!

[requinix]

You are correct: you can't reference aliases in a WHERE clause.

 

You can compare dates as strings. Try

SELECT * FROM table WHERE date BETWEEN '2011-10-00' AND '2011-10-99'

[Alkimuz]

ok, thanks!

 

i am one stap further,

 

but what if you dont know the date on forehand? for example, i take a random item and i want to select all items of the same month as this item, or all items of the previous month of this item?

 

first i thought i could subtract the month number of the random item and select all items with the same month number or month number -1, but that would not be possible, like you say..

[requinix]

It's possible, but wordier than you might like.

 

The simplest solution would be to get the date from that random item in one query then select the other items in a second query...

[Alkimuz]

perfect, but can you explain me how to do that?

[requinix]

One query gets the random item. Best way for this is to count the number of items and pull a random one out. So it's actually two queries.

SELECT COUNT(1) FROM table # say you put this number in $count. subtract one from it and
SELECT * FROM table LIMIT $count, 1

Then you have the item details, including the date. Use the normal query with that information:

SELECT * FROM table WHERE date BETWEEN 'year-month-00' AND 'year-month-99'

[Alkimuz]

thanks for your help in the mean time, i also found an other solution, i didnt know the SQL-formulas MONTH() and YEAR() before ^^,

 

probably there is still a lot faster way, but for now it works:

 

<?php

$datumresult = mysql_query("SELECT *, DATE_FORMAT(datum, '%m') as maand, DATE_FORMAT(datum, '%Y') as jaar FROM strip WHERE vlag = '0' AND datum = '$ID' LIMIT 1") 
or die ("could not execute paginaresult!");

$datumrow = mysql_fetch_array($datumresult);

$m = $datumrow['maand'];
$jaar = $datumrow['jaar']; 

if ($m <2){$vorigem = 12; $vorigej = $jaar - 1;} else {$vorigem = $m - 1; $vorigej = $jaar;}

$vorigeresult = mysql_query("SELECT * FROM strip WHERE vlag = '0' AND MONTH(datum) = '$vorigem' AND YEAR(datum) = '$vorigej' ORDER BY ID DESC ") 
or die ("could not execute vorigeresult!");
$vorigerow = mysql_fetch_array($vorigeresult);

?>

[The Little Guy]

SELECT * FROM table WHERE date_format(date, '%Y-%m') = '2011-10';

[Alkimuz]

wow, just move the date_format to the WHERE-clause!! thanks!!!