PHP SQL 语句不工作

Question

请客气一点，我不是 PHP 开发人员，也不太了解这门语言。

我有以下 SQL PHP 语句:

$SerializedPricingArray = serialize($PricingArray);

$signup_order = $i + 1;

$SQL_Statement = "SELECT IF (EXISTS (SELECT [p].[planname] FROM [package] [p] WHERE [p].[planid] = 10 AND [p].[planname] = '" . $json_post['pricestructure']['tld'][$i]['tld'] . "'), \n"
. "(UPDATE [package] SET [pricing] = '" . $SerializedPricingArray . "', [signup_order] = " . $signup_order . "), \n"
. "(INSERT INTO [package] ([planname], [description], [asset_html], [highlight], [planid], [showpackage], [pricing], [style], [automaticactivation], [allowdirectlink], "
. "[sendwelcome], [stockInfo], [emailTemplate], [bundledProducts], [advanced], [signup_order]) VALUES ('" . $json_post['pricestructure']['tld'][$i]['tld'] . "', '', '', "
. "0, 10, 1, '". $SerializedPricingArray . "', 0, 1, 0, 1, 'a:5:{s:12:\"stockEnabled\";i:0;s:14:\"availableStock\";i:0;s:7:\"soldOut\";i:0;s:13:\"acceptSoldOut\";i:0;s:14:\"showStockLevel\";i:0;}', "
. "35, 'a:3:{s:8:\"optional\";a:0:{}s:8:\"required\";a:0:{}s:7:\"coupons\";a:0:{}}', 'a:3:{s:19:\"autoInitiateTranfer\";s:1:\"1\";s:17:\"enableNamesuggest\";s:1:\"1\";s:14:\"maxNamesuggest\";s:1:\"5\";}', "
. $signup_order . ")));\n";

echo "Working on TLD " . $json_post['pricestructure']['tld'][$i]['tld'] . "\n";
$_NULL = mysqli_query($conn, $SQL_Statement);

当我运行代码时，它会执行循环，但不会对数据库执行任何操作。

我做错了什么？

编辑 1

这是上面代码生成的查询:

SELECT IF (EXISTS (SELECT p.`planname` FROM `package` p WHERE p.`planid` = 10 AND p.`planname` = 'com'), 
    UPDATE `package` SET 
        `pricing`='a:2:{s:7:"taxable";i:0;s:9:"pricedata";a:1:{i:0;a:5:{s:9:"registrar";s:4:"enom";i:1;a:5:{s:6:"period";s:1:"1";s:9:"period_id";s:1:"1";s:5:"price";s:5:"11.99";s:8:"transfer";s:5:"11.99";s:5:"renew";s:5:"11.99";}i:2;a:5:{s:6:"period";s:1:"2";s:9:"period_id";s:1:"2";s:5:"price";s:5:"23.98";s:8:"transfer";s:5:"23.98";s:5:"renew";s:5:"23.98";}i:5;a:5:{s:6:"period";s:1:"5";s:9:"period_id";s:1:"5";s:5:"price";s:5:"59.95";s:8:"transfer";s:5:"59.95";s:5:"renew";s:5:"59.95";}i:10;a:5:{s:6:"period";s:2:"10";s:9:"period_id";s:2:"10";s:5:"price";s:6:"119.90";s:8:"transfer";s:6:"119.90";s:5:"renew";s:6:"119.90";}}}}', 
        `signup_order`=1 
        WHERE `planid`=10 AND `planname`='COM', 
    INSERT INTO `package` (`planname`, `description`, `asset_html`, `highlight`, `planid`, `showpackage`, `pricing`, `style`, `automaticactivation`, `allowdirectlink`, `sendwelcome`, `stockInfo`, `emailTemplate`, `bundledProducts`, `advanced`, `signup_order`) 
    VALUES (
        'com', 
        '', 
        '', 
        0, 
        10, 
        1, 
        'a:2:{s:7:"taxable";i:0;s:9:"pricedata";a:1:{i:0;a:5:{s:9:"registrar";s:4:"enom";i:1;a:5:{s:6:"period";s:1:"1";s:9:"period_id";s:1:"1";s:5:"price";s:5:"11.99";s:8:"transfer";s:5:"11.99";s:5:"renew";s:5:"11.99";}i:2;a:5:{s:6:"period";s:1:"2";s:9:"period_id";s:1:"2";s:5:"price";s:5:"23.98";s:8:"transfer";s:5:"23.98";s:5:"renew";s:5:"23.98";}i:5;a:5:{s:6:"period";s:1:"5";s:9:"period_id";s:1:"5";s:5:"price";s:5:"59.95";s:8:"transfer";s:5:"59.95";s:5:"renew";s:5:"59.95";}i:10;a:5:{s:6:"period";s:2:"10";s:9:"period_id";s:2:"10";s:5:"price";s:6:"119.90";s:8:"transfer";s:6:"119.90";s:5:"renew";s:6:"119.90";}}}}', 
        0, 
        1, 
        0, 
        1, 
        'a:5:{s:12:"stockEnabled";i:0;s:14:"availableStock";i:0;s:7:"soldOut";i:0;s:13:"acceptSoldOut";i:0;s:14:"showStockLevel";i:0;}', 
        35, 
        'a:3:{s:8:"optional";a:0:{}s:8:"required";a:0:{}s:7:"coupons";a:0:{}}', 'a:3:{s:19:"autoInitiateTranfer";s:1:"1";s:17:"enableNamesuggest";s:1:"1";s:14:"maxNamesuggest";s:1:"5";}', 
        1
    )
);

编辑 2

我已将代码更改为以下内容:

$StockInfo = "a:5:{s:12:\"stockEnabled\";i:0;s:14:\"availableStock\";i:0;s:7:\"soldOut\";i:0;s:13:\"acceptSoldOut\";i:0;s:14:\"showStockLevel\";i:0;}";
$BundledProducts = "a:3:{s:8:\"optional\";a:0:{}s:8:\"required\";a:0:{}s:7:\"coupons\";a:0:{}}', 'a:3:{s:19:\"autoInitiateTranfer\";s:1:\"1\";s:17:\"enableNamesuggest\";s:1:\"1\";s:14:\"maxNamesuggest\";s:1:\"5\";}";

$SerializedPricingArray = serialize($PricingArray);

$signup_order = $i + 1;

$SQL_Statement = "INSERT INTO package (`planname`, `description`, `asset_html`, `highlight`, `planid`, `showpackage`, `pricing`, `style`, `automaticactivation`, `allowdirectlink`, `sendwelcome`, `stockInfo`, `emailTemplate`, `bundledProducts`, `advanced`, `signup_order`) VALUES ('" . $json_post['pricestructure']['tld'][$i]['tld'] . "', '', '', 0, 10, 1, '". $SerializedPricingArray . "', 0, 1, 0, 1, '" . $StockInfo . "', 35, '" . $BundledProducts . "', " . $signup_order . ") "
    . "ON DUPLICATE KEY UPDATE `package` SET `pricing` = VALUES(`pricing`), `signup_order` = VALUES(`signup_order`) WHERE `planid` = 10 AND `planname` = '" . $json_post['pricestructure']['tld'][$i]['tld'] . "';";

$result = mysql_query($conn, $SQL_Statement);

echo "Working on TLD " . $json_post['pricestructure']['tld'][$i]['tld'] . "\n";
echo $result . "\n\n";

当执行PHP页面时，它“看起来”遍历了所有458条记录，但是当我在MySQL上查看表时，只有原来的58条记录。

还有什么建议吗？

补充 1

这是 MySQL 的表结构:

+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
| planname            | varchar(45) | NO   |     | NULL    |                |
| description         | text        | NO   |     | NULL    |                |
| asset_html          | text        | NO   |     | NULL    |                |
| highlight           | text        | NO   |     | NULL    |                |
| planid              | int(11)     | NO   |     | 0       |                |
| showpackage         | smallint(6) | NO   |     | 1       |                |
| pricing             | text        | NO   |     | NULL    |                |
| style               | tinyint(4)  | YES  |     | 0       |                |
| automaticactivation | tinyint(4)  | YES  |     | 1       |                |
| allowdirectlink     | tinyint(4)  | NO   |     | 1       |                |
| sendwelcome         | tinyint(4)  | NO   |     | 1       |                |
| stockInfo           | text        | NO   |     | NULL    |                |
| emailTemplate       | smallint(5) | NO   |     | 0       |                |
| bundledProducts     | text        | NO   |     | NULL    |                |
| advanced            | text        | NO   |     | NULL    |                |
| signup_order        | int(11)     | NO   |     | 1       |                |
+---------------------+-------------+------+-----+---------+----------------+

Answer 1

我认为您的查询有一些错误。[] 在 SQL SERVER 中使用，对于 mysql 必须使用 ``

检查细节错误使用这个

// Check connection
if (mysqli_connect_errno()){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Perform a query, check for error
if (!mysqli_query($conn, $SQL_Statement);){
  echo("Error description: " . mysqli_error($con));
}

你的查询不正确首先阅读if语句sql This