Auto insert current date to MySQL

[Strimer]

I just want to add some things to DB and one of it is current date which I want to be added automaticly according to the current daate.  I am using something like below but it is not working

 

 

<?php

//something unimportant

$c_date = date ("Y-m-d", $phptime);
$name = $_POST["name"];
$value = $_POST["value"];


//something unimportant
    
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");    

$result = mysql_query ("INSERT INTO $tbl_name (DATE, NAME, VALUE) VALUES ($c_date, $name', '$value')");
}

mysql_close();
        
if ($result) {
    echo "Zaznam ".$c_date." - ".$name." - ".$value." vložený!!!";
    echo "<BR>";    
} else {
    echo "ERROR";
}

?>

 

 

Thank yo

[davidannis]

You can set the date with just $c_date = date ("Y-m-d");

I have no idea what is in the second variable in the date function but you don't need it.

You should also backquote the field names. I think DATE may be reserved.

Edited March 16, 2013 by davidannis

[jcbones]

The second argument to the date function is a UNIX timestamp.  So that date returns that time, rather than the current.

There is a couple of options you can run with.

 

1. You can specify the date like you are doing.

2. You can store a UTC date in MySQL using the timestamp column type, and specify it to default to CURRENT_TIMESTAMP, you will not have to send anything, and it will add the timestamp.

 

Like Davidannis says, date is a reserved keyword in MySQL, so you will need to enclose it in backticks(`).

 

Edit: UNIX on the brain...

Edited March 16, 2013 by jcbones

[jazzman1]

@OP, there is a syntax error (maybe two or more) in your sql query.

 

Instead using a date function in php, in that particular example you could use date-time functions in mysql.

 

Also, why did you close mysql connection and then make a test for the correct result? 

 

Well, you need to obtain basic knowledge before to start using the script even like this.

[Strimer]

Thank you for your answers...

 

I have tried

 

$result = mysql_query ("INSERT INTO $tbl_name (`DATE`, NAME, VALUE) VALUES ('$c_date', $name', '$value')");

 

but it is not working. When I try to enter NAME or VALUE, everything is OK, but the date causing th eproblem..

 

thank you for help

[jcbones]

You need to find the error:  mysql_error.

[davidannis]

My guess is that the content of $c_date  is mangled because I don't know where or how $phptime was set. I now understand that

 

The second argument to the date function is a UNIX timestamp.  So that date returns that time, rather than the current.

but since the OP said

I want to be added automaticly according to the current daate.

it is not really needed.

 

That said, I think that the advice to find the mysql error is a good.

[josephbupe]

Hi,

 

I also want to ask along the same line. How can I automatically insert ONLY the current year (2013) into mysql database?

 

joseph

[davidannis]

joseph

$c_date = date ("Y");
$query="INSERT into mydatabase ('myfieldname') VALUES ('$c_date')";

then execute the query

Edited March 18, 2013 by davidannis

[Jessica]

https://www.google.com/search?q=mysql+automatic+date

 

Please don't use PHP to set the date.

[davidannis]

Jessica, If I understand correctly, you are saying create a table with a column called myfieldname, YEAR(4) and then

INSERT into `mytablename` (myfieldname) VALUES (NOW())

What difference does it make? Is it a security issue or an efficiency issue or something else entirely?

[Jessica]

Are you really only trying to store the YEAR?

[davidannis]

Joseph's original question was

I also want to ask along the same line. How can I automatically insert ONLY the current year (2013) into mysql database?

so I assume that he is. I have no idea why.

Edited March 18, 2013 by davidannis

[Jessica]

Then sure. (I missed the part where there was a new person posting.)

[davidannis]

FWIW, in an effort to avoid all possible productive work I looked at how long it took to set the year in a database using 3 methods, php outside of the loop, php every time the data was written and using the db's native function. The native function is a tiny bit more efficient unless you are going to make multiple writes tot he DB (you can't pull the native MySQL function out of the loop).

 

The code:

<?php
require_once 'config.inc.php';
require_once (NON_WEB_DIR.'setup.php');;
$time = -microtime(true);

for ($i=0; $i < 4000; ++$i) {
    $c=date('Y');
    $query = "INSERT into test (`id`,`year`) VALUES ('','$c')";
    $result= mysqli_query($link, $query);
}
$time += microtime(true);
echo "time: ",sprintf('%f', $time),PHP_EOL;
$c=date('Y');
echo '<br />time set outside of loop';
$time = -microtime(true);
for ($i=0; $i < 4000; ++$i) {
    $query = "INSERT into test (`id`,`year`) VALUES ('','$c')";
    $result= mysqli_query($link, $query);
}
$time += microtime(true);
echo "time: ",sprintf('%f', $time),PHP_EOL;
echo "<br />time set by DB";
$time = -microtime(true);
for ($i=0; $i < 4000; ++$i) {
    $query = "INSERT into test (`id`,`year`) VALUES ('',NOW())";
    $result= mysqli_query($link, $query);
}
$time += microtime(true);
echo "time: ",sprintf('%f', $time),PHP_EOL;
?>

the results:

time: 0.272242
time set outside of looptime: 0.242826
time set by DBtime: 0.268353

[Strimer]

Thank you for help, for me the following worked

 

$result = mysql_query ("INSERT INTO $tbl_name (DATE, NAME, VALUE) VALUES (NOW(), '$name', $value)");

 

Even with DATE or DATETIME (i belive that it works with every mysql date format).