CODEWITHSUNDEEP
phpmysqli
hainam
hainam

Reputation: 13

Insert multiple value taken from table 1 into table 2 using PHP

I try to get all the value from table 1 then push them in an array. Then I insert the array into table 2, but I got the error said that: "Illegal string offset 'name' ","Illegal string offset 'lat' ", "Illegal string offset 'lng' " I have searched for this error and try to fix it, but I can't. Can anybody help me with this? Here is my code:

$query = "DELETE FROM gpsdata";
    $query_input = "SELECT name, lat, lng FROM gpsdata";
    $result = mysqli_query($connect, $query_input);
    while ($data = mysqli_fetch_array($result)) {
        if (is_array($data)) {
            $sql = array();
            foreach ($data as $row) {
                $sql[] = '(' . $row['name'] . ', ' . $row['lat'] . ', ' . $row['lng'] . ')';
            };
            $query2 = "INSERT INTO gpsdata_backup (name, lat, lng) VALUES ' . implode(',', $sql)'";
            $result_insert = mysqli_query($connect, $query2);
       }
}

Upvotes: 0

Views: 55

Answers (2)

Professor Abronsius
Professor Abronsius

Reputation: 33813

As you are not performing any tasks on the data why not do the entire operation in mysql by inserting directly with a select?

insert into `gpsdata_backup` ( `name`, `lat`, `lng` )
    select `name`, `lat`, `lng` from `gpsdata`;

ie:

$sql='insert into `gpsdata_backup` ( `name`, `lat`, `lng` )
    select `name`, `lat`, `lng` from `gpsdata`';
    
$result=$connect->query( $sql );

Upvotes: 1

Chilarai
Chilarai

Reputation: 1888

There you go. The illegal offset error is coming because you have an unnecessary inner foreach loop due to which the values were not found in your $row array. That is not required

// Array declaration to be on top
$sql = array();
$query_input = "SELECT name, lat, lng FROM gpsdata";
$result = mysqli_query($connect, $query_input);

while ($data = mysqli_fetch_array($result)) {

    if (is_array($data)) {

        // Enclose string values in quotes
        $sql[] = '("' . $data['name'] . '", "' . $data['lat'] . '", "' . $data['lng'] . '")';
        
   }
} 

// Final insert should not be inside the loop 
$query2 = "INSERT INTO gpsdata_backup (name, lat, lng) VALUES ".implode(",",$sql);
$result_insert = mysqli_query($connect, $query2);

Upvotes: 0

Related Questions