Loading Posts...

How to Remove Mysql Duplicates From Returned Array Without PHP

Remove Mysql Duplicates without php
Remove Mysql Duplicates without php

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

array_unique();

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.

0
HeartHeart
0
HahaHaha
0
LoveLove
0
WowWow
0
YayYay
0
SadSad
0
AngryAngry
0
CrapCrap
Voted Thanks!

DEVPRO

CIO at a Large Private Company, Web Developer, Web Designer,

Leave a Comment

Loading Posts...