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.