多个文本输入到MySQL数据库

Question

在我的HTML文件中，我有以下内容以以下格式输入:

Stuff1,Stuff2,Stuff3,Stuff4 
<form action="process_form.php" method="post">
    <form>
     Part 1: <input type="text" name="p1" /><br />
     Part 2: <input type="text" name="p2" /><br />
     Part 3: <input type="text" name="p3" /><br />
     Part 4: <input type="text" name="p4" /><br />
     Part 5: <input type="text" name="p5" /><br />
     Part 6: <input type="text" name="p6" /><br />
     Part 7: <input type="text" name="p7" /><br />
     Part 8: <input type="text" name="p8" /><br />
     Part 9: <input type="text" name="p9" /><br />
     Part 10: <input type="text" name="10" /><br />
    </form>
    <input type="submit" name="formSubmit" value="Submit" />
</form>

从那里，我在我的php文件中使用爆炸来分隔逗号，并从我的字符串创建一个数组。

$create_table1 = "create table parts(qty int(5) NOT NULL,
partID int(5) NOT NULL PRIMARY KEY,
partname varchar(25) NOT NULL,
price int(5) NOT NULL
)";
     $p1_boom = explode(",",$p1); 
     $p2_boom = explode(",",$p2);
     $p3_boom = explode(",",$p3);
     $p4_boom = explode(",",$p4);
     $p5_boom = explode(",",$p5);
     $p6_boom = explode(",",$p6);
     $p7_boom = explode(",",$p7);
     $p8_boom = explode(",",$p8);
     $p9_boom = explode(",",$p9);
     $p10_boom = explode(",",$p10);

现在我要做的是在表中自己的行上输入每组数据。比如P1的所有部分都在表的第一行，P2的所有部分都在表的下一行，等等。提前感谢您的帮助，让我知道，如果你需要更多的信息!

Answer 1

是否需要表创建代码?我将假设每个部分都是TEXT，因为您没有指定....否则，请更改类型。然后还有一些代码来构建查询。

<?php
//find maximum length of $p_booms and the booms
$p_booms = array();
$pb_lengths = array();
for($i = 1; $i <= 10; $i++) {
    $p_booms[] = explode(",", $_POST["p$i"]); //not sanitized! (yet)
    $pb_lengths[] = count($pb_booms[$i]);
}
$pmax = max($pb_lengths);

//create the table with the maximum width
$create_table = "
    CREATE TABLE parts (
    partID INT(5) NOT NULL PRIMARY KEY";
for($i = 0; $i < $pmax; $i++) {
    $create_table .= ", p$i TEXT DEFAULT NULL";
}
$create_table .= ");";
$mysqli->query($create_table);

//then insert the values by building a query
//I am assuming partID is the p1, p2, p3 becomes 1, 2, 3 respectively
foreach($p_booms as $id => $boom) {
    $query = "INSERT INTO parts SET partID=$id";
    foreach($boom as $i => $part) {
        $part = $mysqli->real_escape_string($part); //yay sanitized!
        $query .= ", p$i = '$part'";
    }
    $mysqli->query($query);
}

欢呼

Answer 2

您将无法在字段中插入数组。您必须在插入之前将其保留为字符串或序列化/json_encode数组。插入行与插入任何数据块没有什么不同。

$db = new PDO(/*connection info*/);
$stmt = $db-prepare('insert into tableName(myColumn) values(?)');
// Just loop throw the data with...
$stmt->execute(array($stringToInsert));

Answer 3

使用serialize()函数。

$p10_boom = explode(",",$p10);
$p10_boom_serial = serialize( $p10_boom );

产生如下内容:

a:4:{i:0;s:6:"Stuff1";i:1;s:6:"Stuff2";i:2;s:6:"Stuff3";i:3;s:6:"Stuff4";}

保存到DB时转义该字符串即可。

现在，无论您要用于数据的哪个字段都应该是文本字段或长VARCHAR字段。

如果您必须检索值并需要将其转换回数组，请使用unserialize()。您可以使用join()或implode()