MySQL not return results when using =

[ragrim]

Hi,

 

Im having a wierd issue when building a search form i can get results using "like" or greater then less then but when ever i try to search with = i get no results, wether its a date field or a text field i get nothing, i tested the query in navicat and it works fine.

 

the $expiresquery is a variable from a combo box if the user wants to search for < , > , = or like all of them work except =

 

$query = "SELECT userinfo.User_ID, userinfo.FirstName, userinfo.LastName, userinfo.Phone
, userinfo.Mobile
, userinfo.Email2
, userinfo.Email 
, userinfo.PostCode  
, userinfo.Suburb  
, userinfo.Address
, userinfo.State
, userinfo.DOB   
, userinfo.SecondCardName 
, userinfo.BusinessName 
, userinfo.Concession
, userinfo.CreatedBy       
, userinfo.Proof
, subscribernumbers.SubNumber
, subtransactions.Expires 
, subtransactions.`SubType` 
, userinfo.Datecreated    
, userinfo.LastModified   
, subtransactions.RecieptNum  
, subtransactions.TransDate  
, subtransactions.PaymentType
, subtransactions.NewSubTrans
FROM  userinfo  Inner Join subscribernumbers ON userinfo.User_ID = subscribernumbers.FKUserID
INNER JOIN (SELECT FKSubNum, MAX(NewSubTrans) AS max_trans  FROM subtransactions GROUP  BY FKSubNum ) AS m  ON m.FKSubNum = subscribernumbers.SubNumber   
Inner Join subtransactions ON subscribernumbers.SubNumber = subtransactions.FKSubNum  AND subtransactions.NewSubTrans = m.max_trans";

$whereClause = " WHERE 1 = 1 ";
if($fname != 'ANY' && !empty($fname)) {
                $whereClause .= " AND FirstName " . $fnamequery . "'%$fname%' ";
}
if($lname != 'ANY' && !empty($lname)) {
                $whereClause .= " AND LastName LIKE '%$lname%' ";
}
if($address != 'ANY' && !empty($address)) {
                $whereClause .= " AND Address LIKE '%$address%' ";
}
if($suburb != 'ANY' && !empty($suburb)) {
                $whereClause .= " AND Suburb LIKE '%$suburb%' ";
}
if($po != 'ANY' && !empty($po)) {
                $whereClause .= " AND PostCode LIKE '%$po%' ";
}
if($businessname != 'ANY' && !empty($businessname)) {
                $whereClause .= " AND BusinessName LIKE '%$businessname%' ";
}
if($expires != 'ANY' && !empty($expires)) {
                $whereClause .= " AND Expires " . $expiresquery . "'%$expires%' ";
}

 

when i echo out the query i get

 

SELECT userinfo.User_ID, userinfo.FirstName, userinfo.LastName, userinfo.Phone , userinfo.Mobile , userinfo.Email2 , userinfo.Email , userinfo.PostCode , userinfo.Suburb , userinfo.Address , userinfo.State , userinfo.DOB , userinfo.SecondCardName , userinfo.BusinessName , userinfo.Concession , userinfo.CreatedBy , userinfo.Proof , subscribernumbers.SubNumber , subtransactions.Expires , subtransactions.`SubType` , userinfo.Datecreated , userinfo.LastModified , subtransactions.RecieptNum , subtransactions.TransDate , subtransactions.PaymentType , subtransactions.NewSubTrans 
FROM userinfo Inner Join subscribernumbers ON userinfo.User_ID = subscribernumbers.FKUserID
INNER JOIN (SELECT FKSubNum, MAX(NewSubTrans) AS max_trans FROM subtransactions GROUP BY FKSubNum )
AS m ON m.FKSubNum = subscribernumbers.SubNumber Inner Join subtransactions ON subscribernumbers.SubNumber = subtransactions.FKSubNum 
AND subtransactions.NewSubTrans = m.max_trans WHERE 1 = 1 AND Expires ='%2022-06-28%'

[PFMaBiSmAd]

The % are wild-cards that are only used in LIKE statements (and in the access control privilege settings.)

 

Using % in an = comparison would require that the data contain % characters for the comparison to return a true value.

[DanWhite]

WHERE 1 = '1'

 

You missed the apostrophes as well as PFMaBiSmAd's comment

[cpd]

WHERE 1 = '1'

 

You missed the apostrophes as well as PFMaBiSmAd's comment

 

You don't need apostrophes for integers; strings only.

[DanWhite]

Sorry, you're right. Brain fart.

[smoseley]

The % are wild-cards that are only used in LIKE statements (and in the access control privilege settings.)

 

Using % in an = comparison would require that the data contain % characters for the comparison to return a true value.

The first reply (quoted above) was the correct answer.... Get rid of the % on = comparisons and you'll be fine.