marcbraulio Posted March 5, 2012 Share Posted March 5, 2012 Hello everyone, I am trying to select and return any rows that have a specific string in it. Let's say I have a column name "item_id" and in every row of that column I have values like so: row A: (11,22,44,34) row B: (12,56,78,98) If the user picks the number '22', I use the following SQL line to get the row in which the number '22' is contained. SELECT * FROM Persons WHERE City LIKE '%22%' and row A gets returned, just like it is suppose to. The problem is, if the user selects a single digit number like '2', both row A and row B gets returned because they both have values that contain the number '2' in it. Instance, row A has the number '22' and row B has the number '12' so both rows get returned when the number '2' is requested. I don't want that, I only want the row that has the exact number '2' or '34' or whatever number I request, to be returned. So to get around this problem, I am wrapping my numbers in letters like so: row A: (a11a,a22a,a44a,a34a) row B: (a12a,a56a,a78a,a98a,a2a) and requesting it like so: SELECT * FROM Persons WHERE City LIKE '%a22a%' My question is: Is there a better way to work out this logic? Quote Link to comment Share on other sites More sharing options...
requinix Posted March 5, 2012 Share Posted March 5, 2012 Yes: normalize your table. Create a second table dedicated to holding just a person and a city (specifically their IDs). person | city -------+----- A | 11 A | 22 A | 44 A | 34 B | 12 B | 56 B | 78 B | 98 Then you should find everything suddenly becomes a lot easier. Quote Link to comment Share on other sites More sharing options...
marcbraulio Posted March 6, 2012 Author Share Posted March 6, 2012 Yes: normalize your table. Create a second table dedicated to holding just a person and a city (specifically their IDs). person | city -------+----- A | 11 A | 22 A | 44 A | 34 B | 12 B | 56 B | 78 B | 98 Then you should find everything suddenly becomes a lot easier. What if I have a some what large amount of numbers and tables going across like: person | city | state | country | zipcode -------+----- A | 11 | New Jersey | USA | zipcode A | 22 | New Jersey | USA | zipcode A | 44 | New Jersey | USA | zipcode A | 34 | New Jersey | USA | zipcode A | 18 | New Jersey | USA | zipcode A | 23 | New Jersey | USA | zipcode A | 49 | New Jersey | USA | zipcode A | 34 | New Jersey | USA | zipcode A | 10 | New Jersey | USA | zipcode A | 32 | New Jersey | USA | zipcode A | 44 | New Jersey | USA | zipcode A | 34 | New Jersey | USA | zipcode A | 11 | New Jersey | USA | zipcode A | 22 | New Jersey | USA | zipcode A | 54 | New Jersey | USA | zipcode A | 64 | New Jersey | USA | zipcode A | 31 | New Jersey | USA | zipcode A | 52 | New Jersey | USA | zipcode A | 64 | New Jersey | USA | zipcode A | 14 | New Jersey | USA | zipcode B | 12 | New Jersey | USA | zipcode B | 56 | New York | USA | zipcode B | 78 | New York | USA | zipcode B | 98 Is it is really worth it to repeat everything just because of a number? I figure it would be more performance effective to have A | 11, 22, 44, 55, 56, 64, 34, 54 and so on... | New Jersey | USA | zipcode B | 3, 33, 45, 25, 53, 63, 32, 53 and so on... | New Jersey | USA | zipcode That way I only have two rows, but I am no performance expert. Please give me an insight on this. Quote Link to comment Share on other sites More sharing options...
requinix Posted March 6, 2012 Share Posted March 6, 2012 Why is the state/country/zip stuff being associated with the person? It belongs with the city information. Quote Link to comment Share on other sites More sharing options...
marcbraulio Posted March 6, 2012 Author Share Posted March 6, 2012 Why is the state/country/zip stuff being associated with the person? It belongs with the city information. Well honestly, state/country/zip or even persons have nothing to do with what I am doing. I used "persons" because it was easier to explain and I didn't think this would turn into a table structure issue. But seeing that this is in fact a table structure issue, here is the real scenario: I am building a module based CMS similar to Joomla for my personal use. The numbers on the rows (11, 21, 23, 31) for instance is actually referring to the "itemid" of the page. The link goes as follows: www.example.com/index.php?comp=article&view=category&id=34&itemid=22 The itemid is used by the modules as a reference of when to show up or when to hide. For instance if the page has an itemid=21 and I would like to assign it a "login module", "newsletter module", and "navigation module", I would assign all three of those modules the itemid of 21. And if I wanted to also assign those modules to another page, let's say a page with an itemid of 23, I would assign the three modules to the itemid number of 23, so when I query for "23" all those three modules would show up on that page. So I made the following table: module_id | module_name | module_title | module_status | module_position | module_item_id -------------+-------------------+----------------+--------------------+----------------------+--------------------- 1 | Nav | Main Menu | 1 | nav | 11, 21, 23, 31 But your suggesting that I make another table to reference the itemid to the module's id, correct? Like so: module_id | module_item_id -------------+--------------------- 1 | 11 1 | 21 2 | 23 2 | 31 2 | 44 2 | 56 I would really appreciate your opinion on this. Also if you have any other suggestions on the logic of making this module system work, please let me know. Quote Link to comment Share on other sites More sharing options...
requinix Posted March 6, 2012 Share Posted March 6, 2012 But your suggesting that I make another table to reference the itemid to the module's id, correct? Correct. Quote Link to comment Share on other sites More sharing options...
marcbraulio Posted March 6, 2012 Author Share Posted March 6, 2012 But your suggesting that I make another table to reference the itemid to the module's id, correct? Correct. Thank you! Quote Link to comment Share on other sites More sharing options...
xyph Posted March 6, 2012 Share Posted March 6, 2012 It may seem like it's over-complicating things now, but when it comes to using and manipulating the database you'll be happy you went the way of normalization. Once you start implementing JOINs, you'll understand Quote Link to comment Share on other sites More sharing options...
marcbraulio Posted March 6, 2012 Author Share Posted March 6, 2012 It may seem like it's over-complicating things now, but when it comes to using and manipulating the database you'll be happy you went the way of normalization. Once you start implementing JOINs, you'll understand Fair enough =]. If you have any preferred articles on database design best practices, please share. Once again thank you. Quote Link to comment Share on other sites More sharing options...
xyph Posted March 6, 2012 Share Posted March 6, 2012 Articles? There's BOOKS! http://www.agiledata.org/essays/dataNormalization.html There's a pretty good start, with visuals to help Quote Link to comment Share on other sites More sharing options...
marcbraulio Posted March 6, 2012 Author Share Posted March 6, 2012 Articles? There's BOOKS! http://www.agiledata.org/essays/dataNormalization.html There's a pretty good start, with visuals to help lol, got it! Quote Link to comment 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.