我正在搜索一个表,其中有25+列。其中两个是First Name和Last Name。我可以让CONCAT单独搜索它们,但当我添加其他OR选择时,我会得到错误。有人能给我指个方向吗?任何帮助都太好了。更多的细节:我正在想办法解决这个问题。我有两个不同的SELECT语句运行良好#1是
$query = "SELECT `CustomerID`, `CompanyName`, `ContactFirstName`, `ContactLastName`,
`BillingAddress`, `BillingAddress2`, `City`, `PostalCode`, `PhoneNumber`, `FaxNumber`, `EMail`
FROM `Customers` WHERE `CustomerID` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `CompanyName` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `ContactFirstName` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `ContactLastName` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `BillingAddress` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `BillingAddress2` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `PhoneNumber` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `EMail` LIKE '%".mysql_real_escape_string($search_text)."%'";
和#2是
SELECT *, CONCAT_WS(' ',ContactFirstName,ContactLastName) AS FullName FROM `Customers` HAVING
FullName LIKE '%".mysql_real_escape_string($search_text)."%'";
我一直不知道如何将第二个select添加到第一个select中。
——编辑在a1ex07的帖子后,我重新做了查询,它的工作方式,我正在寻找…
$query = "SELECT `CustomerID`, `CompanyName`, `ContactFirstName`, `ContactLastName`,
BillingAddress`, `BillingAddress2`, `City`, `PostalCode`, `PhoneNumber`, `FaxNumber`, `EMail`
FROM `Customers`
WHERE `CustomerID` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `CompanyName` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `BillingAddress` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `BillingAddress2` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `City` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `PostalCode` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `PhoneNumber` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `FaxNumber` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `EMail` LIKE '%".mysql_real_escape_string($search_text)."%'
OR CONCAT_WS(' ',ContactFirstName,ContactLastName) LIKE
'%".mysql_real_escape_string($search_text)."%'";
$query_run = mysql_query($query);
while ($query_row = mysql_fetch_assoc($query_run)) {
$CustomerID = $query_row['CustomerID'];
$CompanyName = $query_row['CompanyName'];
$ContactFirstName = $query_row['ContactFirstName'];
$ContactLastName = $query_row['ContactLastName'];
$BillingAddress = $query_row['BillingAddress'];
$BillingAddress2 = $query_row['BillingAddress2'];
$City = $query_row['City'];
$PostalCode = $query_row['PostalCode'];
$PhoneNumber = $query_row['PhoneNumber'];
$FaxNumber = $query_row['FaxNumber'];
$EMail = $query_row['EMail'];
echo $CustomerID.' '.$CompanyName.' '.$ContactFirstName.' '.$ContactLastName.'
'.$BillingAddress.' '.$BillingAddress2.' '.$City.' '.$PostalCode.' '.$PhoneNumber.'
'.$FaxNumber.' '.$EMail.'<br>';
它似乎在做我想要的,除非有办法让FullName
变量回来?
如果我理解得很好,您正试图将WHERE ... OR FullName LIKE ...
添加到第一个查询中。它失败是因为你不能在WHERE
中使用字段别名。你需要做WHERE ... OR CONCAT_WS(' ',ContactFirstName,ContactLastName) LIKE ....
:
它似乎在做我想要的,除非有一种方法来获得FullName变量回来?
添加到SELECT
:SELECT ..., CONCAT_WS(' ',ContactFirstName,ContactLastName) AS FullName
.
同样,问题是不能在WHERE
中使用别名。有一些方法可以处理它:
-
WHERE
:SELECT ..., CONCAT_WS(' ',ContactFirstName,ContactLastName) AS FullName WHERE ... OR CONCAT_WS(' ',ContactFirstName,ContactLastName)
-
HAVING
:SELECT ..., CONCAT_WS(' ',ContactFirstName,ContactLastName) AS FullName WHERE ... HAVING FullName ...
使用别名 - 使用派生表:
SELECT * FROM ( SELECT ..., CONCAT_WS(' ',ContactFirstName,ContactLastName) AS FullName
...)a
WHERE ... OR FullName ....
你的最终查询应该是这样的:
$query = "SELECT `CustomerID`, `CompanyName`, `ContactFirstName`, `ContactLastName`,
BillingAddress`, `BillingAddress2`, `City`, `PostalCode`,
`PhoneNumber`, `FaxNumber`, `EMail`,
CONCAT_WS(' ',ContactFirstName,ContactLastName) AS FullName
FROM `Customers`
WHERE `CustomerID` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `CompanyName` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `BillingAddress` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `BillingAddress2` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `City` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `PostalCode` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `PhoneNumber` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `FaxNumber` LIKE '%".mysql_real_escape_string($search_text)."%'
OR `EMail` LIKE '%".mysql_real_escape_string($search_text)."%'
OR CONCAT_WS(' ',ContactFirstName,ContactLastName) LIKE
'%".mysql_real_escape_string($search_text)."%'";
在#1上,如果在单引号后面加一个空格会怎么样?’";换句话说,你的sql不能一起运行,适当的空格