1

PHP version 5.3.3, mysql 5.0.95

Need to migrate data from an existing table to two identical tables. Data from original needs parsing before insert into the two new tables. (That code not shown as I'm hoping to isolate this problem.) Wanted to use transaction to insure new tables are identical.

task_id field is autoincrement in test_timecard and is unsigned mediumint in test_timecar_2.

Engine is InnoDB for both tables.

Separate queries works:

$timecard_data_results = array();
$fill_old_data_array_def = " SELECT task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment FROM timecard WHERE company_id = '" . $company_request . "' AND employee_id = '" . $employee_request . "' AND DATE(task_start_time) < '" . $new_text_format_date . "' AND (DATE(task_end_time) > '2014-12-31' OR DATE(task_end_time) = '2000-01-01') ORDER BY task_start_time";
$timecard_data_results = mysqli_query($conn, $fill_old_data_array_def);

while($timecard_record = mysqli_fetch_assoc($timecard_data_results)) {

    $company_id = $timecard_record['company_id'];
    $employee_id = $timecard_record['employee_id'];
    $location = $timecard_record['location'];
    $task_name = $timecard_record['task_name'];
    $task_start_time = $timecard_record['task_start_time'];
    $task_end_time = $timecard_record['task_end_time'];
    $tccomment = $timecard_record['tccomment'];

    $troubleshoot_def = "INSERT INTO test_timecard (company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES ('" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')"; 
    $troubleshoot_2_def = "INSERT INTO test_timecard_2 (task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES (LAST_INSERT_ID(), '" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')";     
    $troubleshoot = mysqli_query ($conn, $troubleshoot_def);
    $troubleshoot_2 = mysqli_query ($conn, $troubleshoot_2_def);
}

transaction with mysqli_multi_query inserts one row only to both tables. No errors reported.

$timecard_data_results = array();
$fill_old_data_array_def = " SELECT task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment FROM timecard WHERE company_id = '" . $company_request . "' AND employee_id = '" . $employee_request . "' AND DATE(task_start_time) < '" . $new_text_format_date . "' AND (DATE(task_end_time) > '2014-12-31' OR DATE(task_end_time) = '2000-01-01') ORDER BY task_start_time";
$timecard_data_results = mysqli_query($conn, $fill_old_data_array_def);

while($timecard_record = mysqli_fetch_assoc($timecard_data_results)) {

    $company_id = $timecard_record['company_id'];
    $employee_id = $timecard_record['employee_id'];
    $location = $timecard_record['location'];
    $task_name = $timecard_record['task_name'];
    $task_start_time = $timecard_record['task_start_time'];
    $task_end_time = $timecard_record['task_end_time'];
    $tccomment = $timecard_record['tccomment'];

    $troubleshoot_def = "START TRANSACTION; INSERT INTO test_timecard (company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES ('" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "'); INSERT INTO test_timecard_2 (task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES (LAST_INSERT_ID(), '" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "'); COMMIT;"; 
    $troubleshoot = mysqli_multi_query ($conn, $troubleshoot_def);
}

Stumped.

Improve this question
1
  • If you're going to be using mysqli_multi_query(), then it should only be called once (outside the loop), when all the INSERT statements are concatenated. This also means that START TRANSACTION has to be moved out before the loop and COMMIT after the loop, although they aren't really needed. Echo the query string to ascertain that all the desired statements are populated. Check the following related question too: stackoverflow.com/q/20143592/2298301 Commented Jan 3, 2017 at 15:52

1 Answer 1

Reset to default
0 
$troubleshoot_def = "INSERT INTO test_timecard (company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES ('" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')"; 
    $troubleshoot_2_def = "INSERT INTO test_timecard_2 (task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES (LAST_INSERT_ID(), '" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')";

There are lot's of problems here. First is that it does not make any sense at all to insert nearly identical data into two different tables. In fact when the operation completes you have three tables with nearly identical data namely test_timecard_2, test_timecard and timecard

Secondly you are inserting unescaped data. Since data comes from another of your tables there isn't much chance of an sql injection but there is still a likelyhood that the queries will fail. Specifically I am talking about code like this:

VALUES ('" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')";

Thirdly, you almost never need to do SELECT - LOOP - INSERT because mysql has a built in INSERT SELECT command.

INSERT INTO test_timecard (company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) 
SELECT * FROM time_card

take care to get the columns right (the above is just a copy paste from two sections of your code)

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

9 Comments

Apologies for misleading. The daughter tables will actually have a few additional fields that do not exist in the parent table, thus the while loop to parse the original data. When I ran into the transaction problem, I tried to isolate that snafu without the distraction of other code in my question. Creating the new tables is a one-time procedure, entirely in-house, so little concern over injection.
a few additional fields isn't good enough reason to create those extra tables. This is in violation of all the database normalization principals.
Leaving th matter of extra tables aside, the right way to do this is with a single INSERT SELECT statement. instead of what could turn out to be thousands or millions of inserts depending on the sizes of your tables
Thanks for your concern re. database design. I need to condition the original data in a long series of if statements before inserting into a new table. At the same time, it's necessary to archive a copy of that original data in a second table that will never be used for queries, etc. The new tables diverge from the original in many ways, but I thought it was clearest to eliminate such from the code presented in the original post, so the focus would be on use of the transaction inside the while loop. It appears from Dhruv Saxena's comment that I'll need to create another php array for insert.
Unfortunately that turned out to be quite a red herring.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.