When working on retrieving data from a category column I ran into an issue of trying to retrieve data without duplicates. The problem can be solved quickly with php but the issue arises with extra overhead when using php’s
First I must warn a database should never really store all the values on a single row for each data set, but their are databases out their like this, so if you are managing one of these, the tips provided later on will help. The database should be broken up into the smallest chunks possible and foreign keys used as much as you can. The reason for this is to avoid an issue like this in the first place. Here is an example of the wrong database setup:
Wrong Database Setup
id First-Name Last-Name Category 1 Joe Bob Accountant 2 Mark Robinson President 3 Sarah June Accountant
As you can see above if you were to run a Mysql Query like:
SELECT * From table WHERE Category = 'Accountant'
You would return two rows for accountant. Now if you were looking for every account in the firm there would be no issue with this type of query. But What if you wanted to create a menu with the Categories? You would not want duplicate results to show up. Also imagine if their were over 2000 employees in the company. That is massive overhead that is not needed when simply searching for values that should be stored in a separate table. Below is the right Database Setup
Right Database Setup
id First-Name Last-Name Category_id 1 Joe Bob foreign_key1 2 Mark Robinson foreign_key2 3 Sarah June foreign_key1
Second Table – Unique Column
Category Accountant President Accountant
With the above setup you can simply reference the second table for things like dynamic menus and other advanced programming, but if you have a database like the “wrong database” then the below code will work perfectly for you!
SELECT * FROM tablename GROUP BY Category //(Column with duplicates)
This will return all the unique Categories from your database without any additional php coding. Please bare in mind. With smaller databases, this will work fine, but imagine a database with a million records? It would take forever to loop through every record to find the unique columns, where if you had just setup the database to use foreign keys to begin with, you would not run into any issues.
Depending on how you setup your second table, your database may need to also incorporate the use of left join. This is an article for another day, but the above code and setup should help you with what you need.