Get Rid of MySQL Duplicates with SELECT DISTINCT

Well, there you have it! How do you get rid of duplicate content showing up in your nicely crafted, database-driven tables? Use the DISTINCT clarifier in your SELECT statement.

There’s no need to say more about this easy way to delete doubled data, but an example is always nice for clarity. Let’s say you’re keeping track of all the animals with whom you’ve shared your abode. The database is a simple one that collects information about your pets, their names, the type of animals, birthdates, deaths, and the history you’ve shared.

Here’s an example table produced with a simple SELECT statement. A row of data is repeated in the table because of duplicate values in the database.

Duplicate Data

Query: SELECT `name`, `species`, `variety`, `history` FROM `pets` WHERE `history` = ‘found stray’

Here’s a table produced with a SELECT DISTINCT statement. Each row is distinct and the repeated row has quietly disappeared.

Distinct Data

Query: SELECT DISTINCT `name`, `species`, `variety`, `history` FROM `pets` WHERE `history` = ‘found stray’

How did the data for ‘Highway’ get in there twice? Someone probably re-entered his information that was previously entered at an earlier date. But, now that we know how to use the DISTINCT clarifier in a MySQL query, the duplicated data is not a problem for us in presenting the data onscreen.

Improve Your Skills with a $5 eBook from Packt

That’s right! Visit Packt’s $5 eBook Bonanza until January 3rd, 2014 to pick up some new skills. You’ll be able to get any eBook or Video from Packt for just $5. This includes their entire range of WordPress eBooks as well.

$5-ebooks
Packt Publishing offers more than 1,700 books and videos on a wide range of topics that you might be interested in. Here’s their shopping categories and the number of titles in each category: Continue reading Improve Your Skills with a $5 eBook from Packt

How to Change the Format of MySQL Dates Using PHP

Dates stored in MySQL databases are represented using the format ‘YYYY-MM-DD’. People don’t usually say they accomplished a task on 2013-01-01, so this format is not the most convenient. Often we need to convert the format of a date stored in a database into a more readable or more user-friendly format.

We can use PHP to re-format dates retrieved from a MySQL database using two functions, DateTime() and date_format().

Note: One requirement for the DateTime() function to work is that the date column in the database must be of the type datetime, not date. Even if the date column started life with the type date, it can be changed at any time. Just realize that dates will now be reported along with the time, which will be all zeroes in this case. To make the date look pretty again, use both functions as outlined below.

A warning is reported when a date column is of the type date when a function expects the type datetime. You might see something like “Warning: date_format() expects parameter 1 to be DateTime, string given” if this happens to you.

Once the dates are recorded as the type datetime, using the date_format() function of PHP will work. Going back to values that are already stored in a database and updating the type is ok, this doesn’t have to happen at the creation of the database. So, if you’re keeping track of dates and want to use a format other than something like “2013-01-01”, keep reading. Continue reading How to Change the Format of MySQL Dates Using PHP

Columbus Day Sale at Packt for 50% Off Ebooks!

Have you been thinking about picking up a copy of WP Theme Development Beginners Guide but just didn’t pull the trigger yet? If so, this is your lucky day! Packt still has a Columbus Day Sale going, so check it out.

This is Packt Publishing’s biggest sale ever. Their entire catalog of ebooks and videos are 50% off! It’s like a BOGO sale where you can Buy One Get One free. With nearly 1,800 books in the catalog there’s bound to be one or two titles that you’d like to add to your collection. I know I’m heading over there myself.

Packt offers books and training videos in these categories: Application Development, Big Data and Business Intelligence, CMS and eCommerce, Enterprise Products and Platforms, Game Development, Instant, Mobile Application Development, Networking and Servers, Virtualization and Cloud, and Web Development.

How about learning more about Drupal, Joomla!, jQuery, WordPress, VoIP, Moodle, or Oracle? Just visit Packt’s eBook Sale and use the code “COL50” at checkout for your 50% savings. Use that code as many times as you want. No need to wait for a Holiday sale to get a good deal, but you’ll have to hurry. The ebook and video sale lasts only through Thursday, 17Oct.