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

Question

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");
  }
?>

I suggest you read unixwiz.net/techtips/sql-injection.html

Robus
– Robus

2011-09-25 13:53:43 +00:00
Commented Sep 25, 2011 at 13:53 — Robus
– Robus, Commented Sep 25, 2011 at 13:53
agreeing with @Robus, also take a look at xkcd.com/327

damianb
– damianb

2011-09-25 14:34:08 +00:00
Commented Sep 25, 2011 at 14:34 — damianb
– damianb, Commented Sep 25, 2011 at 14:34

Gordon · Accepted Answer · 2013-07-22 09:08:42Z

3

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

edited Jul 22, 2013 at 9:08

answered Sep 25, 2011 at 13:56

Gordon

318k76 gold badges548 silver badges566 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam · Accepted Answer · 2011-09-25 13:55:17Z

2

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.

answered Sep 25, 2011 at 13:55

Sam

16.4k6 gold badges59 silver badges89 bronze badges

Comments

s.webbandit · Accepted Answer · 2011-09-25 14:27:08Z

0

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

edited Sep 25, 2011 at 14:27

answered Sep 25, 2011 at 14:04

s.webbandit

17.2k17 gold badges60 silver badges83 bronze badges

Comments

MrJ · Accepted Answer · 2011-09-25 14:04:26Z

-1

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.

answered Sep 25, 2011 at 14:04

MrJ

1,9382 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related