PHP Looping through elements and adding to Database

Question

I don't know if what I'm trying to do is achievable / plausible but I'll ask anyway:

I am building a survey type website and am looking at storing the responses in a MySQL DB. Each response / text field will be a record

The current page that I'm working on has two elements (Answer1 and Answer2).

How can I get the submit to add each answer as a separate line in my DB? eg, 2 separate records, with different text stored in the responsetext field?

I have the below (obviously changed for security reasons), which adds one record, but of the element that I specify:

$conn = new mysqli($dbhost, $dbuser, $dbpass,$dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
} 
$stmt = $conn->prepare("INSERT INTO responses (qkey,rtext)
VALUES ('".$_POST["1"]."','".$_POST["Q1Answer"]."')");  

$Q1Answer = $_POST["Q1Answer"];

$stmt->bind_param("s", $Q1Answer);

$stmt->execute();

$stmt->close();   

$conn->close();

I imagine I could loop through all the elements and add them one by one? Is this possible on the submit action / post?

I have searched here but can't find anything that has been able to help...

What are you binding??? You are already inserting unsafe data — Rotimi, Apr 15 '17 at 15:44
I remember your earlier [(now deleted) question](http://stackoverflow.com/q/43426706/1415724) and [I gave you links](http://stackoverflow.com/questions/43426706/php-mysql-looping-through-elements-and-inserting-into-database#comment73911800_43426706) to look at and your question doesn't show any effort provided from them. Note: Links viewable by the OP and 10k+ members only. — Funk Forty Niner, Apr 15 '17 at 15:47
RTM on that `bind_param()` http://php.net/manual/en/mysqli-stmt.bind-param.php — Funk Forty Niner, Apr 15 '17 at 15:49
@Akin, it executes, but if it's unsafe, I'd rather do a) safely / properly and b) loop through everything and update the DB from the elements... — QS Train, Apr 15 '17 at 16:02
@Fred-ii- I found code from Stackoverflow that I used.Will read through your suggestions instead.Thanks... — QS Train, Apr 15 '17 at 16:04
@QSTrain : let's say that `Q1Answer` asks for my son's name, I would maybe answer [Robert'); DROP TABLE responses;--](http://www.bobby-tables.com/) ^^ — OldPadawan, Apr 15 '17 at 16:16
@QSTrain : please read [PPS : Prepared Parameterized Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). This will help [Preventing SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Then, you may want to have a look at something that was discussed earlier and where you can find [an basic example](http://stackoverflow.com/questions/43422233/php-insert-into-not-working/43422758#43422758) where you can even more sanitize $_POST data before preparing : **never** trust data coming from user ! — OldPadawan, Apr 15 '17 at 16:32

K. Tromp · Answer 1 · 2017-04-20T06:24:35.660

2

First of all, NEVER use $_POST or $_GET or $_SESSION values in your sql-statement, you should always sanitize with for example:

$value = mysqli_real_escape_string($_POST['value1']);

This is to prevent any mysql injections just like OldPadawan mentions..

To answer your question you could use a for loop:

$i = 1;
foreach ( $_POST as $key => $value ){
    if ( $key == $i ){
        $value.$i = mysqli_real_escape_string($value);
     }
     if ( $key == 'Q'.$i.'Answer' ){
        $value.$i = mysqli_real_escape_string($value);
     }
    if ( $i % 2 ){
        mysqli_query($conn, "INSERT INTO responses (qkey,rtext) VALUES ($key.($i-1), $key.$i)");
    }
$i++;
 }

edited Apr 20 '17 at 06:24

answered Apr 15 '17 at 16:29

K. Tromp

350
1
13

this is not the good practice to add query in loop. you should make query first and then insert in one request. – Mumtaz Ahmad Apr 20 '17 at 06:28
What would you suggest? – K. Tromp Apr 20 '17 at 20:10

score 0 · Answer 2 · answered Apr 21 '17 at 07:24

look at this @K. Tromp

$i = 1;
$queryValue = NULL;
foreach ($_POST as $key => $value) {
    if ($key == $i) {
        $value . $i = mysqli_real_escape_string($value);
    }
    if ($key == 'Q' . $i . 'Answer') {
        $value . $i = mysqli_real_escape_string($value);
    }
    if ($i % 2) {
        $queryValue .= "($key.($i-1), $key.$i),";
    }
    $i++;
}
$query = 'INSERT INTO responses (qkey,rtext) VALUES ' . rtrim($queryValue);
mysqli_query($conn, $query);

PHP Looping through elements and adding to Database

2 Answers2

Linked