0

I'm not sure what I'm doing wrong. So I have 2 tables users and user_phone. Each user has a phone_number1 and phone_number2. The phone number are currently in the user_phone table. Now i added the phone_number1 and phone_number2 columns in the users table. So I want to move all the phone numbers from user_phone to users table for each user. But my logic below since not working. throwing.

DoctrineMigrations\Version20220222224248 failed during Execution. Error: "An exception occurred while executing 'UPDATE usersSETphone_number1= '666-666-6666,phone_number2= 999-999-9999 WHEREid = 12':

And here the code I'm currently running.

public function up(Schema $schema): void
{
    // this up() migration is auto-generated, please modify it to your needs
    if($schema->getTable('users')->hasColumn('phone_number1')) {

        $phone_numbers   = $this->connection->fetchAll("SELECT * FROM user_phone");
        $users           = $this->connection->iterateColumn("SELECT id FROM users");

        
        foreach($phone_numbers as $number) {
            $phone1 = $number['phone_number1'];
            $phone2 = $number['phone_number2'];
            $phone_id = $number['id'];
            foreach($users as $user) {
                $this->connection->executeUpdate("UPDATE `users` SET `phone_number1` = $phone1, `phone_number2` = $phone2 WHERE `id` = '$phone_id');
            }
        }
    }

}
livreson ltc
  • 733
  • 8
  • 22
  • You need to quote the phone numbers. Should try and use params and bind the values to prevent SQL injection and not have to write crazy string concatenations. See `prepare`. FWIW: this could be written purely with SQL no? – ficuscr Feb 23 '22 at 03:55
  • @ficuscr this code is going to run only once, then will be remove. I did try to quotes the phones while troubleshooting but still didn't work. Thank you taking your time to comment out. – livreson ltc Feb 23 '22 at 04:00
  • They must be quoted. Does it fail on first iteration? Is that the full error message? I'd use single quote on the string values. Drop all that backtick stuff. Wroth a peek maybe -> [How do I UPDATE from a SELECT in SQL Server?](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) – ficuscr Feb 23 '22 at 04:01
  • It seems to be failing on the first iteration – livreson ltc Feb 23 '22 at 04:10

0 Answers0