Michael Michael - 5 months ago 11
SQL Question

PHP: Inserting Multiple array into mysql table

I am testing multiple array insertion into my table, I have try all what I could but I am not getting.Here is my code:

<?php

ini_set('display_errors',1);
//ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$mysqli = new mysqli(HOST,USER,PASS,DB);
$message = array();

$id =1;
$SocialHandle = "Facebook,Twitter,LinkIn";
$SociaUrl ="Url1,Url2,Url3";

$strSocialHandle = explode(',', $SocialHandle);
$strSociaUrl = explode(',', $SociaUrl);
print_r($strSocialHandle);
print_r($strSociaUrl);
$sql = "INSERT INTO `social_table`(`id`, `social_handle`, `handle_url`) VALUES";
foreach($strSocialHandle as $SocialNameValue){
$sql .= "({$id}, '{$SocialNameValue}','{$strSociaUrl}'),";
}
$sql = rtrim($sql, ',');
$result = $mysqli->query($sql);

if (!$result){
$message = array('Message' => 'insert fail or record exist');
echo json_encode($message);
}else{
$message = array('Message' => 'new record inserted');
echo json_encode($message);
}
?>


Here is my goal achievement:


ID         social handle      
  handle url
1           Facebook
        url1
1        
  Twitter             url2
1
          LinkIn        
    url3


Please help.

Answer

You can use for loop for it as

$id =1;
$SocialHandle = "Facebook,Twitter,LinkIn";
$SocialHandle = explode(",", $SocialHandle);
$SociaUrl = "Url1,Url2,Url3";
$SociaUrl = explode(",", $SociaUrl);

$sql = "INSERT INTO `social_table`(`id`, `social_handle`, `handle_url`) VALUES";
for ($i = 0; $i < count($SocialHandle); $i++) {
    $sql .= "({$id}, '$SocialHandle[$i]','$SociaUrl[$i]'),";
}


$sql = rtrim($sql, ',');
echo $sql;
$result = $mysqli->query($sql);

OUTPUT

INSERT INTO social_table(id, social_handle, handle_url) VALUES(1, 'Facebook','Url1'),(1, 'Twitter','Url2'),(1, 'LinkIn','Url3')

DEMO

UPDATED

Better use prepare and bind statement to prevent form sql injection as

for ($i = 0; $i < count($SocialHandle); $i++) {
    $sql = "INSERT INTO `social_table`(`id`, `social_handle`, `handle_url`) VALUES (?,?,?)";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('iss', $id, $SocialHandle[$i], $SociaUrl[$i]);
    $stmt->execute();
}
Comments