Category: mysql

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.

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

Learning Code Resources to Beat the Summer Heat

People love the summertime with all its heat and sunshine. But not everyone likes to bask in the sun, so instead they turn to cooler activities than baking their skin to a crisp. Count me in as one of those people who hibernate away from the heat or in a word, estivate.

What can we do to avoid the scorching sun in the middle of the day? We could take a nap to avoid the heat, or go to a movie or take the time to learn something cool. How about learning to code? It’s not that hard to pick up a new tip or trick, you just have to want to. With that, here’s a few resources to get you started on your way to learning something new this summer.

Free Online Learning Tools

  • CodeAcademy– sign up for an account to track your progress
  • CourseHero– check out the Web Programming path
  • W3Schools– complete with references, quizzes, examples and a certification path

Need more? Take a look at this great list of online learning centers.

Which Versions of PHP and MySql Run Your WordPress Blog?

How Healthy is Your WordPress Blog? Do you know what version of PHP or MySQL your server is running?

The improvements in WordPress continue. Check the state of your blog’s health by running the new Health Check plugin. Currently, it will check for the versions of PHP and MySQL that are running your blog. In order to work with WP 3.2, your server will need to be up-to-date and running —

  • PHP version 5.2 or greater
  • MySQL version 5.0.15 or greater

In an attempt to help others into the 21st century, WordPress will no longer work using PHP 4 or MySQL 4.

WordPress will also drop support for Internet Explorer 6. Yay! There are other improvements coming, so stay tuned!