php - MySQL 匹配和不匹配组合
问题描述
我正在尝试为许可证验证创建条件语句。我有 3 个参数purchase code
,item id
并且website URL
. 我想检查它们是否一起或部分匹配我的数据库行中的数据,如果不存在则插入。所以我必须创建一个或多个 SQL 查询来执行此操作,但我不知道什么是最干净/有效的方法。看看代码本身,你就会明白我在做什么。
<?php
if (isset($_GET['purchasecode']) && isset($_GET['itemid']) && isset($_GET['website'])) {
$purchasecode = $mysqli->real_escape_string($_GET['purchasecode']);
$website = $mysqli->real_escape_string($_GET['website']);
$itemid = $mysqli->real_escape_string($_GET['itemid']);
require_once 'class-verify.php';
$access_token = 'MyAccessTokenHere';
$purchase = new EnvatoPurchaseCodeVerifier($access_token);
$verified = $purchase->verified($purchasecode);
// Verified that user have purchased one of our items
if ($verified) {
$item_id = $verified->item->id;
$item_name = $verified->item->name;
$buyer = $verified->buyer;
$license = $verified->license;
$amount = $verified->amount;
$sold_at = $verified->sold_at;
$supported_until = $verified->supported_until;
$query = $mysqli->query("SELECT * FROM PurchaseCodes WHERE Code='".$purchasecode."' AND ItemID='".$item_id ."' AND Website='".$website."'");
if (/* puchase code, item id and website URL exist/matches */) {
echo 'License is already active!';
} elseif (/* puchase code and item id matches but website URL is different */) {
echo 'License is already actived on another website!';
} elseif ($item_id != $itemid) {
echo 'This purchase code is for our another item';
}else {
// insert into database if not already exists
$mysqli->query("INSERT IGNORE INTO PurchaseCodes (Code, ItemID, Website) VALUES ('$purchasecode', '$item_id', '$website')");
echo 'License is successfully activated!';
}
} else {
echo 'Purchase code is invalid!';
}
}
?>
解决方案
也许像这样的 smt 会有所帮助:
$valid_license_sql = "SELECT
CASE WHEN Code='".$purchasecode."' THEN 1 ELSE 0 END as purchasecode,
CASE WHEN ItemID='".$item_id ."' THEN 1 ELSE 0 END as itemid,
CASE WHEN Website='".$website."' THEN 1 ELSE 0 END as website,
CASE WHEN Code='".$purchasecode."' AND ItemID='".$item_id ."' AND Website='".$website."' THEN 1 ELSE 0 END as license
FROM PurchaseCodes";
$query = $mysqli->query($valid_license_sql);
$proper= $query->fetch_assoc();
if ( $proper['license'] ) {
echo 'License is already active!';
} elseif ( $proper['purchasecode'] && $proper['itemid'] && !proper['website']) {
echo 'License is already actived on another website!';
} elseif ( !$proper['itemid'] ) {
echo 'This purchase code is for our another item';
}else {
// insert into database if not already exists
$mysqli->query("INSERT IGNORE INTO PurchaseCodes (Code, ItemID, Website) VALUES ('$purchasecode', '$item_id', '$website')");
echo 'License is successfully activated!';
}
如果表中有更多行,只需循环遍历结果集。
推荐阅读
- javascript - Ajax 请求未中止
- php - 遍历数组并按月分组价格,莫里斯图
- arrays - 使用 First Index Swift 在数组中重新排序数组
- python - 在python中将yyyy-dd-mm转换为yyyy-mm-dd
- c++ - 从 3.2 升级到 openCV 3.4.1 后出现分段错误
- node.js - TypeError:无法读取未定义节点js的属性'then'
- code-coverage - CYTHON:为 pyx 文件生成覆盖率
- c++ - 为什么 Qt 在信号和槽中为 lambda 表达式抛出错误?
- python - odoo 10 维护设备模块中的记录仅对管理员可用?
- ios - 如何将新添加的 UIWindow 保持在顶部