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 co开发者_开发知识库nnect");
  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");
  }
?>

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

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.

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().")";

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.