我对以下代码有一些问题。
<?php
$mysql = new mysqli("localhost", "user", "pw", "db");
unset($_POST);
$_POST = array();
$q = "INSERT INTO shk_marketplace_requests (".
"village_id, Grafschaft, player_id, village_name, ".
"Holz, Stein, Eisen, Pech, ".
"_Ae_pfel, K_ae_se, Fleisch, Brot, Gem_ue_se, Fisch, Bier, ".
"Wild, M_oe_bel, Metallwaren, Gew_ae_nder, Wein, Salz, Gew_ue_rze, Seide, ".
"B_oe_gen, Piken, R_ue_stungen, Schwerter, Katapulte".
") VALUES (".
"?, IFNULL((SELECT id FROM shk_marketplace_grafschaft WHERE name=?),0), ".
"IFNULL((SELECT id FROM shk_marketplace_player WHERE name=?),0), ?, ".
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
#mysqli_report(MYSQLI_REPORT_ALL);
if (mysqli_connect_errno()) {
print(mysqli_connect_error());
exit(0);
}
$stmt = $mysql->prepare($q);
if (!$stmt) {
exit(0);
}
$stmt->bind_param("isssiiiiiiiiiiiiiiiiiiiiiiii",
$id, $grafschaft,
$_POST["player"], $_POST["name"],
$_POST["Holz"], $_POST["Stein"], $_POST["Eisen"], $_POST["Pech"],
$_POST["_Ae_pfel"], $_POST["K_ae_se"], $_POST["Fleisch"], $_POST["Brot"], $_POST["Gem_ue_se"], $_POST["Fisch"],
$_POST["Bier"],
$_POST["Wild"], $_POST["M_oe_bel"], $_POST["Metallwaren"], $_POST["Gew_ae_nder"],
$_POST["Wein"], $_POST["Salz"], $_POST["Gew_ue_rze"], $_POST["Seide"],
$_POST["B_oe_gen"], $_POST["Piken"], $_POST["R_ue_stungen"], $_POST["Schwerter"], $_POST["Katapulte"]);
$data = json_decode(file_get_contents("requests.dat"));
$search = array("_ae_", "_oe_", "_ue_", "_Ae_", "_Oe_", "_Ue_", "_DD_", "_KM_", "_US_");
$replace = array("ä", "ö", "ü", "Ä", "Ö", "Ü", ":", ",", "_");
$resources = array( "Holz", "Stein", "Eisen", "Pech",
"_Ae_pfel", "K_ae_se", "Fleisch", "Brot", "Gem_ue_se", "Fisch",
"Bier",
"Wild", "M_oe_bel", "Metallwaren", "Gew_ae_nder", "Wein", "Salz", "Gew_ue_rze", "Seide",
"B_oe_gen", "Piken", "R_ue_stungen", "Schwerter", "Katapulte");
foreach ($data as $grafschaft => $val) {
foreach ($val as $id => $req) {
$_POST = array();
foreach ($req as $k => $v) {
$_POST[$k] = str_replace($search, $replace, $v);
}
foreach ($resources as $k => $v) {
if (!isset($_POST[$v]) || !is_numeric($_POST[$v]) || $_POST[$v] < 0) {
$_POST[$v] = 0;
}
}
if ($stmt->execute()) {
print("INSERT of '".$id."' sucessful!'n");
} else {
print("ERROR: INSERT of '".$id."' failed!'n".$stmt->error." (".$stmt->errno.")'n");
#var_dump($_POST["name"]);
}
}
}
$stmt->close();
$mysql->close();
?>
脚本应从文件中读取数据并将其写入数据库。示例文件内容：
{"Chemnitz":{"99853":{"name":"01","player":"King_Eik","Eisen":"50000","Pech":"50000"}}}
现在我得到这个结果：
ERROR: INSERT of '99853' failed!
Column 'village_name' cannot be null (1048)
插入'99853'成功！所以现在我的问题是为什么 PHP 或 SQL 将文件中的名称字段转换为数据库中的 NULL。$_POST["name"] 在语句执行之前和/或之后不是 NULL。
谁能帮我解决这个问题？
我已经用这段代码测试了很多，但总是出错......我在 SO 或其他地方也没有发现这样的问题:(
解决方案非常简单。
只是交换
$_POST = array();
跟
foreach ($resources as $k => $v) {
$_POST[$v] = 0;
}
在此之后，数据入到数据库中。我仍然不知道为什么它以某种方式删除了这个名字，但这已经不重要了:)
这是关于如何在没有所有"引用"问题的情况下开发查询并具有易于阅读的内容的评论。
如果将查询保存在文件中，则可以对其进行编辑，测试等。
然后使用"file_get_contents"加载到PHP的"$sql"变量中。
示例代码：
/*
* Developing SQL Queries
*
* If you keep them in a file then you can easily edit the SQL Query.
*
* Run in in an 'SQL' IDE and also easily run it in PHP by using
* 'file_get_contents' to load it into a PHP $variable.
*
* Advantages.
* It is easy to see what the structure is.
* It is easy to change and run.
* No issues with 'quotes'.
* It is easy 'post' on 'SO' and it be understandable :-/
*/
$sqlFilename = 'outstanding.sql';
$sql = file_get_contents($sqlFilename);
echo '<pre>';
echo $sql;
echo '</pre>';
// Do what you want with the SQL...
示例 SQL 文件...
select event.*, source.*
from event
join event_source
on event_source.event_id = event.id
and event.state in ('waitingForData', 'newRequest')
join source
on source.id = event_source.source_id
and source.sourceid = ?