I have a web-based system that reads a sheet of an excel file (each sheet uploaded is 6 rows-5 columns) like this:
Below is the code I'm using (for demonstration purposes I've set static values for the $entry but its real code would be for example $entry1=$objTpl->getActiveSheet()->getCell('A2')->getValue();
$con=mysqli_connect("localhost","root","","view");
if(isset($_POST['submit']))
{
$entry1 = 1;
$entry2 = 2;
$entry3 = 3;
$entry4 = 4;
$entry5 = 5;
$entry6 = 1;
$entry7 = 2;
$entry8 = 0;
$entry9 = 4;
$entry10 = 0;
$entry11 = 0;
$entry12 = 2;
$entry13 = 3;
$entry14 = 4;
$entry15 = 0;
$entry16 = 1;
$entry17 = 2;
$entry18 = 0;
$entry19 = 0;
$entry20 = 5;
$entry21 = 1;
$entry22 = 0;
$entry23 = 3;
$entry24 = 0;
$entry25 = 5;
if($entry1 != 0 || !empty($entry1))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry1','$entry1')");
}
if($entry2 != 0 || !empty($entry2))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry2','$entry2')");
}
if($entry3 != 0 || !empty($entry3))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry3','$entry3')");
}
if($entry4 != 0 || !empty($entry4))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry4','$entry4')");
}
if($entry5 != 0 || !empty($entry5))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry5','$entry5')");
}
if($entry6 != 0 || !empty($entry6))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry6','$entry6')");
}
if($entry7 != 0 || !empty($entry7))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry7','$entry7')");
}
if($entry8 != 0 || !empty($entry8))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry8','$entry8')");
}
if($entry9 != 0 || !empty($entry9))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry9','$entry9')");
}
if($entry10 != 0 || !empty($entry10))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry10','$entry10')");
}
if($entry11 != 0 || !empty($entry11))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry11','$entry11')");
}
if($entry12 != 0 || !empty($entry12))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry12','$entry12')");
}
if($entry13 != 0 || !empty($entry13))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry13','$entry13')");
}
if($entry14 != 0 || !empty($entry14))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry14','$entry14')");
}
if($entry15 != 0 || !empty($entry15))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry15','$entry15')");
}
if($entry16 != 0 || !empty($entry16))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry16','$entry16')");
}
if($entry17 != 0 || !empty($entry17))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry17','$entry17')");
}
if($entry18 != 0 || !empty($entry18))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry18','$entry18')");
}
if($entry19 != 0 || !empty($entry19))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry19','$entry19')");
}
if($entry20 != 0 || !empty($entry20))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry20','$entry20')");
}
if($entry21 != 0 || !empty($entry21))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry21','$entry21')");
}
if($entry22 != 0 || !empty($entry22))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry22','$entry22')");
}
if($entry23 != 0 || !empty($entry23))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry23','$entry23')");
}
if($entry24 != 0 || !empty($entry24))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry24','$entry24')");
}
if($entry25 != 0 || !empty($entry25))
{
mysqli_query($con,"INSERT INTO viewer(base_id,name,value) VALUES('1','entry25','$entry25')");
}
}
?>
I do have a couple of questions:
Yes, you can do this with a single SQL statement. It would work like this. You can string together as many sets of (val, val, val) as you want. (There's a limit on the string length of the overall query, but it's quite large in MySQL).
INSERT INTO viewer(base_id,name,value) VALUES
('1','entry1','$entry1'),
('1','entry2','$entry2'),
('1','entry3','$entry3')
If you're inserting stuff into an InnoDB table, you get quite a lot of performance boost by doing this when your connection is in autocommit mode. That's because you commit all the rows in the insert together, and commit has an overhead.
Edit
In php, if you have an array $values, each element of which contains a text string like (1,'val','val')
you can use this code to create your query string.
$sqlstart = 'INSERT INTO viewer(base_id,name,value) VALUES ';
$sql = sqlstart . implode(',' , $values);
This puts commas between but not after the elements of the $values array. implode()
is made for this kind of purpose.
So, your code might look like this:
$values = array(); /* make an empty array */
if ($entry1 != 0 || !empty($entry1)) {
$values[] = "('1','entry1','$entry1')"; /* append to array */
}
if ($entry2 != 0 || !empty($entry2)) {
$values[] = "('1','entry2','$entry2')";
}
/* etc etc for the rest of your $entry values */
$sqlstart = 'INSERT INTO viewer(base_id,name,value) VALUES ';
$sql = sqlstart . implode(',' , $values);
(Please beware of SQL insertion exploits).