Jump to content

date_format


Alkimuz

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!

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:

 

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

?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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