How to insert array as key => value with string value into sql using php

Question

foreach($_SESSION['s'] as $key=>$value){
  $name[]="'".$value."'";
}
$name=implode(",",$name);

$conn= mysqli_connect("localhost","root","","slashbill");

for($x=0;$x<=3;$x++){
  $sql = "INSERT INTO slashbill (member,give,receive,paid) VALUES  
  ($name,'".$give[$x]."','".$receive[$x]."','".$paid1[$x]."')";

  $exe=mysqli_query($conn,$sql);
 }

I am having just a small problem with the name array, when i perform the above code every row's member values takes the value of

What i am getting

member
-----
name1,name2,name3,name4
name1,name2,name3,name4
name1,name2,name3,name4
name1,name2,name3,name4

What i want is this

member
-----
name1
name2
name3
name4

Note: Every Other Column is perfectly inserted and I would take into account the SQL Injection security later on.

score 0 · Accepted Answer · answered Jun 12 '20 at 15:44

0

I think you just need to keep $name as an array, don't implode it back into a string, and then loop over that. I have no idea what is in $give and others. I see that they are arrays but I don't understand their relationship to $name. The below code does this and inserts 16 records (assuming 4 names):

// Store names in an array
$names = [];
foreach ($_SESSION['s'] as $key => $value) {
    $names[] = $value;
}

$conn = mysqli_connect("localhost", "root", "", "slashbill");
$sql = 'INSERT INTO slashbill (member,give,receive,paid) VALUES (?,?,?,?)';

// Loop over names
foreach ($names as $name) {
    for ($x = 0; $x <= 3; $x++) {
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            die('Could not prepare statement');
        }
        if (!$stmt->bind_param('ssss', $name, $give[$x], $receive[$x], $paid1[$x])) {
            die('Could not bind statement');
        }
        if (!$stmt->execute()) {
            die('Could not execute statement');
        }
    }
}

If, however, there's some magic relation between the session data and your other variables, and you really want to only insert 4 rows, then this version which still track $x can be used.

// Store names in an array
$names = [];
foreach ($_SESSION['s'] as $key => $value) {
    $names[] = $value;
}

$conn = mysqli_connect("localhost", "root", "", "slashbill");
$sql = 'INSERT INTO slashbill (member,give,receive,paid) VALUES (?,?,?,?)';

// Loop over names
for ($x = 0; $x <= count($names); $x++) {
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        die('Could not prepare statement');
    }
    if (!$stmt->bind_param('ssss', $names[$x], $give[$x], $receive[$x], $paid1[$x])) {
        die('Could not bind statement');
    }
    if (!$stmt->execute()) {
        die('Could not execute statement');
    }
}

I know you said you'll worry about escaping it later, but I just switched it over to a prepared statement any because it is much easier to ready for everyone.

answered Jun 12 '20 at 15:44

Chris Haas

53,986
12
141
274

Does the first `foreach()` achieve much? – Nigel Ren Jun 12 '20 at 15:47
You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) – Dharman Jun 12 '20 at 15:51
@Dharman, all code I post on this site assumes that the OP has warnings turned off because that is historically true. I think I closed a dozen posts this week that were simple "turn errors on and you'll see the problem". So when I give someone code here, I always make sure it screams painfully for them, and I'll let them deal with configuring whatever later. In the past, I would sometimes put the error directives at the top of my code, but people just don't copy and paste that. – Chris Haas Jun 12 '20 at 16:04
@NigelRen, besides looping, I don't know what you mean? Are you asking why a `foreach` once and then a `for` loop next? – Chris Haas Jun 12 '20 at 16:05
Just that you will probably end up with `$names` == `$_SESSION['s']`. – Nigel Ren Jun 12 '20 at 16:07
I don't really understand your way of thinking but this is ok. I will just leave my comment for other readers to know that you should never check the return value manually. I still appreciate that your answer at least shows how to use prepared statements. – Dharman Jun 12 '20 at 16:07
@NigelRen The keys will be different. – Dharman Jun 12 '20 at 16:07
@NigelRen, I see what you mean. I assumed the OP had some other logic in there which is why I kept it, but if they don't, I agree, just use that. – Chris Haas Jun 12 '20 at 18:07
@Chris Haas Thank You It solved the problem :) . I liked that you gave your time and tried to solve it and others be like why this why that and closed the question { I knew that :) }. But there is a problem the code inserts into the database but outputs 'Could not execute statement'. – NR17 Jun 13 '20 at 05:39
So the array has 4 elements but the for loop executes five time so the error 'Could not execute statement' but still enters. :) – NR17 Jun 13 '20 at 15:02
You will need to print/echo the contents of $names or each $name to see if you have something extra in there – Chris Haas Jun 14 '20 at 15:23

How to insert array as key => value with string value into sql using php

1 Answers1