Inserting multiple rows into database through a link

Question

I am quite new in php and mysql. I was trying to insert multiple (unknown ) number of rows into mysql database. The data is posted into the table through a link - http://localhost:81/logdata.php?CtrlID=3842&DateTime=2017-05-18+11%3A45%3A23&Bat=50.2&LVSD=1&Indt=29.4&Outdt=32.8&submit

The following code works perfect as long as a single row is posted. But I have no idea how to insert several rows together and how the link should look like. Actually rows containing data are formed and stored in a Microcontroller. I am sending the data with the help of GPRS. The controller successfully sending one row at a time, the data is correctly recorded in mysql database. But I am struggling to send several rows together. I would highly appreciate any suggestion.

<?php
$servername = "localhost";
$username = "root";
$password = ""; //your pwd
$dbname = "mirzu";

// Create connection
$conn = new mysqli($servername, $username, $password,$dbname);
if($conn){
    echo 'Successfully Connected database.';
     }

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

//if(isset($_GET['submit'])){   //
    $ID = $_GET['CtrlID'];
    $DateTime = $_GET['DateTime'];
    $battery = $_GET['Bat'];
    $LVSD = $_GET['LVSD'];
    $IndoorT = $_GET['Indt'];
    $OutdoorT = $_GET['Outdt'];

$totalCtrlID = sizeof($ID);

for($i=0;$i<$totalCtrlID;$i++) {

    $InsCtrlID = [$ID][$i]; 
    $InsDateTime = [$DateTime][$i];
    $Insbattery = [$battery][$i];
    $InsLVSD = [$LVSD][$i];
    $InsIndoorT = [$IndoorT][$i];
    $InsOutdoorT = [$OutdoorT][$i];


 $query = "INSERT INTO btsdata (CtrlID,DateTime,Batt,LVSD,IndT,OutdT)". 
"VALUES  ('$InsCtrlID','$InsDateTime','$Insbattery','$InsLVSD','$InsIndoorT','$InsOutdoorT');";

}

if (mysqli_query($conn,  $query)) {
    echo "New record created successfully into database";
} else {
    echo "Error: " .  $query . "<br>" . mysqli_error($conn);
}
//}
mysqli_close($conn);
?>

It is a bad idea to insert values to database from $_GET. And to trust user input in general. Read about SQL injections. — ArtOsi
– ArtOsi, Commented May 18, 2017 at 9:06
Thanks, GET will be replaced with POST, not a big problem. But my main problem is now inserting multiple rows with one posting. — Mainul
– Mainul, Commented May 18, 2017 at 11:05
Answer provided by @Bhupesh Kushwaha is what you looking for. Other topic, you used in here a lot of bad practises. You should always, validate data, before infect to SQL. — timiTao
– timiTao, Commented May 18, 2017 at 11:30

Bhupesh · Accepted Answer · 2017-05-18 11:27:14Z

1

Two records the query will become:

 INSERT INTO TABLE (column1, column2) VALUES ('data', 'data'), ('data', 'data')

Same as a more than two record.

 INSERT INTO tbl_name
        (a,b,c)
    VALUES
        (1,2,3),
        (4,5,6),
        (7,8,9);

answered May 18, 2017 at 11:27

Bhupesh

8817 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mainul Over a year ago

Bhupesh Kushwaha, thanks for the answer. The main issues actually are, the total number of row is unknown and the data will be posted through a link as I have mentioned at the beginning of my post. timiTao, sorry for the 'bad practices', I am completely new in php. Hope to improve gradually.

Mainul Over a year ago

The data needs to come from a remote location through GPRS Modem. Bhupesh, could you please elaborate a little bit.

Bhupesh Over a year ago

tell me one thing CtrlID=3842&DateTime=2017-05-18+11%3A45%3A23&Bat=50.2&LVSD=1&Indt=29.4&Outdt=32.8 its repeated or not CtrlID,DateTime ..

Mainul Over a year ago

Sorry for not understanding your question. Here the CtriID=3842 is a constant number, all others are variables recorded in one row in the controller in 1 minutes interval. After 15 minutes or so the GPRS modem sends the records to the URL at a time. The number of records may increase if the modem fails to communicate, will automatically retry after 15 minutes, but the number of rows will increase.

Bhupesh Over a year ago

@mainul can you share those url

|

Collectives™ on Stack Overflow

Inserting multiple rows into database through a link

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related