CODEWITHSUNDEEP
phpmysqldateinsert
Erik
Erik

Reputation: 5791

How do add date and time stamp to an INSERT via PHP with mySQL?

I have an insert script with mySQL. I need to place the date and time when the record is created in the 'add_time" column.

Can anyone show me how to modify my existing script to do this? Do I need a separate PHP script?

I would like the date to appear in standard formt: 09/25/11 6:54 AM

<?
  $host="XXXXXXXXXXX";
  $username="XXXXXXX";
  $password="XXXXXXX";
  $db_name="naturan8_hero";
  $tbl_name="cartons_added";

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

  $order = "INSERT INTO cartons_added (
      type,
      part_no,
      add_type,
      add_qty,
      add_ref,
      add_by,
      add_notes
    ) VALUES (
      '$_POST[type]', 
      '$_POST[part_no]', 
      '$_POST[add_type]', 
      '$_POST[add_qty]', 
      '$_POST[add_ref]', 
      '$_POST[add_by]', 
      '$_POST[add_notes]'
    )";

  $result = mysql_query($order);

  if ($result) {
    $part_no = $_REQUEST['part_no'] ;
    $add_qty = $_REQUEST['add_qty'];
    header("location: inv_fc_add_success.php?part_no=" . urlencode($part_no) . "&add_qty=" . urlencode($add_qty));
  }
  else {
    header("location: inv_fc_add_fail.php");
  }
?>

Upvotes: 1

Views: 3465

Answers (4)

Gordon
Gordon

Reputation: 316969

You do not need to insert data to that column from PHP at all:

TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp).

So change the add_time column definition to

add_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

and then it will automatically populate the column for you when you insert a new row

See: MySql Manual on Timestamp Initializations

Upvotes: 3

s.webbandit
s.webbandit

Reputation: 17000

Add 'time' column to your 'cartons_added' table.

And use this as order

$order = "INSERT INTO cartons_added (type, part_no, add_type, add_qty, 
  add_ref, add_by, add_notes, time)

  VALUES
  ('$_POST[type]', 
  '$_POST[part_no]', 
  '$_POST[add_type]', 
  '$_POST[add_qty]', 
  '$_POST[add_ref]', 
  '$_POST[add_by]', 
  '$_POST[add_notes]',
  '".time().")";

Upvotes: 0

MrJ
MrJ

Reputation: 1938

I like Sam's answer, as it is best - as an alternative, however, you could use date("m/d/Y H:i A"); as per your requirements - ie. it will output MM/DD/YYYY HH:MM AM/PM. Though if you use Sam's method, it will require less code, but you'd need to convert it to get you desired format.

Upvotes: -1

Sam
Sam

Reputation: 16455

You got the "add_time" column set up in your database? Is it of DATETIME format?

In that case you may just modify your query like this:

$order = "INSERT INTO cartons_added (type, part_no, add_type, add_qty, 
  add_ref, add_by, add_notes, add_time)

  VALUES
  ('$_POST[type]', 
  '$_POST[part_no]', 
  '$_POST[add_type]', 
  '$_POST[add_qty]', 
  '$_POST[add_ref]', 
  '$_POST[add_by]', 
  '$_POST[add_notes]',
   NOW())";

Though you should be aware that executing queries like this is dangerous as you trust the user to input only nice things! Google "SQL Injection" to find out more about it, mysql_real_escape_string(), too.

Upvotes: 2

Related Questions