Help with date() function

ovisopa

Hello ppl, I'm back again 'cause I need some help with date function.

I want to make a query which can print only the rows where the date is today date,

ie. if in my table i have in column timestamp these dates :1.11.2002, 2.11.2002, 3.11.2002, 4.11.2002 I want only the row with date :4.11.2002 being printed

What I must write in my php file from where I store the data in db ->

<?
require("db.inc.php"); //connect to the database
if($action=="submit")
{
mysql_query("INSERT INTO stiri (subject,content,timestamp,small) VALUES ('$news_subject','$news_content','WHAT I WRITE HERE?', '$small')");
echo "Stirea a fost inregistrata!<br>\n";
}
?>

And what I must write in my php file from where i read from db ->

$result = mysql_query ("SELECT * FROM stiri WHERE  timestamp = 'WHAT I WRITE HERE????'");

If you understand what I want and you can help me pls give me an reply to my post.
10x alot and
see ya soon!!

RadioGeek

From http://www.mysql.com/doc/en/DATETIME.html:

Column type Display format

TIMESTAMP(14) YYYYMMDDHHMMSS

TIMESTAMP(12) YYMMDDHHMMSS

TIMESTAMP(10) YYMMDDHHMM

TIMESTAMP(8) YYYYMMDD

TIMESTAMP(6) YYMMDD

TIMESTAMP(4) YYMM

TIMESTAMP(2) YY

So, my guess is you need TIMESTAMP(14) [YYYYMMDDHHMMSS]. The php equivalent is:

date("YmdHis");

Hope this helps.

klansman

in fact you have the answer there...

what you need to do is to format the current date correctly so that you can use it in the select query under the WHERE condition:

To get the correctly formatted current date, you use:

$current_date=date("YmdHis");

Check out http://www.php.net/manual/en/function.date.php for details about the date function.

according to http://www.mysql.com/doc/en/DATETIME.html, the $current_date variable should have the current date properly formatted... why not echo it to see if it is ok?

Then the select query sould be something like:

$select="SELECT * FROM stiri WHERE timestamp=".$current_date;
$result = mysql_query ($select);

If all goes well, (why not echoing the $select string also?) you should be getting the correct results by now.

The insert query should be treated in the same way that the select query.

good luck!

[]

ovisopa

10x for help , it was a good tip using $crent_date = date()......

So what I've done is this:

<?
require ("database.inc.php");
$current_date = date("Ymd");
echo $current_date;
$result = mysql_query ("SELECT * FROM stiri WHERE timestamp = '$current_date'");

while ($data = mysql_fetch_array ($result))
{

?>

I used date(Ymd) 'cause I need only the day-month-year.

After this part of code the data from my db is printed using this:

<tr><td><a name="<? echo $data["id"]; ?>"></a><b><? echo $data["subject"]; ?></b></td><td  align="right">[<? echo date("Y.m.d", $data["timestamp"]) ?>]&nbsp;</td></tr>
<tr><td class="buttonin" colspan="2" align="justify"><? echo $data["content"]; ?></td></tr>
<?
}
?>

Everything is ok, I see only the posts which have the "timestamp" equal with the current_date.

What I wanted is to display the "timestamp" for every post, and here is the problem, I've done something wrong.
When I open phpMyadmin and look in timestamp I see 20021104 and this is fine , but the printed data is 1970.08.20. I'm sure here is the problem but I couldn't find it :

<? echo date("Y.m.d", $data["timestamp"]) ?>

I hope you will find where's my mistake, 'cause I really don't understand.

10x anyway for help.

klansman

i see.

the problem lies with the way that mysql stores the date, your timestamp column.

when you do:

date("Y.m.d",_$data["timestamp"])

you are trying to format a date string using the unix-formatted (integer) date. so far so good. the thing is that $data["timestamp"] is not an unix-formatted date: instead it is a "mysql-formatted" timestamp, so the date() cannot be done that way.

There are a couple of ways to do it. The first is to change the select query so that MySQL returns an unix-formatted timestamp instead of the mysql-formatted date string. The second is change your date() call.

Let's call them method 1 and method 2, respectively:

method 1:
The key to this method is to use the mysql function "UNIX_TIMESTAMP()" to change the returned string format.
This

$result_=_mysql_query_("SELECT_*_FROM_stiri_WHERE_timestamp_= '$current_date'");

should be replaced (or updated by) this

$select="SELECT *,UNIX_TIMESTAMP(timestamp) AS unix_timestamp FROM stiri WHERE timestamp='$current_date'";

This change to the select string will alter the $data array, including an additional field called "unix_timestamp". This field is the one that you should use with the date function:

[<?_echo_date("Y.m.d",_$data["unix_timestamp"])_?>]

method 2:
The key to this method is to create an unix-formatted timestamp based on the mysql-formatted date. The mysql-formatted date has the following form: (assuming that you are using a timestamp type column, do not confuse it with the name of the column, which is also timestamp)
YYYYMMDDHHMMSS so, with a simple string manipulation you can get every field into a single variable:

$year=substr($data["timestamp"],0,4);
$month=substr($data["timestamp"],4,2);
$day=substr($data["timestamp"],6,2);
$hour=substr($data["timestamp"],8,2);
$minute=substr($data["timestamp"],10,2);
$second=substr($data["timestamp"],12,2);

and then, with mktime, you build your unix-formatted timestamp:

$unix_timestamp=mktime($hour,$minute,$second,$month,$day,$year);

finally, you use that timestamp in the date() function:

[<?_echo_date("Y.m.d",_$unix_timestamp)_?>]

or, you can compress things a little...

[<? echo date("Y.m.d",mktime(substr($data["timestamp"],8,2),substr($data["timestamp"],10,2),substr($data["timestamp"],12,2),substr($data["timestamp"],4,2),substr($data["timestamp"],6,2),substr($data["timestamp"],0,4))) ?>]

end of all methods... maybe there are easier ways to do this, but that's the way i do it.

[]