我有一個包含兩個欄位的資料庫插入表單：站點名稱、URL。
在插入資料庫之前，我想檢查相關站點是否不存在（通過檢查名稱或 URL 是否不存在）。問題是名稱可以不同（大寫，小寫，...），以及 URL。
最好的解決方案是什么？這是我當前的代碼（不作業）：
<?php
// Database connection
$nameCompetitor = strtolower($_POST['nameSite']);
$urlCompetitor = parse_url('http://' . str_replace(array('https://', 'http://'), '', $_POST['url']), PHP_URL_HOST);
$reqCompetitorExist = "SELECT id_site, name_site, url FROM competitor_site WHERE LOWER(name_site) = \"".$nameCompetitor."\" OR url = \"".$urlCompetitor."\"";
$resCompetitorExist = mysqli_query($conn, $reqCompetitorExist) or die (mysqli_error($conn));
$competitorExist = mysqli_num_rows($resCompetitorExist);
$competitorData = mysqli_fetch_assoc($resCompetitorExist);
if ($competitorExist == 0) // Doesn't exist
{
// INSERT OK
}
else // Exists
{
echo "KO
<ul>
<li>ID:".$competitorData['id_site']."</li>
<li>Name:".$competitorData['name_site']."</li>
<li>URL:".$competitorData['url']."</li>
</ul>";
}
?>
uj5u.com熱心網友回復：
您可以通過 LIKE 更改查詢的“=”，它已經忽略了字符的大寫差異。
我還為您的腳本添加了 SQL 注入保護。
<?php
// Database connection
$nameCompetitor = str_replace("'","",$_POST['nameSite']);
$urlCompetitor = parse_url('//' . str_replace(array('https://', 'http://'), '', str_replace("'","",$_POST['url'])), PHP_URL_HOST);
$reqCompetitorExist = "SELECT id_site, name_site, url FROM competitor_site WHERE name_site LIKE '".$nameCompetitor."' OR url LIKE '".$urlCompetitor."'";
$resCompetitorExist = mysqli_query($conn, $reqCompetitorExist) or die (mysqli_error($conn));
$competitorExist = mysqli_num_rows($resCompetitorExist);
$competitorData = mysqli_fetch_assoc($resCompetitorExist);
if ($competitorExist == 0) // Doesn't exist
{
// INSERT OK
}
else // Exists
{
echo "KO
<ul>
<li>ID:".$competitorData['id_site']."</li>
<li>Name:".$competitorData['name_site']."</li>
<li>URL:".$competitorData['url']."</li>
</ul>";
}
?>
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/398930.html
上一篇：Laravel8EloquentManyToMany插入資料透視表不起作用
下一篇：用PHP計算英國母親節