RE: question on where statements - Todd Finney - com.mysql.lists.perl

4 messages in com.mysql.lists.perlRE: question on where statements

From	Sent On	Attachments
Todd Finney	17 Jun 2000 15:11
Nathan Poole	18 Jun 2000 05:26
Todd Finney	18 Jun 2000 11:40
Dan Busarow	18 Jun 2000 13:33

Subject:	RE: question on where statements
From:	Todd Finney (tfin...@boygenius.com)
Date:	06/18/2000 11:40:41 AM
List:	com.mysql.lists.perl

At 08:26 AM 6/18/00, Nathan Poole wrote:

Hey Tod,

Um, if you only want Cust # 15, just say 'WHERE customernumber=15' and that's it. Likewise, if you want to find the customer who's name is 15foo, you CAN use the LIKE statement, but bear in mind you'll get more than one record returned if their sister-company '15foo International' or something (please be gentle with the example :) ) is in the db because they both contain the string '15foo'...

That's what one would expect, but that's not what is happening. It would appear that the statement is matching on only the integer portion of $value, rather than the whole thing:

Perhaps a simpler example would help you.

mysql> select customerNumber, companyName from customers where customerNumber='55foo';

+----------------+---------------------+ | customerNumber | companyName | +----------------+---------------------+ | 55 | COMPANY, INC. | +----------------+---------------------+

This is not the behavior that I would expect, and from the sounds of your message, you would not expect it either.

When you use an OR statement in a where clause, you're saying "gimme a record that contains one of these things, OR one of these other things, I'm not so fussy" If both exist, they'll both get returned.

I am using an OR, but it's returning the wrong results. There is one customerNumber 55, and another record which contains the username 55foo. If I ask for customerNumber='55foo' OR username = '55foo', it should not return both rows. It should only return the row containing an actual match, which it is not:

mysql> select customerNumber, username, companyName from customers where customerNumber='55foo' OR username='55foo';

+----------------+----------+----------------------+ | customerNumber | username | companyName | +----------------+----------+----------------------+ | 55 | [username1] | COMPANY, INC | | 123 | 55foo | OTHER COMPANY | +----------------+----------+----------------------+

(This is general SQL stuff, not specific to MySQL. There's a fair bit of help for generic SQL stuff out there, email me if you have trouble finding any)

I don't believe that this is general SQL stuff, unless they're all broken this way. Is this 'broken'? Am I expecting the wrong thing?

Todd

-----Original Message----- From: Todd Finney [SMTP:tfin...@boygenius.com] Sent: Sunday, June 18, 2000 8:12 AM To: msql...@lists.mysql.com Subject: question on where statements

I've noticed some behavior that has me a little perplexed. I have a database table containing the columns:

customerNumber - mediumint(8) primary key not null companyName - varchar(128) username - varchar(24) email - varchar(128)

One of the customers is customerNumber '15'. Another customer's username is '15foo'. Customer 15foo appears in some row >15. If $value='15foo', and I call the following statement on it:

SELECT * FROM customers WHERE \ customerNumber = '$value' \ OR companyName LIKE '%$value%' \ OR username LIKE '%$value%' \ OR email LIKE '%$value%'

It will return both customer #15 and customer 15foo, rather than just the customer 15foo. I don't see any mention of this kind of behavior in the doc, although given the sheer volume of the documentation, there's a possibility that I'm just missing it. [No, I'm not complaining about the docs, they're great, just exhaustive :) ]. Is this the behavior that is expected, and is:

SELECT * FROM customers WHERE \ customerNumber LIKE '%$value%' \ OR companyName LIKE '%$value%' \ OR username LIKE '%$value%' \ OR email LIKE '%$value%'

a valid fix for this?

Thanks a bunch, Todd

--------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail msql...@lists.mysql.com

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail msql...@lists.mysql.com instead.