like operator problem

[aeonfox]

Hi

I'm trying to work why the following paramter doesnt work properly. it seems to be ignoring the 'and' part of the query

i am using a mysql 5.1.41  server

 

$stmt = $mysqli ->prepare("select CandidateVotes,CandidateTitle, CandidatefirstName ,CandidateLastName,CandidateID from Candidate where ( CandidateFirstName like ? and  CandidateLastName like ?) order by CandidateVotes DESC LIMIT 0,?");

  $stmt->bind_param('sss', $code1,$code2,$noVotes);

 

$code1="%".$searchFirstName."%";

$code2="%".$searchLastName."%";

$stmt->execute();

$stmt->bind_result($candidateVotes,$candidateTitle,$candidateFirstName,$candidateLastName,$candidateID);

[Psycho]

What, exactly, do you mean by "it seems to be ignoring the 'and' part of the query". Is the query returning ALL records where the first name is a match regardless of whether the last name matches or not? OR, is it returning results where the first name matches or the last name matches?

 

However I suspect your problem is due to a type

$stmt = $mysqli ->prepare("select CandidateVotes,CandidateTitle, CandidatefirstName ,CandidateLastName,CandidateID from Candidate where ( CandidateFirstName like ? and  CandidateLastName like ?) order by CandidateVotes DESC LIMIT 0,?");

[aeonfox]

Hi This syntax error did not seem to affect the search results. I still have the same problem.

The query does not take into account the second term , the Candidatelastname, and returns the list based only on the first term

 

Cheers

Craig

[xyph]

Try using RLIKE instead

[aeonfox]

Thanks, that doesn't seem to fix the problem

 

I am getting a result based only on the first term, not one seems to use both search terms.

 

 

Cheers

Craig

[Drummin]

I believe you should quote any text other wise it is treating the "blankand" as part of the WHERE statement.  CAPS is also a good idea.  Hey maybe I'm wrong but that's what I've found.

CandidateFirstName LIKE "?" and  CandidateLastName LIKE "?"