Struggling little with MySQL insertion

Question

Hi I am trying a php code to insert record into a table.

<?php
$server = "localhost";
$user = "root";
$pwd = "";
$db_con = mysql_connect($server,$user,$pwd);
$select_db = mysql_select_db("test_database",$db_con);
$txnid = date ("ydmHis") . mt_rand(1000, 9999);
$custnumber = "4316010100000001";
$cust_mo_num = "7875432990";
$bc_id = "LTH001";
$timestamp = "12:12:12:12:12:12";
$amounta = "500";
$txnupdate = "INSERT INTO cust_txn (txnid,cust_num,cust_mo_num,bc_username,txn_time,txn_amount,txn_type,txn_status) VALUES ('$txnid','$custnumber','$cust_mo_num','$bc_id','$timestamp','$amounta','WTH','SUCC')";
$result = mysql_query($txnupdate);
mysql_close($db_con);
echo $result;
?>

When I run this page and refresh it, the record is inserted only once. (The txnid is primary key and I do change it every time.) I have no way to know where am I going wrong.

Answer 1

$txnid sounds like timestamp. Mysql can insert it automatically. Set it as

ALTER TABLE CUST_TXN CHANGE txnid txnid TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP.

You will not longer set timestamp in code.

Answer 2

Your code is working fine. I just capitalize table name since i'm using ubunthu box. (Your create table statement provided with CUST_TXN table name)

SOURCE

<?php
$user = "root";
$pwd = "root123";
$db_con = mysql_connect($server,$user,$pwd);
$select_db = mysql_select_db("ci",$db_con);
$txnid = date ("ydmHis") . mt_rand(1000, 9999);
$custnumber = "4316010100000001";
$cust_mo_num = "7875432990";
$bc_id = "LTH001";
$timestamp = "12:12:12:12:12:12";
$amounta = "500";
$txnupdate = "INSERT INTO CUST_TXN (txnid,cust_num,cust_mo_num,bc_username,txn_time,txn_amount,txn_type,txn_status) VALUES ('$txnid','$custnumber','$cust_mo_num','$bc_id','$timestamp','$amounta','WTH','SUCC')";
echo $txnupdate;
$result = mysql_query($txnupdate);
mysql_close($db_con);
echo $result;
?>

RESULTS

> select * from CUST_TXN;
+------------------+------------------+-------------+-------------+-------------------+------------+----------+------------+
| txnid            | cust_num         | cust_mo_num | bc_username | txn_time          | txn_amount | txn_type | txn_status |
+------------------+------------------+-------------+-------------+-------------------+------------+----------+------------+
| 1209042028162311 | 4316010100000001 | 7875432990  | LTH001      | 12:12:12:12:12:12 | 500        | WTH      | SUCC       |
| 1209042028177407 | 4316010100000001 | 7875432990  | LTH001      | 12:12:12:12:12:12 | 500        | WTH      | SUCC       |
| 1209042028194204 | 4316010100000001 | 7875432990  | LTH001      | 12:12:12:12:12:12 | 500        | WTH      | SUCC       |
| 1209042028342444 | 4316010100000001 | 7875432990  | LTH001      | 12:12:12:12:12:12 | 500        | WTH      | SUCC       |
| 1209042028358383 | 4316010100000001 | 7875432990  | LTH001      | 12:12:12:12:12:12 | 500        | WTH      | SUCC       |
| 1209042028362068 | 4316010100000001 | 7875432990  | LTH001      | 12:12:12:12:12:12 | 500        | WTH      | SUCC       |
+------------------+------------------+-------------+-------------+-------------------+------------+----------+------------+

Also you can use current_timestamp for your txn_time column

MYSQL DDL

CREATE TABLE CUST_TXN_2 ( txnid char(16), PRIMARY KEY(txnid), cust_num char(16), cust_mo_num char(10), bc_username char(6), txn_time timestamp DEFAULT CURRENT_TIMESTAMP, txn_amount varchar(10), txn_type varchar(5), txn_status varchar(10) );

SOURCE

<?php
$user = "root";
$pwd = "root123";
$db_con = mysql_connect($server,$user,$pwd);
$select_db = mysql_select_db("ci",$db_con);
$txnid = date ("ydmHis") . mt_rand(1000, 9999);
$custnumber = "4316010100000001";
$cust_mo_num = "7875432990";
$bc_id = "LTH001";
$amounta = "500";
$txnupdate = "INSERT INTO CUST_TXN_2 (txnid,cust_num,cust_mo_num,bc_username,txn_amount,txn_type,txn_status) VALUES ('$txnid','$custnumber','$cust_mo_num','$bc_id','$amounta','WTH','SUCC')";
echo $txnupdate;
$result = mysql_query($txnupdate);
mysql_close($db_con);
echo $result;
?>

RESULTS

> select * from CUST_TXN_2;
+------------------+------------------+-------------+-------------+---------------------+------------+----------+------------+
| txnid            | cust_num         | cust_mo_num | bc_username | txn_time            | txn_amount | txn_type | txn_status |
+------------------+------------------+-------------+-------------+---------------------+------------+----------+------------+
| 1209042055524683 | 4316010100000001 | 7875432990  | LTH001      | 2012-04-09 20:55:52 | 500        | WTH      | SUCC       |
| 1209042055563581 | 4316010100000001 | 7875432990  | LTH001      | 2012-04-09 20:55:56 | 500        | WTH      | SUCC       |
| 1209042055564435 | 4316010100000001 | 7875432990  | LTH001      | 2012-04-09 20:55:56 | 500        | WTH      | SUCC       |
| 1209042055579931 | 4316010100000001 | 7875432990  | LTH001      | 2012-04-09 20:55:57 | 500        | WTH      | SUCC       |
+------------------+------------------+-------------+-------------+---------------------+------------+----------+------------+