Back To Programming / Scripting / Coding Forum

MySQL query inquery

  • ScottG
  • Proficient
  • Proficient
  • No Avatar
  • Joined: Jul 06, 2010
  • Posts: 266
  • Status: Offline

Post April 10th, 2012, 11:42 am

OK so I've been working on an image archive system (redevelopment) and the issue I'm having is that people are having search issues. Now there are some things about this one it that the keywords that are attached have to be added into the file's db record and not an id relating to the keyword (Not my style but this is how it has to be). The reasoning behind this it that if the keyword gets deleted they still want to be able to find the file with that keyword (again not my style I don't delete anything from the database).

So here is an example table dumbed down to the keywords column
MYSQL Code: [ Select ]
Files Table
+----------+----------------------------------------+
| id       | keywords                               |
+----------+----------------------------------------+
| 1        | ["Test","test 2","test 3"]             |
| 2        | ["team","tetris"]                      |
| 3        | ["Test", "te"]                         |
| 4        | ["te"]                                 |
 
  1. Files Table
  2. +----------+----------------------------------------+
  3. | id       | keywords                               |
  4. +----------+----------------------------------------+
  5. | 1        | ["Test","test 2","test 3"]             |
  6. | 2        | ["team","tetris"]                      |
  7. | 3        | ["Test", "te"]                         |
  8. | 4        | ["te"]                                 |
  9.  

So If I ran a query like
MYSQL Code: [ Select ]
SELECT `files`.`id`
FROM `files`
WHERE `files`.`keywords` LIKE '%te%'
 
  1. SELECT `files`.`id`
  2. FROM `files`
  3. WHERE `files`.`keywords` LIKE '%te%'
  4.  

I would get all three of the results in the example table above when all I would want is record 3 and 4. The keywords column is a text column. so doing a sql query like
MYSQL Code: [ Select ]
SELECT `files`.`id`
FROM `files`
WHERE `files`.`keywords` LIKE 'te'
 
OR like
 
SELECT `files`.`id`
FROM `files`
WHERE `files`.`keywords` = 'te'
 
  1. SELECT `files`.`id`
  2. FROM `files`
  3. WHERE `files`.`keywords` LIKE 'te'
  4.  
  5. OR like
  6.  
  7. SELECT `files`.`id`
  8. FROM `files`
  9. WHERE `files`.`keywords` = 'te'
  10.  

would return no results since it is in a text based array format. What I would need to do is have a query find exactly te in the results. I could do a query to find the first set then process the keywords in php and match against the search to find the exact match but would prefer not to have php process it and have Mysql return the correct result set.

So it anyone has any thoughts on this please let me know, and feel free to ask any questions you may have
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post April 10th, 2012, 11:42 am

  • ScottG
  • Proficient
  • Proficient
  • No Avatar
  • Joined: Jul 06, 2010
  • Posts: 266
  • Status: Offline

Post April 10th, 2012, 2:56 pm

OK. So after much deliberation with the client and several failed MySQL attempts I am going to the continue to have the keywords attached to the file as well as go with how I do database structure and use a many to one relationship table. This will make it able to have an exact match for a search as well as using said attached keywords to just do a wild card search so I do not have to join to the relationship table when doing a wild card search and maintain backwards compatibility.
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Joined: Dec 20, 2002
  • Posts: 8925
  • Loc: Seattle, WA & Phoenix, AZ
  • Status: Online

Post April 11th, 2012, 9:29 am

Hi Scott,

Are you restricted at only using that database structure? The reason I ask is that I think the ideal way to do this would be to create a keywords table and associate the keywords with the file ID. So you would still have your files table, but you would no longer put keywords in them. Instead you would create a new table called keywords with a structure similar to:

MYSQL Code: [ Select ]
Keywords Table
+----------+----------------------------------------+
| file_id  | keyword                                |
+----------+----------------------------------------+
| 1        | Test                                   |
| 1        | test 2                                 |
| 1        | test 3                                 |
| 2        | team                                   |
| 2        | tetris                                 |
| 3        | Test                                   |
| 3        | te                                     |
| 4        | te                                     |
  1. Keywords Table
  2. +----------+----------------------------------------+
  3. | file_id  | keyword                                |
  4. +----------+----------------------------------------+
  5. | 1        | Test                                   |
  6. | 1        | test 2                                 |
  7. | 1        | test 3                                 |
  8. | 2        | team                                   |
  9. | 2        | tetris                                 |
  10. | 3        | Test                                   |
  11. | 3        | te                                     |
  12. | 4        | te                                     |


So basically you would list every keyword individually and associate it with the file_id that has it. Then when you do a MySQL query it will let you know exactly what file ids have an exact match on a keyword. For example:

MYSQL Code: [ Select ]
SELECT f.id
FROM files f, keywords k
WHERE f.id = k.file_id
AND k.keyword = 'te'
  1. SELECT f.id
  2. FROM files f, keywords k
  3. WHERE f.id = k.file_id
  4. AND k.keyword = 'te'


That would return file id 3 and 4.

You could also go one step further to prevent keywords from being duplicated over and over in this table and make another keyword table that lists each unique keyword and assigns each one a keyword_id. Then with the table above you would put the keyword_id and the file_id.

With these you will want to make sure you use indexes to speed up query times.

Hope this helps a bit :)
Ozzu Hosting - Want your website on a fast server like Ozzu?
  • ScottG
  • Proficient
  • Proficient
  • No Avatar
  • Joined: Jul 06, 2010
  • Posts: 266
  • Status: Offline

Post April 11th, 2012, 10:26 am

I am restricted to that data base structure and i do agree fully with your solution that's how I personally would have done it but I'm rebuilding a system that someone else made and it has been in use for sometime and there are over 100,000 files attached to the system with just as many database records if not more to make things even worse there is also a keywords table which basically duplicates the keywords not only to the files but the keywords themselves. It really is a mess and I've tried to talk them into a full Database redesign but they want to keep the same structure to maintain backward compatibility and so if something goes wrong the can flip back to the old system that currently works, really slow but works.

Here is an example of what I'm dealing with
MYSQL Code: [ Select ]
 
Category Table
+----------+---------------+------------------------+
| ID       | busorgID      | display_name           |
+----------+----------------------------------------+
| 1        | 1             | cat 1                  |
| 2        | 1             | cat 2                  |
| 3        | 3             | cat 1                  |
| 4        | 2             | cat 1                  |
| 5        | 3             | cat 2                  |
| 6        | 3             | cat 3                  |
| 7        | 1             | cat 3                  |
 
Keywords Table
+----------+---------------+----------------+-----------+
| ID       | busorgID      | keyword        | parentID  |
+----------+--------------------------------------------+
| 1        | 1             | Test           | 1         |
| 2        | 1             | test 2         | 2         |
| 3        | 1             | test 3         | 7         |
| 4        | 1             | team           | 7         |
| 5        | 1             | tetris         | 7         |
| 6        | 1             | Test           | 1         |
| 7        | 1             | te             | 2         |
| 8        | 1             | te             | 2         |
| 9        | 2             | tetris         | 2         |
| 10       | 2             | Test           | 2         |
| 11       | 2             | te             | 2         |
| 12       | 2             | te             | 5         |
| 13       | 3             | Test           | 3         |
| 14       | 3             | te             | 3         |
| 15       | 3             | te             | 3         |
| 16       | 3             | Test           | 5         |
| 17       | 3             | te             | 5         |
| 18       | 3             | te             | 6         |
 
 
  1.  
  2. Category Table
  3. +----------+---------------+------------------------+
  4. | ID       | busorgID      | display_name           |
  5. +----------+----------------------------------------+
  6. | 1        | 1             | cat 1                  |
  7. | 2        | 1             | cat 2                  |
  8. | 3        | 3             | cat 1                  |
  9. | 4        | 2             | cat 1                  |
  10. | 5        | 3             | cat 2                  |
  11. | 6        | 3             | cat 3                  |
  12. | 7        | 1             | cat 3                  |
  13.  
  14. Keywords Table
  15. +----------+---------------+----------------+-----------+
  16. | ID       | busorgID      | keyword        | parentID  |
  17. +----------+--------------------------------------------+
  18. | 1        | 1             | Test           | 1         |
  19. | 2        | 1             | test 2         | 2         |
  20. | 3        | 1             | test 3         | 7         |
  21. | 4        | 1             | team           | 7         |
  22. | 5        | 1             | tetris         | 7         |
  23. | 6        | 1             | Test           | 1         |
  24. | 7        | 1             | te             | 2         |
  25. | 8        | 1             | te             | 2         |
  26. | 9        | 2             | tetris         | 2         |
  27. | 10       | 2             | Test           | 2         |
  28. | 11       | 2             | te             | 2         |
  29. | 12       | 2             | te             | 5         |
  30. | 13       | 3             | Test           | 3         |
  31. | 14       | 3             | te             | 3         |
  32. | 15       | 3             | te             | 3         |
  33. | 16       | 3             | Test           | 5         |
  34. | 17       | 3             | te             | 5         |
  35. | 18       | 3             | te             | 6         |
  36.  
  37.  


As you can see horribly bad Database design and its frustrating because they won't let me redesign or change the tables

so my solution is something similar to what I would have done and you suggested

MYSQL Code: [ Select ]
 
Keyword Relations Table
+------------+---------------+----------------+
| keyword_id | file_id       | rating         |
+------------+--------------------------------+
| 1          | 1             | 5              |
| 2          | 1             | 4              |
| 3          | 1             | 3              |
| 4          | 2             | 5              |
| 5          | 2             | 5              |
| 6          | 2             | 3              |
| 7          | 3             | 2              |
| 8          | 3             | 1              |
 
 
  1.  
  2. Keyword Relations Table
  3. +------------+---------------+----------------+
  4. | keyword_id | file_id       | rating         |
  5. +------------+--------------------------------+
  6. | 1          | 1             | 5              |
  7. | 2          | 1             | 4              |
  8. | 3          | 1             | 3              |
  9. | 4          | 2             | 5              |
  10. | 5          | 2             | 5              |
  11. | 6          | 2             | 3              |
  12. | 7          | 3             | 2              |
  13. | 8          | 3             | 1              |
  14.  
  15.  


The rating is just a sorter flag that the user base the keywords relevance to the picture.
  • ScottG
  • Proficient
  • Proficient
  • No Avatar
  • Joined: Jul 06, 2010
  • Posts: 266
  • Status: Offline

Post April 11th, 2012, 10:58 am

I've been writing all the new code to be simply changed so that when the Database dose get overloaded and it will I can change over with ease to a database structure like I would use and then write some php to transfer info from the old database to a new one.

Page 1 of 1

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: Bigwebmaster and 198 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.