mysqli预处理语句字符串被NULL替换

Question

I have some problems with the following code.

<?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!
");
        } else {
            print("ERROR: INSERT of '".$id."' failed!
".$stmt->error." (".$stmt->errno.")
");
            #var_dump($_POST["name"]);
        }
    }
}

$stmt->close();
$mysql->close();






?>

The script should read data from a file and write it into a database. Example file content:

{"Chemnitz":{"99853":{"name":"01","player":"King_Eik","Eisen":"50000","Pech":"50000"}}}

Now I get this as a result:

ERROR: INSERT of '99853' failed!
Column 'village_name' cannot be null (1048)

It was expected to get "INSERT of '99853' sucessful!" as result. So now my question is why PHP or SQL converts the name-field in the file to NULL in the database. $_POST["name"] is not NULL before and/or after the execution of the statement.

Can anyone help me with this?

I already tested a lot with this code but always something went wrong... I also found nothing about a problem like this on SO or somewhere else :(

Answer 1

The solution is pretty simple.

Just exchange

$_POST = array();

with

foreach ($resources as $k => $v) {
    $_POST[$v] = 0;
}

After this the data is inserted into the DB. I still don't know why it somehow deleted the name but it does not matter any longer :)