Skip to content
Advertisement

Insert a php string as multiple rows on MySQL table

I have a table with more than 750 rows of data that I paste on a textarea and get as a single string variable, then I pass the data as just one string thru an ajax and I want to insert it on my database in just one INSER INTO line

 if(isset($_POST['data'])){
            $data = mysqli_real_escape_string($conexion,$_POST['data']); // get the string of data
    
    // on the next two lines I try to give the format like if it were multiple rows of data to insert
            $data = str_replace("t", "','", $data); 
            $data = str_replace("n", "')('", $data);
// at this point $data = data1','data2','data3','data4','data5','data6')
//                       ('data1','data2','data3','data4','data5','data6')
//                       .
//                       .
//                       .
//                       .
//                       ('data1','data2','data3','data4','data5','data6
    $sql = "INSERT INTO table(col1, col2, col3, col4, col5, col5, col6) VALUES ('$data')";
    $result = mysqli_query($conexion,$sql);

Is there a way to make this work, I was able to insert the data on the database one value at a time, but is taking to long Any ideas?

Advertisement

Answer

Multiple rows statement format:

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

You’re using newlines to determine when there should be enclosing parenthesis for the record data. Separate it with a comma, per the format above.

$data = str_replace("n", "'),('", $data);

Since your $data now consists of many records, each one inside enclosing parenthesis and separated by commas, you don’t need to repeat the enclosing parenthesis when you construct your SQL query.

$sql = "INSERT INTO table(col1, col2, col3, col4, col5, col5, col6) VALUES $data";

My preferred method to debug would be to print out the $sql statement. Then copy it and run it directly against your database to see what errors it throws.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement