mysql 逗号分割个数组,PHP：将分隔的逗号字符串值与多个数组值一起插入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

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

解决方案

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, ',');

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.