[RESOLVED] question about a LIKE query

steamPunk

Hi there

I have a columnd called 'CT_Intitule' containing people's names formated like this :

SMITH John
JONES Bill
etc

there are also First Name and Family Name columns but the person who enters the users hasn't filled them all out

so i need to be able to find a user (who enters their First Name and Family Name into two separate form fields) in the "CT_Intitule" column

I'd like to do it like this :

$firstname=$_POST[firstname];
$surname=$_POST[surname];

"SELECT * FROM client WHERE ((CT_Intitule LIKE '%".$CT_Nom."%')AND(CT_Intitule LIKE '%".$CT_Prenom."%'))"

does that syntax look right to you ?

do i need to do $surname=strtoupper($_POST[surname]); or would the LIKE condition recognise that Smith==SMITH ?

thanks

laserlight

What's $CT_Nom and $CT_Prenom?

Also, note that string indices should be quoted, e.g., $_POST['firstname'].

steamPunk

rats ! i wrote that too fast

this is what it should look like

$firstname=$_POST['firstname'];
$surname=$_POST['surname'];

"SELECT * FROM client WHERE ((CT_Intitule LIKE '%".$firstname."%')AND(CT_Intitule LIKE '%".$surname."%'))"

laserlight

do i need to do $surname=strtoupper($_POST[surname]); or would the LIKE condition recognise that Smith==SMITH ?

Yes, since LIKE is case sensitive, as far as I can tell.

It looks like you might want to search for strtoupper($surname) . ' ' . $firstname instead of using LIKE with AND.

steamPunk

laserlight wrote:
Yes, since LIKE is case sensitive, as far as I can tell.

It looks like you might want to search for strtoupper($surname) . ' ' . $firstname instead of using LIKE with AND.

yes you're right there - but now the game has changed :/
the client wants to check using

surname AND (cellphone number OR tel number OR email)

to see if one of the pairs exist

so now i have another prob with telephone numbers - if the user enters his phone number in a 00 00 00 00 00 format (which is quite common here) then it won't be matched if the number in the db is in 0000000000 format using LIKE

at the moment my sql is like this

"SELECT * FROM client WHERE (CT_Intitule LIKE '%".strtoupper($CT_Nom)."%') AND (((CT_Telephone LIKE '%".$CT_Telephone."%')OR(CT_Telephone LIKE '%".$CT_TelPortable."%')) OR ((CT_TelPortable LIKE '%".$CT_Telephone."%')||(CT_TelPortable LIKE '%".$CT_TelPortable."%')) OR(CT_Email='".CT_Email."'))";
//
//

is there a way of telling the LIKE comparison to ignore the spaces in the phone numbers ?

steamPunk

simplified :

 

SELECT * FROM client WHERE (CT_Intitule LIKE '%Catoire%')AND ((CT_Telephone LIKE '%xxx%') OR (CT_Telephone LIKE '%06 07 47 85 33%') OR (CT_TelPortable LIKE '%xxx%') OR ( CT_TelPortable LIKE '%06 07 47 85 33%' ) OR (CT_Email = 'xxx'))

laserlight

if the user enters his phone number in a 00 00 00 00 00 format (which is quite common here) then it won't be matched if the number in the db is in 0000000000 format using LIKE

Remove the whitespace first (e.g., with preg_replace()), and then check for equality.

steamPunk

i'd thought of that - and it would work if the user enters 00 00 00 00 00 and the db number is in 0000000000 format

but if the user enters 0000000000 and the db number is in 00 00 00 00 00 format or in 0000 000 000 format then i can't use preg_replace()

maybe i should convert all db numbers to 0000000000 format ...

laserlight

maybe i should convert all db numbers to 0000000000 format ...

Yes, it would make things much easier if the format was consistent.

steamPunk

i have just read in the manual that it's possible to use a regex in a select

mysql> SELECT 'Monty!' REGEXP '.*';

but i haven't got the slightest idea how i would use that here, so I'm begging to think my best option is to update all the numbers to the correct format

steamPunk

in the end i went with a

ereg_replace("[^0-9]", "", $str);

and formated all the numbers on the db and all incoming tel numbers

so it's all just fine now

thanks for your help

🙂

laserlight

Use preg_replace() instead of ereg_replace(). The ereg family of functions is deprecated. The difference is minor:

$str = preg_replace('/\D/', '', $str);

steamPunk

you're a champ !

many thanks