PHP：将分隔的逗号字符串值与多个数组值一起插入MySql [英] PHP: Insert separated comma string value with as multiple array value Into MySql

问题描述

这是我的目标
1.我只有一个ID从服务器发送，字符串分隔逗号
这个看起来如下：ID = 1，names = blue，red，green，黄色
2.这是我的尝试：
2.1我尝试使用此代码将名称更改为数组

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我试试这样插入：

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);     
            }

这是我的完整代码视图

 <?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);   
                } ?>

这是我想要在
以下实现的目标。
表
ID   类别

1      RED

1      Blue

1      Black

This is what i want to achieve below
TABLE
ID     Categories
1        RED
1        Blue
1        Black

插入后

请帮助我不知道我做错了什么

Please help i don't know what i doing wrong

推荐答案

虽然该SQL无效，但您永远不会关闭值。 Explode也不构建关联的数组。

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

如何构建有效的SQL语句的一个粗略示例是

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

演示： https://eval.in/587840

如果对构造中的数组如何使用有疑问 print_r 或 var_dump 。如果在mysqli中遇到查询问题，请使用错误报告， http://php.net/ manual / zh / / mysqli.error.php 。

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.

此外，在您目前的使用情况下，您不会接受SQL注入，但如果 $ myString 来自用户输入，或者您可能是您的数据库。您应该研究使用参数化查询; http://php.net/manual/en/mysqli.quickstart.prepared -statements.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.

这篇关于PHP：将分隔的逗号字符串值与多个数组值一起插入MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！