如何使用PHP将多维数组存储到mysql中

Question

我已经创建了一个名为"test“的数据库，并创建了一个名为"biodata”的表。我已经创建了3列，称为“名称”，“年龄”和“描述”到生物数据表。现在如何将数组结果存储到每一列中。

下面是完整的代码...

<?php

    $ip = "localhost";
    $username = "root";
    $password = "";
    $dbname = "test";
    $res = mysql_connect($ip,$username,$password);
    if(!$res)
    {
        echo "DB Connection Failed.";
        exit;
    }
    if(!mysql_select_db("test"))
    {
        echo "NOT SELECTED";
        exit;
    }

        $company =  array(
                    'Record1'=>array('Shabbir',26,'Designer'),
                    'Record2'=>array('Burhan',24,'Architecture'),
                    'Record3'=>array('Huzeifa',20,'Accountant'),
                );

        foreach ($company as $employees=>$details){

        echo '<strong>'.$employees.'</strong><br>';

        foreach($details as $employeeinfo){

            echo $employeeinfo.'<br>';

        }

        }

        $sql = "INSERT INTO biodata (Name, Age, Description) VALUES ($employeeinfo[0], $employeeinfo[1], '$employeeinfo[2]')";
        mysql_query($sql);

?>

Answer 1

您的mysql_query应该在您的foreach语句中...由于SQL Injection的存在，您还需要清理数据

你也不需要2条foreach语句...

校正

foreach ($company as $employees =>$details){
    echo '<strong>'.$employees.' - OK</strong><br>';
    mysql_query(sprintf($sql,mysql_real_escape_string($details[0]),mysql_real_escape_string($details[1]),mysql_real_escape_string($details[2])));
}

完整脚本排列

$ip = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$res = mysql_connect($ip,$username,$password);
$sql = "INSERT INTO biodata (Name, Age, Description) VALUES ('%s', '%d', '%s')";
$company =  array(
        'Record1'=>array('Shabbir',26,'Designer'),
        'Record2'=>array('Burhan',24,'Architecture'),
        'Record3'=>array('Huzeifa',20,'Accountant'),
);

if(!$res)
{
    echo "DB Connection Failed.";
    exit;
}

if(!mysql_select_db("test"))
{
    echo "NOT SELECTED";
    exit;
}

foreach ($company as $employees =>$details){
    echo '<strong>'.$employees.' - OK</strong><br>';
    mysql_query(sprintf($sql,mysql_real_escape_string($details[0]),mysql_real_escape_string($details[1]),mysql_real_escape_string($details[2])));
}

Answer 2

附注。无论你的循环有多小(迭代次数很少)，都不要把查询放在里面。相反，使用循环只构造一个包含所有数据的复杂查询，然后在循环外执行该查询。

EDIT:可以在循环中构造的查询的示例。

INSERT INTO table 
    (name, age, position)
VALUES
   ('Shabbir', 26, 'Designer'),
   ('Burhan', 24, 'Architecture'),
   ('Huzeifa', 20, 'Accountant');

Answer 3

您的SQL查询只是放在错误的位置：

foreach ($company as $employees=>$details)
{
    echo '<strong>'.$employees.'</strong><br>';
        foreach($details as $employeeinfo)
        {
            echo $employeeinfo.'<br>';
        }
    $sql = "INSERT INTO biodata (Name, Age, Description) VALUES ($details[0], $details[1], '$details[2]')";
    mysql_query($sql);
}