ryanschefke Posted August 25, 2008 Share Posted August 25, 2008 Hello, What is the best query to use optimized for speed that will search a table for rows where the column value is contained in a block of text that is stored in another table? For example, if I am storing "Blue, Yellow, Purple, Green" in one large text value in the "color" column of table "Crayons", how do I SELECT the rows in the "Todays_crayons" table if the value in the "color" column matches any of the values in the "color" column (the text block) of the "Crayons" table? Thanks, Ryan Quote Link to comment https://forums.phpfreaks.com/topic/121170-solved-pull-rows-where-a-columns-value-is-in-a-paragraph/ Share on other sites More sharing options...
fenway Posted August 25, 2008 Share Posted August 25, 2008 Why are you storing it that way/ Quote Link to comment https://forums.phpfreaks.com/topic/121170-solved-pull-rows-where-a-columns-value-is-in-a-paragraph/#findComment-625233 Share on other sites More sharing options...
ryanschefke Posted August 26, 2008 Author Share Posted August 26, 2008 I don't have to store it that way. I don't know of anyway better. There are 195 countries so the user could have 195 entries in one row of a table. Is that how I should do it? I figured I couldn't store all the users filter in one record because the user could enter an unlimited number of entries and I would just be storing them in randomn columns. There are 52 states so I would likely have the same table with 52 columns ....but, how would I compare the entry in one row, say a table named "filter_countries", with a certain userID to a table called "records" that had a "country" column so I could match only the rows in the "country" column that were contained in the "filter_countries" table with that userID? Quote Link to comment https://forums.phpfreaks.com/topic/121170-solved-pull-rows-where-a-columns-value-is-in-a-paragraph/#findComment-625632 Share on other sites More sharing options...
ryanschefke Posted August 26, 2008 Author Share Posted August 26, 2008 Would it be better to store each of the users filters in a "filter_states" table as individual rows vs storing it as a text block then doing a WHERE IN statement? So, for example, I would have a table called "filter_states" with the following columns: userID filterState filterName row1 33 MI myFilter row2 33 OH myFilter row3 33 TX bobsFilter ... ... ... How would I compare that to another table that had a bunch of records with a column and I only wanted to display the states that the user had in the "filter_states" table with the filterName myFilter? Quote Link to comment https://forums.phpfreaks.com/topic/121170-solved-pull-rows-where-a-columns-value-is-in-a-paragraph/#findComment-625634 Share on other sites More sharing options...
fenway Posted August 26, 2008 Share Posted August 26, 2008 How would I compare that to another table that had a bunch of records with a column and I only wanted to display the states that the user had in the "filter_states" table with the filterName myFilter? You can simply INNER join these two tables. Quote Link to comment https://forums.phpfreaks.com/topic/121170-solved-pull-rows-where-a-columns-value-is-in-a-paragraph/#findComment-625980 Share on other sites More sharing options...
ryanschefke Posted August 26, 2008 Author Share Posted August 26, 2008 That worked! Quote Link to comment https://forums.phpfreaks.com/topic/121170-solved-pull-rows-where-a-columns-value-is-in-a-paragraph/#findComment-626428 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.