OR in a search query?

[dmccabe]

Can you use OR in a search query?

 

eg

 

$query = "SELECT * FROM `tbl_corpaccount` WHERE `accname` OR `accno` = $search* ORDER BY aid ASC";

 

 

[aschk]

Yes, but you'll need make sure your string is inside single quotes (') and the asterisk (*) symbol is invalid.

[dmccabe]

thanks.

 

The * symbol was my next question, how do I make it so if they type in "test" then it searches for "test*" (so anything beginning with test) ?

[dmccabe]

OK so I worked that bit out:

 

SELECT * FROM `tbl_corpaccount` WHERE `accname` OR `accno` LIKE 'Test%'

 

The only problem being that it wont work with the OR, however if I remove the OR and `accno` then it works, but I need it search both.

[aschk]

The answer is: using a LIKE clause with a % (which is an asterisk equivalent)

e.g.

SELECT * FROM `tbl_corpaccount` WHERE `accname` OR `accno` LIKE '$search%' ORDER BY aid ASC

 

I also noted another problem. Your logic is wrong.

"WHERE `accname` OR ..." will ALWAYS be true.

It needs to be "WHERE `accname` = 'blah' OR ..."

 

I have another question though, isn't `accno` going to be a number?

[dmccabe]

Ok here is current code as I think we are confusing matters:

 

$query = "SELECT * FROM `tbl_corpaccount` WHERE `accname` OR `accno` LIKE '%$search%' ORDER BY aid ASC";

 

This doesnt work when I put it direct in to SQL query in phpmyadmin it returns 0 results.

 

however if I remove the OR `accno` section it works perfectly?

 

 

[dmccabe]

ok this works:

 

SELECT * FROM `tbl_corpaccount` WHERE `accname` LIKE 'test%' OR `accno` LIKE 'test%'

 

[aschk]

Mmm, it seems a bit silly that you're checking 2 fields for the same information? I would have thought accno to be an INT or numeric type of some sort.

[dmccabe]

It's because there is one search box and I want them to be able to either search for the account name or account number (which is letters and numbers VARCHAR).

 

So it searches both fields so that they can search for either.

[aschk]

Ah fair enough, semantics mean a lot to me these days so I like to know what the "literal" representation of the column also has meaning about the data contained within.