将大量数据插入MySQL表只插入1行？

Question

I am pulling data from a IDX/RETS server and trying to insert the data into my own MySQL data base using php.

The data comes in as multiple associative arrays and i loop through them entering each array into its own row.

The table itself has 215 columns, in my first test I only pull 10 records/array's and after running my script I only get 1 row entered.

I have been able to get all 10 rows to insert but only by reducing the number of columns to about 6. For what ever reason when trying to enter all 215 columns of the 10 records it keeps timing out.

I have tried:

ini_set('max_execution_time', 500);

&

set_time_limit(0);

I have also tried entering these values into my php.ini file but what ever i do the script only seems to run for about 15-30 seconds.

Is there something else I am missing or should be doing when entering so many columns??

My code is just a simple while loop that is looping through the arrays. And my insert is like this:

$sql  = "INSERT INTO rets_property_residentialproperty";
        $sql .= " (`".implode("`, `", array_keys($my_array))."`)";
        $sql .= " VALUES ('".implode("', '", $my_array)."') ";
        $result = mysqli_query($dbcon, $sql) or die(mysql_error());

Answer 1

For one thing, don't mix mysql and mysqli interface functions. The call to mysql_error should be replaced with a call to mysqli_error.

Not at all clear what's "timing out".

Most likely, the INSERT statement is throwing an error, the code is going into the die, and the problem is with the mysql_error function. Get that function replaced with mysqli_error, and I venture that you'll get a MySQL error.

    ... or die(mysqli_error($dbcon));
                    ^       ^^^^^^

---

Likely, the underlying issue is a SQL syntax error. For example, one of the values in the array may contain a character that needs to be escaped, such as a single quote.

(But, I'm just guessing here.)

For debugging, you might consider echoing out the SQL text

    echo $sql;

before the call to mysqli_query. (Then you can compare the error to the actual SQL text that you intended to send to the database.

To handle single quotes and other "dangerous" characters in the values, (to close a gaping SQL Injection vulnerability) you'd either need to call mysqli_real_escape_string on each of the array values, before the value is included in the SQL text. Or, use a prepared statement with bind placeholders, and then supply the array values as the values for the bind placeholders.