Jump to content



Recommended Posts



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?



Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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'

Link to comment
Share on other sites

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:



$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);


Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.