date question

[shadiadiph]

i have a date column in my database the date when originally inputed is about a month ahead of the date inserted how can i use the where date < 7????  to make it display things that expire in the next 7 days. sorry not sure how to do this? = now() < 7 ???

[ngreenwood6]

I would do it like this:

 

$time = time(); //sets the current time
$seven_days = 60*60*24*7; // makes it seven days
$total = $time - $seven_days // subtracts seven days from current time

//make the query
$query = "SELECT * FROM table WHERE time > '$total'"; //selects any time that is greater than seven days before the time

 

This has not been tested but I am pretty sure it will work. There are other methods but it is late and my brain has almost shutdown lol.

[xtopolis]

... WHERE dateColumn BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)

 

What about that?

[shadiadiph]

thanks guys but i have another question about date in my database it always saves as 2009-01-15 when i call this from the database it displays this too how can i get it to display 15-01-2009 ??? when i call it from the database i usually use $date= $row["date"];

[xtopolis]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

[shadiadiph]

THANKS PERFECT

 

I HAVE JUST BEEN LOOKING FOR THE ANSWER TO THIS QUESTION ON THERE BUT CANT FIND IT.

 

HOW DO YOU GET A NUMBER STORED AS 1000000 to display 1,000,000 or 1,000,000.00 I TRIED PLAYING WITH FLOATS BUT IT DIDN'T WORK EASY TO GET 999.00 but any thing over that doesn't seem to work.??

[xtopolis]

Probably can use this function: http://us.php.net/manual/en/function.number-format.php

Look at the examples

[shadiadiph]

sorry can't get the date thingy towork with my script i think this is more of a php question so I am going to post a question there thanks for the input guys

[ngreenwood6]

you can use the date function:

 

$date = date("m/d/Y", $row['date']) // would output 01/15/2009

 

check out this article http://www.tizag.com/phpT/phpdate.php

[shadiadiph]

mm thanks hows about when the user is inputting it on a form as  01-15-2009 how to insert is to the database as

 

$date	      = ($_POST["date"]);


$insertsql ="insert into tbltradedetails ($date) values ('$date')"; 

[/code

doesn't work

[ngreenwood6]

Personally I store all my times/dates as a timestamp in the int type. I would look into the strtotime() function.

[shadiadiph]

saving as integers means you can still query dates?

 

for example if o wanted to display in sql all the dates with less than 7 days until that date could it still be done saved not as a DATE

[shlumph]

I thought this thread was going to be about a girl 

[ngreenwood6]

lol that would have been more interetsting.

 

shadiadiaiph I will give you an example. Say I had a table like this:

 

table - entries

field - id //int auto_increment primary key

field - date_entered //int

field - text_entered //varchar

 

I would then enter something in the database like so.

 

$time_now = time(); //makes a unix timestamp
$query = "INSERT INTO entries (date_entered,text_entered) VALUES ('$date','somevalue here')"; //inserts the timestamp in the date_entered field

 

Then when I wanted to display the time from the database I would show it like this:

 

$query = "SELECT * FROM entries";
$results = mysql_query($query);

while($row = mysql_fetch_array($results))
{
$date_entered = $row['date_entered']; //sets the date as a value
$formatted_date = date("m/d/Y", $date_entered); //formats the timestamp into 01/15/2009
echo $formatted_date; //displays the date

 

Check out this site for the date() function http://www.tizag.com/phpT/phpdate.php. You should also look into the mktime() and strtotime() functions.