Member Avatar for LTT

Ive been looking around and havent been able to find anything like this yet.. I am new to mySQL.

Im looking for a function like the "where" function of a query string.. but insted of it being exact i would like to select "where" datafield "contains" text..


example..

item is listed under multiple states
DB
id=1 name=apple state=FL, WI, CA
id=2 name=orange state=FL

when displaying results i would like to have apple come up when i select any of the states that it is listed under..

any questions?

Thanks,
-LTT

  • 7 Contributors
  • 13 Replies
  • 830 Views
  • 8 Months Discussion Span
  • Latest Post Latest Post by Molech

Dani AI

Generated

Short answer: stop storing multiple states inside one text field and represent the relation properly. A normalized many-to-many design (items, states, item_state) makes queries simple, lets the optimizer use indexes, and avoids fragile text-search hacks. Example schema and a straightforward lookup:

CREATE TABLE items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE states (
  code CHAR(2) PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE item_state (
  item_id INT NOT NULL,
  state_code CHAR(2) NOT NULL,
  PRIMARY KEY (item_id, state_code),
  FOREIGN KEY (item_id) REFERENCES items(id),
  FOREIGN KEY (state_code) REFERENCES states(code)
);

SELECT i.name
FROM items i
JOIN item_state s ON s.item_id = i.id
WHERE s.state_code = 'CA';

A normalized approach is the recommended pattern for many-to-many relationships and keeps queries fast and predictable. (digitalocean.com)

If you cannot change the schema right away, MySQL provides functions you can use as a stopgap: the built-in set/list helpers and JSON functions let you test membership inside a column. For example, MySQL has a function for searching comma lists, a dedicated SET column type (for small fixed lists), and JSON search functions for JSON arrays — each has tradeoffs in correctness and indexing. Use these only as temporary fixes. (dev.mysql.com)

Performance note: text-search on a delimited column (or wrapping the column in functions) usually prevents index use and can force full table scans. If schema change is impossible, consider adding generated (computed) columns that evaluate membership and index those generated columns so the optimizer can use an index. Test everything with EXPLAIN to confirm the plan. (dev.mysql.com)

Practical recommendation: follow the quick path only for small, read-light datasets. For real apps, normalize now (or add a proper junction table later). This builds on ’s idea that text matching works in a pinch and on ’s point about checking documentation — but with the above tradeoffs and indexing guidance in mind. (dev.mysql.com)

Recommended Answers

All 13 Replies

Member Avatar for almostbob 
select name where state like '%s', $post['state']
mysql_query(sprintf("select name where state like '%s', $post['state'] "));

probably better if you take the commas out of your state tables
wa fl az mi
instead of
wa, fl, az, mi,
many schema give commas a meaning, and cannot be sure where the data will be interpreted and parsed
the space character is a sufficient divider

%s , $post['state'] could be written as 
$post['state'] 
replaceable parameters that I need to know which ones are numeric / text / blob to debug,  sql %s is literal text
Member Avatar for verruckt24

: Rather than posting the exact queries as answer, why not just mention that MySQL contains something like 'LIKE' and then maybe point him towards the documentation page for the same and then let him do the further tasks, such as the syntax botheration for MySQL, PHP etc. Wouldn't the OP learn more things with this approach ?

Member Avatar for almostbob

most do not want to be told rtfmits easier for me its just another line of code, that I have in memory, references I have to waste my time looking up

Member Avatar for verruckt24

most do not want to be told - rtfm

Then such people aren't worthy of telling anything at all. Because this community attempts to nurture an environment that makes people learn while helping them. Giving away readymade answers just because someone would not like you telling him "go-read-the-manual", isn't the right approach.

And if you feel that your time is wasted, siting people references from which they can learn, then isn't not posting a good idea to consider ?

Member Avatar for ahmksssv

Then such people aren't worthy of telling anything at all. Because this community attempts to nurture an environment that makes people learn while helping them. Giving away readymade answers just because someone would not like you telling him "go-read-the-manual", isn't the right approach.

And if you feel that your time is wasted, siting people references from which they can learn, then isn't not posting a good idea to consider ?

:hi..if anybody post new thread in forums means, its urgent to them...i think pointing to some referance manual is waste of time....Learning depends on their own interest..no body can force them...ok coool....enjoy & learn something by using this forumm..

commented: that's to cool you down +0
Member Avatar for almostbob

Verruckt
the final arbitration of all takes up so much of your time.
It must be difficult to be you.

Member Avatar for verruckt24

Verruckt
the final arbitration of all takes up so much of your time.
It must be difficult to be you.

Hmm.. It's quite easy, I guess, to just retaliate with the first possible means than admitting your mistake. And however difficult it might seem to you, I like to suggest a few good things to people when I see them going wrong.

hi..if anybody post new thread in forums means, its urgent to them...i think pointing to some referance manual is waste of time....Learning depends on their own interest..no body can force them...ok coool....enjoy & learn something by using this forumm..

We are not here to solve people's problem on an urgent basis. If you can understand this is not a 24x7 support site. Go read the forum rules if you want to. And I am sure you might feel reference manuals siting are waste of time because people like you are so very ready to gulp down on readymade food. If the OP doesn't want to learn we won't help him thats all, thats the culture over here, which you might not know well just because you haven't stayed here that long. We cannot force this on the OP but we can certainly opt not to help such blood sucking leeches.

Member Avatar for jacobposey

Thank you for posting the examples. I looked at the manual and did not understand it. I like forums where people post their own examples and not just refer to "manuals" because it helps me learn more. Thanks for your help.

Member Avatar for almostbob

Thank you for posting the examples. I looked at the manual and did not understand it. I like forums where people post their own examples and not just refer to "manuals" because it helps me learn more. Thanks for your help.

Glad it all helped, examples stick in my mind better too

commented: Totally agree :) +11
Member Avatar for verruckt24

I looked at the manual and did not understand it. I like forums where people post their own examples and not just refer to "manuals"

Yeah why would newbies like you like to take any pains preparing your food, when it is thrown ready made at you, what you say is pretty obvious for your kinds.

And FYI the "referring to manuals" or any other information/resource thing that you are cribbing about is sort of the culture over here, go read the rules, they clearly say "we offer help only to those who show effort" and I afraid but the "effort" on your part and on the part of similar people like you is often severely lacking.

Member Avatar for nav33n

Thank you for posting the examples. I looked at the manual and did not understand it. I like forums where people post their own examples and not just refer to "manuals" because it helps me learn more. Thanks for your help.

I noticed, its your first post on Daniweb. Welcome to Daniweb! :)


P.S. Some people will never change. They are always whining.
:icon_rolleyes: Bah! Nevermind.

Member Avatar for verruckt24

P.S. Some people will never change. They are always whining.
Bah! Nevermind.

Suits me if the whining is about correct things, Which if you follow the thread you will come to know, but alas...you opted not to.

Member Avatar for Molech

I stumbled across this thread when searching for something unrelated, but I decided to register here just to say that verruckt24 is a faggot. thank you, have a nice day.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.