complicated date to day

nagasea

i got the order date from the database

ID SS_YEAR SS_DATE SS_MONTH
1 2003 4 10
2 2003 5 10
3 2003 7 10
4 2003 10 10

And the company do tuesday and thursday shipping only each week.
how to pull that ID following that condition and situation.
Because each week tueday/thursday is fall on different date so it makes the task more complicated.

LordShryku

Is there a lot of records? You may be better off pulling everything, concat'ing the date together, and using date("l") to figure out the day of the week.

tekky

I would store the date in the database as either a DATE field or an int with a unixtimestamp in it first... then you can easily use php to check if its a tuesday or not....

something like

$result = mysql_query("SELECT * FROM orders ORDER BY datefield");
//this requires datefield to be an int type holding a unix timestamp
while($row = mysql_fetch_assoc($result))
{
     $ts = $row['datefield'];
     if (date('w', $ts) == 2 || date('w', $ts) == 4)
     {
          //shipping date! do this!
     }
     {
          //not a shipping day, customers can wait another day!
     }
}

otherwise you can use mysql functions...

SELECT * FROM orders WHERE DAYOFWEEK(datefield) == 3 OR DAYOFWEEK(datefield) == 5
//this requires datefield to be of mysql DATE type

nagasea

why unixtimestamp is better ?
can someone tell me a little bit about this

LordShryku

Either date or timestamp is better because you can perform calculations on it. Saves you from having to pull the whole table and have php do the work because you can do all the work in your SQL statement

tekky

Originally posted by nagasea
why unixtimestamp is better ?
can someone tell me a little bit about this

to elaborate on the previous answer....

with a unixtimestamp.... 12/23/03 < 12/25/03

you can substract the timestamps to get the timebetween (in seconds) with timestamps you can then use php's date() function to get a plethora of information from those timestamps....

with sql DATE type still 12/23/03 < 12/25/03
you use mysql functions to get not AS much but still quite abit of data from the date (DAYOFWEEK() and other functions in mysql)

you could even do SELECT * FROM table WHERE datefield IS BETWEEN 1/1/03 AND 2/28/03

with your method... using month/day/year columns you would have to compare THREE columns to do that.... (thats alot of SQL statement to write....)

hope this helps explain some 😃