Jump to content

Archived

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

Alkimuz

date_format

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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'

Share this post


Link to post
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..

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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'

Share this post


Link to post
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:

 

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

?>

Share this post


Link to post
Share on other sites

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