Json to MySQL Query Error when executing manually it works

Question

Json to MySQL when ran it imports into the database fine. When i run the SQL Query manually it runs without an error.

When am running it through the code, its returning back with a error in my SQL syntax near line 1.

I have removed the magic quotes, still errors. Can someone shine some light on this before i chuck in the towl.

I understand magic quotes are deprecated, and i thought this was causing the error, but these have been removed.

<?php
/**
 * Created by PhpStorm.
 * User: dave
 * Date: 14/11/2018
 * Time: 15:26
 */

$connect = mysqli_connect("localhost", "root", "password", "clients");
$query = '';
$table_data = '';
$filename = "test.json";

$data = file_get_contents($filename);
$array = json_decode($data, true);

foreach($array as $set)
{
    $tblName = $set['tableName'];
    echo $tblName ;
    if(sizeof($set['rows']) > 0) {
        $query = '';
        $colList = array();
        $valList = array();
        //  Get list of column names
        foreach($set['rows'][1] as $colname => $dataval) {
          // $colList[] = "`".$colname."`";
           $colList[] = $colname;
        }
        $query .= "INSERT INTO ".$tblName." \n";
        $query .= "(".implode(", ",$colList).")\nVALUES\n";
        //  Go through the rows for this table.
        foreach($set['rows'] as $idx => $row) {
            $colDataA = array();
            //  Get the data values for this row.
            foreach($row as $colName => $colData) {
                $colDataA[] = "'".$colData."'";
            }
            $valList[] = "(".implode(",",$colDataA).")";
        }
        //  Add values to the query.
        $query .= implode(",\n",$valList)."\n";
        echo "<p>Insert query:<pre>$query</pre></p>";
        $results = mysqli_query($connect, $query);

 //       if ($connect->query($results) === TRUE) {
 //           echo "New record created successfully";
 //       } else {
  //          echo $results . "<br>" . $query . "<br>" ;
 //            echo "error" .$connect->error ;
 //       }

        echo "<h1>".mysqli_num_rows($connect)." Rows appeded in $tblName</h1>";
    } else {
        echo "<p>No rows to insert for $tblName</p>";
    }
}

Json File:

[
    {
      "tableName":"contacts",
      "rows":[
        {
          "First_Name": "Dave",
          "Last_Name": "Frank",
          "Company": "Company1",
          "Business_Phone": "0115 999999",
          "Email_Address": "zvv@zz.com"
        },
        {
          "First_Name": "Dave",
          "Last_Name": "Blogs",
          "Company": "Company2",
          "Business_Phone": "0115 888888",
          "Email_Address": "zvv@zz.com"
        },
        {
          "First_Name": "David",
          "Last_Name": "frank",
          "Company": "Company3",
          "Business_Phone": "0115 777777",
          "Email_Address": "zvv@zz.com"
        }
      ]
    },
    {
      "tableName":"contacts_old",
      "rows":[
        {
          "First_Name": "Dave",
          "Last_Name": "Frank",
          "Company": "Company1",
          "Business_Phone": "0115 999999",
          "Email_Address": "zvv@zz.com"
        },
        {
          "First_Name": "Dave",
          "Last_Name": "Blogs",
          "Company": "Company2",
          "Business_Phone": "0115 888888",
          "Email_Address": "zvv@zz.com"
        },
        {
          "First_Name": "David",
          "Last_Name": "frank",
          "Company": "Company3",
          "Business_Phone": "0115 777777",
          "Email_Address": "zvv@zz.com"
        }
      ]
    }
]

Here is the Echo as per request:

contacts

Insert query:

INSERT INTO contacts 
(First_Name, Last_Name, Company, Business_Phone, Email_Address)
VALUES
('Dave','Frank','Company1','0115 999999','zvv@zz.com'),
('Dave','Blogs','Company2','0115 888888','zvv@zz.com'),
('David','frank','Company3','0115 777777','zvv@zz.com')

Error: 1
INSERT INTO contacts (First_Name, Last_Name, Company, Business_Phone, Email_Address) VALUES ('Dave','Frank','Company1','0115 999999','zvv@zz.com'), ('Dave','Blogs','Company2','0115 888888','zvv@zz.com'), ('David','frank','Company3','0115 777777','zvv@zz.com')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1contacts_old

Insert query:

INSERT INTO contacts_old 
(First_Name, Last_Name, Company, Business_Phone, Email_Address)
VALUES
('Dave','Frank','Company1','0115 999999','zvv@zz.com'),
('Dave','Blogs','Company2','0115 888888','zvv@zz.com'),
('David','frank','Company3','0115 777777','zvv@zz.com')

Error: 1
INSERT INTO contacts_old (First_Name, Last_Name, Company, Business_Phone, Email_Address) VALUES ('Dave','Frank','Company1','0115 999999','zvv@zz.com'), ('Dave','Blogs','Company2','0115 888888','zvv@zz.com'), ('David','frank','Company3','0115 777777','zvv@zz.com')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in

Answer 1

So as Nigel pointed out, your original problem was caused by passing a result set to query() instead of a string. I expect this question will be closed as off-topic due to typo, but there are a number of problems and inefficiencies in your code I wanted to point out before that happens.

Most importantly, your lack of prepared statements and data sanitization means that your query will fall apart as soon as an apostrophe shows up. I'm rusty on mysqli, so I've used PDO in this code here, because I can do it off the top of my head. It's a more modern interface anyway, and far less verbose. You can read more about prepared statements in both PDO and mysqli, but the important thing to remember is that you prepare your statement, using ? as a placeholder for values you're going to insert. Then, you execute the statement, passing the values in. The database takes care of escaping any nasty values, and it's a lot less overhead than rebuilding an entire query on every iteration of the loop.

You should also be looking to use PHP's built-in functions like array_keys, array_values, or array_columns to extract data, rather than resorting to foreach loops.

<?php
$connect = new PDO("mysql:host=localhost;dbname=clients", "root", "password");
$filename = "test.json";

$data = file_get_contents($filename);
$array = json_decode($data, true);

foreach($array as $set)
{
    $tblName = $set['tableName'];
    if(count($set['rows']) > 0) { /* typical to use count() for measuring array size */
        //  Get list of column names
        $colList = array_keys($set["rows"][0]); /* no loop needed */
        $query = "INSERT INTO `$tblName` \n";
        $query .= "(" . implode(", ", $colList) . ")\nVALUES\n";
        $placeholders = implode(",", array_fill(0, count($colList), "?"));
        $query .= "($placeholders)"; /* you should have INSERT INTO xxx (yyy, zzz) VALUES (?, ?) */
        $stmt = $connect->prepare($query);
        if (!$stmt) {
            echo "Prepare error: $query<br/>" . $connect->errorInfo()[2];
            continue; /* give up, go to next record set */
        }
        //  Go through the rows for this table.
        foreach($set['rows'] as $row) {
            $result = $stmt->execute($row); /* hey you can just pass the array directly! */
            if ($result === TRUE) {
                echo "New record created successfully";
            } else {
                echo "Execute error: $stmt->queryString<br/>" . $stmt->errorInfo()[2];
            }
        }
    } else {
        echo "<p>No rows to insert for $tblName</p>";
    }
}