append to table instead of overwriting

[matthewst]

I have a php page that inserts data into a database.

mysql_query("insert into job_log VALUES ('','$emp_id','$project','', '$now','$clock_actions','$domain')", $db_link);

 

The problem is I also need the data inserted into a new table with duplicate fields and insted of the data being overwritten, I need the newest data appened to the table.

I have tried update but havent been able to get it to work.

mysql_query("update job_log_time VALUES ('','$emp_id','$project','', '$now','$clock_actions','$domain')", $db_link);

 

examples:

 

currently:

 

table 1

emp_id  time    action

1            8.00  clock in

2            8.00  clock in

 

when the employee performs a new action the data is overwritten

 

table1

emp_id  time    action

1          10.00  break

2          10.03  break

 

************************

what I need is this:

 

table1

emp_id  time    action

1            8.00  clock in

2            8.00  clock in

1            10.00  break

2            10.03  break

[mpharo]

Just do another insert statement, but remove the unique index on the emp_id field if you have one and just make it a normal index...

[matthewst]

I took over the site after my predecessor "left". How do I tell if the field is unique or not.

 

BTW here is the complete insert code:

 

if ($action_submit){

	$now=(mktime()-21600);

	$emp_id = $row_user['employee_id'];

	$domain = GetHostByName($REMOTE_ADDR);

	mysql_query("insert into job_log VALUES ('','$emp_id','$project','', '$now','$clock_actions','$domain')", $db_link);

	mysql_query("insert into job_log_time VALUES ('','$emp_id','$project','', '$now','$clock_actions')", $db_link);

[mpharo]

what database are you using?

you can just try inserting a duplicate entry for all fields and see what happens, but you should have some sort of admin tool that you can use to view you table structure...

[matthewst]

we're using phpmyadmin

my bosses want to keep track of all the employees working habits

 

a day in the life of one of my users:

 

arrive at 8:00 am, log into company site and click "begin work" (literally, that's what they wanted the button labeled)

 

10:00 click "break out"

10:15 click "break in"

 

12:00 click "lunch out"

 

etc. etc.

 

I have a page that will pull the data and display:

userid--clock action

1--------clock in 8:00

1--------break out 10:00

1--------break in 10:15

2--------clock in 8:00

2--------break out 10:03

2--------break in 10:18

 

eventually they want me to build a php page that will automatically add all working time

 

It has to be a new table because one of the big wigs heard "if you go adding fields and databases to existing tables you could corrupt the information" (yes thats exactally how he said it)

 

unfortunately his son, who just started here, knows just enough to check up on me.

[per1os]

What is your table structures?

[mpharo]

phpmyadmin is an awesome tool for SQL management, when you click on the table it brings up the table stucture page, in about the middle of the window on the left hand side it says indexes, from here you can see what fields are indexed on or are set as unique, if you have it set as unique you wont be able to have id's listed more than once, so just change it so it is indexed but not uniquly, with the button that looks like a lightning bolt at the end of that field..now in your inserts you can keep your original insert but just add an additional one to insert the same thing into the different table, unless it is in a different database...I can post the code if you need further assistance..

[matthewst]

I sincerely apologize, I have several projects going at once and I "combined" two of them in my head. I am sorry if I wasted anyones time.

 

thanks mpharo, i can still put that information to use

 

 

[rcorlew]

You would want to use the following query to add (append) data:

 

$con = mysql_connect($HOST, $USER, $PASS);

if (!$con) {

  // Since the entire script depends on connection, die if connection fails

  die("Error connecting to MySQL database!"); }

 

mysql_select_db($NAME, $con);

 

$result = mysql_query("UPDATE table_name  SET column_name = CONCAT(COALESCE(column_name, ''),',$variable') WHERE row_entry='$variable_row_name'",$con);

 

That should get you going in the right direction, if you do not want a delimiter between cell entries, just remove the comma before the $variable and replace it with a space.