Jump to content


Photo

Query for something in Text


  • Please log in to reply
4 replies to this topic

#1 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 06 April 2006 - 08:20 PM

Hi,

I have a field in my database that is a text type and inside the fields the values are stored like:

Model | PNS
--------------------------------------
Item1 | C1,C2,C4,C10,C34,C35,C36
--------------------------------------
Item2 | C1,C4,C34,C55,C56,C57
--------------------------------------
Item3 | C1,C4,C56,C59,C98
--------------------------------------

I have SELECT * FROM parts WHERE PNS = ????? How do I just make a query to look for the C34?



#2 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 06 April 2006 - 08:22 PM

thats definitely not the most efficient way of storing this information, but you can use

SELECT * FROM parts WHERE PNS LIKE '%C34%'

consider looking into the "SET" type to store the information if possible. It looks to me like you just want to store a list of information and sorting through text in the method I show above is not very efficient.

#3 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 06 April 2006 - 08:29 PM

[!--quoteo(post=362351:date=Apr 6 2006, 01:22 PM:name=jworisek)--][div class=\'quotetop\']QUOTE(jworisek @ Apr 6 2006, 01:22 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
thats definitely not the most efficient way of storing this information, but you can use

SELECT * FROM parts WHERE PNS LIKE '%C34%'
[/quote]

Yea I do realize that is note totally efficient. But I have like 55 items that point to the same Description but have a different REF_PN

So all those in the Item1 are the same part, but in a different location.

I could change it so that C14, C15, C28, C29 etc are all seperate items in the database, but it seems silly because the only difference between those 4 parts is the Ref_pn.

If I use LIKE that will also pull out things like C345, C345, CC34 too wont it?

Though any suggestions on how I should store thses values would be grealty appreciated

Something like this pershaps?
id | Ref_pn | Description | TR-PN
------------------------------------------------
1 | C3 | Cap 10uF 10V 10% | 25-000004-00
------------------------------------------------
2 | C5 | Cap 10uF 10V 10% | 25-000004-00
------------------------------------------------
3 | C6 | Cap 0.1uF 12V 5% | 25-000005-01
------------------------------------------------

If that helps :)


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 April 2006 - 09:33 PM

There's no question that these should be represented as different DB records; however, for the time being, you can use the following query (UNTESTED) to returning proper matches (will match C34, not C345):

SELECT PNS, LOCATE( ',C34,', CONCAT(',',PNS,',') ) AS matchPos FROM parts HAVING matchPos > 0

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 SharkBait

SharkBait
  • Members
  • PipPipPip
  • Advanced Member
  • 845 posts
  • LocationMetro Vancouver, BC

Posted 07 April 2006 - 12:08 AM

[!--quoteo(post=362372:date=Apr 6 2006, 02:33 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 6 2006, 02:33 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
There's no question that these should be represented as different DB records; however, for the time being, you can use the following query (UNTESTED) to returning proper matches (will match C34, not C345):

SELECT PNS, LOCATE( ',C34,', CONCAT(',',PNS,',') ) AS matchPos FROM parts HAVING matchPos > 0
[/quote]

I did the proper way and put them into their own database and their own items and link 2 other tables to it.

Thanks :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users