Michael Michael - 5 months ago 30
Java Question

PHP: Insert separated comma string value with as multiple array value Into MySql

Here is my goal
1. I have only one ID send from the server with list of string separated comma
this how it look like: ID=1, names=blue,red,green,yellow
2. This is my attempt:
2.1 i try to change the names to arrays by using this code

$myString = "Red,Blue,Black";
$myArray = explode(',', $myString);


2.2 and i try my insertion like this:

$sql="INSERT INTO `cat_interest`(`id`,`categories`) VALUES (1,'".$myArray["categories"]."'";
if (!$result = $mysqli->query($sql)){
$message = array('Message' => 'insert fail');
echo json_encode($message);
}else{
$message = array('Message' => 'new record inserted');
echo json_encode($tempArray);
}


Here is my complete code view

<?php
define('HOST','serveraddress');
define('USER','root');
define('PASS','pass');
define('DB','dbname');
ini_set('display_errors',1);
//ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$mysqli = new mysqli(HOST,USER,PASS,DB);
$message= array();
$myString = "Red,Blue,Black";// incoming string comma names
$myArray = explode(',', $myString);

$sql="INSERT INTO `cat_interest`(`id`,`categories`) VALUES (1,'".$myArray["categories"]."'";
if (!$result = $mysqli->query($sql)){
$message= array('Message' => 'insertion failed');
echo json_encode($message);
}else{
$message= array('Message' => 'new record inserted');
echo json_encode($message);
} ?>


This is what i want to achieve below

TABLE

ID     Categories

1        RED

1        Blue

1        Black

after insertion

Please help i don't know what i doing wrong

Answer

While that SQL is invalid, you never close the values. Explode also doesn't build an associated array.

A rough example of how you could build a valid SQL statement would be

$myString = "Red,Blue,Black";// incoming string comma names
$myArray = explode(',', $myString); 
print_r($myArray);
$sql = "INSERT INTO `cat_interest`(`id`,`categories`) VALUES";
foreach($myArray as $value){
    $sql .= " (1, '{$value}'),";
}
$sql = rtrim($sql, ',');

Demo: https://eval.in/587840

When in doubt about how an array in constructed use print_r or var_dump. When having an issue with a query in mysqli use error reporting, http://php.net/manual/en/mysqli.error.php.

Also in your current usage you aren't open to SQL injections but if $myString comes from user input, or your DB you could be. You should look into using parameterized queries; http://php.net/manual/en/mysqli.quickstart.prepared-statements.php.

Comments