是我做错了什么吗?或者php不支持这个？

Question

我的问题是，当我试图通过php的mysql_query函数执行下面的查询时，它什么也不做(当然，除了这个查询之外，所有的东西都可以工作)。如果我将die(mysql_error())放在查询的末尾，它只会显示白色页面，而不会出现任何错误。另一方面，如果我尝试直接从mysql客户端执行它，那么sql代码就可以工作。我不知道到底出了什么问题。这是sql代码。

-- Instructions:  
-- Set the NPC Entry and stats you want it to have below.  
SET  
@NPC_ENTRY := ".$entry.", -- This is your NPC's Entry  
@NPC_HEALTH := ".$health.", -- This is the health value you want your NPC to have.  
@NPC_MANA := ".$mana.", -- This is the mana value you want your NPC to have.  
@NPC_ARMOR := ".$armor."; -- This is the armor value you want your NPC to have.

-- DO NOT CHANGE ANYTHING BELOW, UNLESS YOU KNOW WHAT YOU ARE DOING.  
-- Getting NPC datas:  
SET  
@NPC_CLASS := (SELECT `unit_class` FROM creature_template WHERE Entry = @NPC_ENTRY),  
@NPC_LEVEL := ROUND(((SELECT `minlevel` FROM creature_template WHERE Entry = @NPC_ENTRY)+  (SELECT `maxlevel` FROM creature_template WHERE Entry = @NPC_ENTRY))/2, 0),  
@EXP := (SELECT `exp` FROM creature_template WHERE Entry = @NPC_ENTRY);

-- Getting base HP from a HP column defined by exp.  
SET  
@GET_HP_COL :=  
(SELECT CASE @EXP  
WHEN 0 THEN (SELECT basehp0 FROM creature_classlevelstats WHERE `level` = @NPC_LEVEL and `class` = @NPC_CLASS)  
WHEN 1 THEN (SELECT basehp1 FROM creature_classlevelstats WHERE `level` = @NPC_LEVEL and `class` = @NPC_CLASS)  
WHEN 2 THEN (SELECT basehp2 FROM creature_classlevelstats WHERE `level` = @NPC_LEVEL and `class` = @NPC_CLASS)  
END),  
-- Getting base mana  
@GET_MA_COL := (SELECT basemana FROM creature_classlevelstats WHERE `level` = @NPC_LEVEL and `class` = @NPC_CLASS),  
-- Getting base armor  
@GET_AR_COL := (SELECT basearmor FROM creature_classlevelstats WHERE `level` = @NPC_LEVEL and `class` = @NPC_CLASS);

-- Running the update with all the data collected:  
UPDATE creature_template SET Health_mod = (@NPC_HEALTH/@GET_HP_COL), Mana_mod = (@NPC_MANA/@GET_MA_COL), Armor_mod = (@NPC_ARMOR/@GET_AR_COL) WHERE Entry = @NPC_ENTRY;

如果我的帖子有错误，我深表歉意。英语不是我的母语。:(

Answer 1

我怀疑问题可能出在这里：

SET  
@NPC_ENTRY := ".$entry.", -- This is your NPC's Entry  
@NPC_HEALTH := ".$health.", -- This is the health value you want your NPC to have.  
@NPC_MANA := ".$mana.", -- This is the mana value you want your NPC to have.  
@NPC_ARMOR := ".$armor."; -- This is the armor value you want your NPC to have.

您发布了一个大型MySQL查询，我想您可以通过对mysql_query的一次调用来调用它(同样，要注意弃用)。你怎么做到的？

SQL应该看起来像这样

@NPC_ENTRY := 11,  -- This is your NPC's Entry  
@NPC_HEALTH := 42, -- This is the health value you want your NPC to have.  
@NPC_MANA := 17, -- This is the mana value you want your NPC to have.  
@NPC_ARMOR := 33; -- This is the armor value you want your NPC to have.

如果是这样的话，你应该这样做：

$query1 = <<<SQL1
SET  
@NPC_ENTRY := {$entry}, -- This is your NPC's Entry  
@NPC_HEALTH := {$health}, -- This is the health value you want your NPC to have.  
@NPC_MANA := {$mana}, -- This is the mana value you want your NPC to have.  
@NPC_ARMOR := {$armor}; -- This is the armor value you want your NPC to have.
SQL1;

mysql_query($query1);

如果只是将查询放在更多的引号之间，最终会得到像.42这样的字段。而不是42，这会破坏SQL。

注释和多个查询也是如此:由于各种原因，一些后端不允许这样做(许多人认为一次只允许一个查询“更安全”，以避免某些类型的SQL攻击)。

由于这些原因，您可能希望将大型查询拆分到一个由单个SQL语句组成的数组中，并依次执行它们(可能是在事务中)。您还可以将注释移动到PHP：

$sqls = array(
    'BEGIN WORK',
    // Set the data
    "SET @NPC_ENTRY := {$entry}," // This is your NPC's Entry
      ."@NPC_HEALTH := {$health},"  // This is the health value you want your NPC to have.  
      ."@NPC_MANA := {$mana},"      // This is the mana value you want your NPC to have.  
      ."@NPC_ARMOR := {$armor};",   // This is the armor value you want your NPC to have.
    // DO NOT TOUCH BELOW THIS
    "SET @NPC_CLASS := ...",
    ...
    "END WORK;"
);

/* Or also like this; it is clearer and almost as efficient.

$sqls = array(
    'BEGIN WORK',
    // Set the data
    "SET @NPC_ENTRY  := {$entry};",   // Your NPC's Entry
    "SET @NPC_HEALTH := {$health};",  // health value you want your NPC to have.  
    "SET @NPC_MANA := {$mana};",      // mana value   " "
    "SET @NPC_ARMOR := {$armor};",    // armor value  " "
    // DO NOT TOUCH BELOW THIS
    "SET @NPC_CLASS := ...;",
    ...
    "END WORK;"
);

*/


unset($error);
foreach($sqls as $sql) {
    if (!mysql_query($sql)) {
        $error = // get MySQL last error
        break;
    }
}
if (isset($error)) {
    ...do something...
}