Jump to content


Photo

combining IN and LIKE ??


  • Please log in to reply
3 replies to this topic

#1 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 04 June 2006 - 10:33 PM

Having spent the whole day reading up about database coding - is it possible with MySQL to combine the 'LIKE' and the 'IN' statements in a selection? This would get me part way to where I am wanting to be, but I can't find any references to syntax like this.

So if my event codes have a first part to express type of event, and a second part to show specific event, my fake code needs to run something like:
WHERE pe.people_id <> '' 
AND lk.code_id IN ( 
(LIKE 'wshop_%'), 
(LIKE 'seminar_%'), 
(LIKE 'lect_%' ) )
AND lk.code_id <> 'lect_london'
... and this might get me 'everyone who has been to any workshop or seminar or lecture, but not the London lecture'

This might save a bundle of separate statements, if it is possible to do something of this kind! Thank you....

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 04 June 2006 - 11:02 PM

Your code structure combining eventtype_location seems to be you main problem. Why not have different columns and make life easier for yourself?

WHERE type IN ('wshop', 'seminar')
OR (type = 'lect' AND location <> 'london)

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 04 June 2006 - 11:11 PM

Thank you for your reply - yes, I was uneasy about the way this is put together - I'm sure splitting the data would solve all sorts of things, but the way that the client wants to be able to run searches on the database made me build it that way, plus the need to make all the drop-down selects etc. manageable for me....

I think that I will have to do just as you say in the end - it would also avoid the trauma of my other question on the board at the minute. But it would be interesting to know if there is a way of combining the syntax in this way.


[!--quoteo(post=380062:date=Jun 4 2006, 06:02 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 4 2006, 06:02 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Your code structure combining eventtype_location seems to be you main problem. Why not have different columns and make life easier for yourself?

WHERE type IN ('wshop', 'seminar')
OR (type = 'lect' AND location <> 'london)
[/quote]


#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 04 June 2006 - 11:28 PM

[!--quoteo(post=380067:date=Jun 5 2006, 12:11 AM:name=erewash)--][div class=\'quotetop\']QUOTE(erewash @ Jun 5 2006, 12:11 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
...But it would be interesting to know if there is a way of combining the syntax in this way.
[/quote]

No, you can't combine IN and LIKE

a IN ('x', 'y', 'z')

is an alternative to

(a = 'x') OR (a = 'y') OR (a = 'z')

Where you have

id
-----------
code1xxx
code1zzz
code2yyy
code3ccc

You could use

WHERE SUBSTRING(id, 1, 5) IN ('code1', 'code2')

as an alternative to

WHERE (id LIKE 'code1%') OR (id LIKE 'code2%')

but that wouldn't work with 'lect_xxx', 'seminar_xxx', 'wshop_xxx' because of the different lengths of the first part of the code.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users