php - 尝试执行 CREATE 语句时出现 "Incorrect database name"错误

Question

所以我有这段代码可以从一个表中选择数据，将其提取到数组中并将其插入到另一个表中。一切正常，直到我尝试在步骤 5 中执行 CREATE 语句，这给了我这个错误:

不正确的数据库名称'EN'

我就像“什么？我的代码中没有提到‘EN’!”。
你看，我有点绝望，既然问也没什么坏处，我想我不妨把我的问题扔在这里。也许我很幸运，有人发现了什么，我不知道。
无论如何，谢谢!

我认为不需要整个代码，但我不想冒任何风险，所以这里是:

<?php
// ---------------------------------------------STEP 1: CONNECT TO DB---------------------------------------------
$host = "localhost";
$user = "****";
$pass = "****";
$db = "customerfinder";

$con = new mysqli($host, $user, $pass, $db);
if($con->connect_error)
{
    die("Connection failed (" . $con->connect_error . ")");
}

// ---------------------------------------------STEP 2: STORE COUNTRY CODES & NAMES---------------------------------------------
// Note: In the database exist rows like "Last Updated: 10/17/2014". The WHERE clause exists because of that.
$query = "SELECT DISTINCT Country_Code, Country_Name FROM TData WHERE Country_Code<>'' AND Country_Name<>''";
$result = $con->query($query);
// $countries will be used as an associative array with all country codes
$countries = array();

if($result->num_rows > 0)
{
    // Every row is fetched into an associative array
    while($row = $result->fetch_assoc())
    {
        $country_code = $row["Country_Code"];
        $country_name = $row["Country_Name"];
        // The first part of the array $countries is made.
        // At the moment, $countries looks like this: array("COUNTRY_CODE" => array("Name" => "COUNTRY_NAME"))
        $countries[$country_code] = array(
                "Name" => $country_name
        );
    }
}

// ---------------------------------------------STEP 3: STORE REMAINING VALUES---------------------------------------------
$query = "SELECT DISTINCT Country_Code, Series_Code, 2012_YR2012 FROM TData WHERE Country_Code<>'' AND Series_Code<>'' AND 2012_YR2012<>''";
$result = $con->query($query);
// $series_codes will be used as an indexed array which contains all the codes of 2012_YR2012.
// It will be used for creating dynamic INSERT statements.
$series_codes = array();
// $series_codes_first will be used as a string which stores the first series code
$series_codes_first = "";
// $series_codes_insert is a boolean which is set to false once the current code matches $series_codes_first,
// so the codes do not get stored multiple times
$series_codes_insert = true;
if($result->num_rows > 0)
{
    while($row = $result->fetch_assoc())
    {
        $country_code = $row["Country_Code"];
        $series_code = $row["Series_Code"];
        $series_value = $row["2012_YR2012"];
        // If $series_codes_first is equal to $series_code all the codes have been stored
        // and $series_codes_insert is set to false
        if($series_codes_first == $series_code)
        {
            $series_codes_insert = false;
        }
        // If $series_codes_insert is set to true the series codes are added to $series_codes
        if($series_codes_insert == true)
        {
            array_push($series_codes, $series_code);
        }
        // If $series_codes_first is empty it is the first loop and the first code gets stored to be checked later
        if($series_codes_first == "")
        {
            $series_codes_first = $series_code;
        }
        // The series values are added to $countries.
        // Now $countries looks like this: array("COUNTRY_CODE" => array("Name" => "COUNTRY_NAME", "SERIES_CODE" => "SERIES_VALUE"))
        $countries[$country_code][$series_code] = $series_value;
    }
}

// ---------------------------------------------STEP 4: CHECK (AND DROP) TCOUNTRIES---------------------------------------------
$drop_query = "DROP TABLE TCountries";
$drop_feedback;
$check_query = "SHOW TABLES LIKE 'TCountries'";
// If $result contains no rows, TCountries does not exist and $drop_query has not to be used
$result = $con->query($check_query);
if($result->num_rows == 0)
{
    $drop_feedback = "TCountries did not exist and was therefore not dropped!";
}
else
{
    if($con->query($drop_query))
    {
        $drop_feedback = "TCountries was successfully dropped!";
    }
    else
    {
        $drop_feedback = "An error occurred while trying to drop TCountries (" . $con->error . ")";
    }
}

// ---------------------------------------------STEP 5: CREATE TCOUNTRIES---------------------------------------------
$create_query = "CREATE TABLE TCountries(Country_Code VARCHAR(3) PRIMARY KEY, Country_Name VARCHAR(20)";
// The series codes are added to $create_query one after another
foreach($series_codes as $value)
{
    $create_query .= ", " . $value . " VARCHAR(15)";
}
$create_query .= ")";
$create_feedback;
if($con->query($create_query))
{
    $create_feedback = "TCountries was successfully created!";
}
else
{
    $create_feedback = "An error occurred while trying to create TCountries (" . $con->error . ")";
}

// ---------------------------------------------STEP 6: INSERT COUNTRIES---------------------------------------------
$insert_feedback = "";
// Every country is inserted separately
foreach($countries as $country_code => $country)
{
    $insert_query = "INSERT INTO TCountries VALUES('" . $country_code . "'";
    // The series values are added to $insert_query one after another
    foreach($country as $value)
    {
        $insert_query .= ", '" . $value . "'";
    }
    $insert_query .= ")";
    if($con->query($insert_query))
    {
        $insert_feedback .= "<br>Dataset was successfully inserted!";
    }
    else
    {
        $insert_feedback .= "<br>An error occurred while trying to insert the dataset (" . $con->error . ")";
    }
}

// ---------------------------------------------CHECK DATA---------------------------------------------
echo "<h2>List</h2>";
foreach($countries as $country_code => $country)
{
    echo "<b>" . $country_code . "</b><br>";
    foreach($country as $key => $value)
    {
        echo $key . ": " . $value . "<br>";
    }
    echo "</p>";
}

echo "<h2>JSON</h2>";
$countries_json = json_encode($countries, JSON_PRETTY_PRINT);
echo $countries_json;

echo "<h2>Array</h2>";
print_r($countries);

echo "<h3>\$series_codes</h3>";
print_r($series_codes);

echo "<h3>Query feedbacks</h3>";
echo $drop_feedback . "<br>";
echo $create_feedback;
echo $insert_feedback;
?>

Answer 1

点 . 在 mysql 中被认为是数据库/表/列之间的分隔符。即使连接到数据库 bar 而不是 foo，您也可以使用 SELECT * FROM foo.table。

您正在从另一个数据库动态创建列，因此如果这些名称可能包含点，请在查询中用反引号 (`) 将它们括起来以表示完整的列名:

$create_query .= ", `" . $value . "` VARCHAR(15)";
                    ^-- here       ^-- and here