Tuesday, May 10, 2011

Top 10 MySQL Mistakes Made by PHP People

A lot of developers would choose PHP than any other programming languages. Why? Because PHP is commonly said to be faster and more efficient for complex programming tasks and trying out new ideas, and is considered by many to be more stable and less resource-intensive as well. It is a very powerful programming language. However, what you created in PHP is not effective if you implement a weak database. Commonly, when you work in PHP, you'll also probably work with MySQL for the database. Well, many would have problems involving PHP and MySQL and some of these are caused by the mistakes which are listed below:

1. Using MySQL and not MySQLi

This is one of the very very common mistake made by PHP people. PHP Manual recommends using MySQLi over MySQL

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

2. User inputted data not sanitized/cleaned

When you are creating a form, it enables a user to input anything they want, therefore, exposing your database to any vulnerability that there is. Your code, which processes the input from your form, should be carefully written so as to ensure that the input is as requested. If not your database would be prone to SQL injection or any attack which could lead to its destruction.

3. Querying all the fields (*) instead of specific fields.

It is important to create your query based only on what you need. Using * returns all columns in a table. One disadvantage of this is - it is slower compared to the specific query because it extracts all the data stored in a specific table (and some of it may not be of use).

4. Using full-privileged users for database operations.

Privileges such as updating, adding, deleting, etc. should only be granted to users with such specific functions. Full privileges can be very powerful and in the same way harmful, and should be granted only when necessary to roles and trusted users of the database.

5. Poor naming standards.

When naming your databases, objects, fields, etc., use descriptive names. Name them so that when you go back to your code years from now, you still know what it is about. Sometimes, you name fields, tables, or databases with names that only makes sense to you. When you visit it again after a month or so, you can't even figure out what it is. So, better name them carefully and descriptively. There really isn't a right or wrong way to name them. Although there are some simple general rules that should be followed like not using spaces, avoiding the use of reserved words, not using dashes, etc.

6. Not properly normalized tables.

Normalization is the process of organizing data to minimize redundancy. It involves dividing large tables into smaller ones to produce well-structured relations. When not properly normalized, additions, deletions, and modifications of any data may lead to problems like data redundancy or data inconsistency.

7. Using usernames or other character typed fields as primary keys.

A primary key is anything unique that you assign to a specific record in your database. In other words, that certain record only belongs to that certain primary key. Using usernames or other character typed fields as primary keys may lead to accidental access to certain information.

8. Relying too much on PHP.

Instead of using MySQL functions to do the mathematical calculation PHP developer's prefer to be using PHP to perform those calculation. As an example AVG() is an built in Function in MySQL still I have seen many people prefer to be using PHP to get the average of values fetched from MySQL.

Apart from that comparing values, or any other operations that we need to solve, sometimes, we tend to create our own versions of functions which we don't know exists in MySQL. For example, using PHP script for comparison in order to determine the largest value in a group of data, when in fact, we can use the Max() function in MySQL. This can lead to unnecessary steps and ultimately results in slower code. Therefore, it is good to utilize your knowledge in MySQL, or in cases where you are not familiar enough with it, it's good to study and analyze MySQL.

9. Using wrong data types.

MySQL supports a number of data types which includes numeric types, date and time types, and string (character) types.So, for example, if you're storing dates use the Date data type. Using any other data type will only make it complicated. You should use the most precise data type for your data to ensure optimum storage and to reduce possible errors.

10. Not using UTF-8.

MySQL includes character set support that enables you to store data using a variety of character sets and perform comparisons according to a variety of collations. When creating a database, some of us forget to set the database to UTF-8 character set, which then makes us wonder why some of the data won't appear anywhere else. Setting it to UTF-8 usually solves those issues.  
source : Go4Expert

1 comment:
Write comments
  1. Till date that Oakley Jawbone Review have millions of brand loyal customers all across the globe. Oil Rigs Oakley The best thing which made them such a huge hit was that it provided a complete protection from the UV rays of the sun. vbnjcxy0dfh Oakley sunglasses were invented to keep the Oil Rig Oakley Sunglasses out of your eyes. Somewhere along the way, they became a way to look cool. Celebrities likely started this. There are many famous people who are known for wearing Discount Oakley Oil Rig Sunglasses . The celebrities themselves have a magnetism and an allure that people can't get enough of Oakley Frogskin Sunglasses , but when those same celebrities wear Oakley Dispatch , they become just that much cooler. In fact, Oakley O Frame have become such an icon in pop culture, Oakley O Frame Goggle that many celebrities are known for them. These are called Famous Oakley sunglasses. The
    Oakley Xs O Frame Snow Goggles are considered as one of the best as it not only protects from the glare of the Oakley O Frame Snow but it also protects the eyes from the reflection caused by water, snow and glass.