CODEWITHSUNDEEP
phpmysqlpdo
xltmt
xltmt

Reputation: 61

PHP PDO Insert into row with missing value

I have the following PDO code that copies specific rows from one table to another table:

$sqlCopyProfileId = $dbh->prepare("INSERT INTO new_table SELECT * FROM original_table WHERE profile_id = :profile_id");
$sqlCopyProfileId->execute(array(':profile_id' => $profileId));

The new_table has an extra column called record_date_created - when this column wasn't present it copied the row successfully. When I introduced record_date_created. It didn't copy the row.

The record_date_created will update using the following code:

$sqlUpdateProfileId = $dbh->prepare("UPDATE new_table SET record_date_deleted = :record_date_deleted WHERE profile_id = :profile_id");
$sqlUpdateProfileId->execute(array(':record_date_deleted'=>$timeStamp, ':profile_id'=>$profileId));

The only solution I have in mind is instead of using SELECT * I select each row one my one. The problem is that I have over 30 columns and from a programming point of view I see it as tedious and improper to maintain especially if I may increase the number of columns in the future.

How can I copy one row to another while having an empty column?

This is what I tried so far

$sqlCopyProfileId = $dbh->prepare("INSERT INTO new_table SELECT * FROM original_table, record_date_created = :record_date_created WHERE profile_id = :profile_id");
$sqlCopyProfileId->execute(array(':record_date_deleted'=>$timeStamp, ':profile_id' => $profileId));

Upvotes: 0

Views: 187

Answers (1)

Ninju
Ninju

Reputation: 2530

Specify the column names in your insert and don't include the 6th column (which would set the 6th column to null for all rows inserted so the column needs to be nullable) add a null/default value to the VALUES of the query to insert into the 6th column Modify the table and remove the 6th column temporarily so you can insert and then add the column back. This would lose any existing values in the 6th column 

$sqlCopyProfileId = $dbh->prepare("INSERT INTO new_table (col1, col2, col3, col4, col5, col6) 
            SELECT (col1, col2, col3, col4, col5, 'anyDefaultValue') 
            from original_table WHERE profile_id = :profile_id");
            $sqlCopyProfileId->execute(array(':profile_id' => $profileId));

Upvotes: 1

Related Questions