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: #-Link-Snipped-# This was really worth, so I wished to share it here...
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: #-Link-Snipped-# This was really worth, so I wished to share it here...
Replies
-
pompanoI hate dealing with MYSQL. I mainly do java but always need someones help doing the queries for the#-Link-Snipped-# of what I run lol. -
PraveenKumar PurushothamanMySQL is database and Java is programming. I am damn sure that you are spamming!
-
synergynextthough it didnt make sense to the bugger 😁
but I must say ..GREAT INFO PRAVEEN! -
PraveenKumar Purushothaman
Thanx Synergy... Can't you see that he's not an user at all!!! Just a spammer... 😛synergynextthough it didnt make sense to the bugger 😁
but I must say ..GREAT INFO PRAVEEN! -
synergynextyeah he tried to put a link that and his effort was wasted as the link got removed. Big K has done good job in filtering unwanted stuff!
-
PraveenKumar Purushothaman
Ohy! It was done by AKD... 😛synergynextyeah he tried to put a link that and his effort was wasted as the link got removed. Big K has done good job in filtering unwanted stuff! -
silverscorpion@praveen: Don't quote such spam posts. If you think any post is a spam, just report it. That'll be better I think.. 😀
-
PraveenKumar Purushothaman
Yeah, I know that SS. I quoted only after the link was removed! 😛 Anywayz, I removed it... 😀silverscorpion@praveen: Don't quote such spam posts. If you think any post is a spam, just report it. That'll be better I think.. 😀
You are reading an archived discussion.
Related Posts
There is an event from Freescale a leading MCU manufacturer company.
In this event there are basically two Tracks.
Participants may choose from two tracks:
Mechatronics Robot Track
Contestants...
It might be old for some,but I really liked this game and thus posted here.
So,Post your best here!
Escapa!
😁My Best is 20.625 seconds.
Hi guys, can you guide me in this tour, i,m a computer science student and also a ccna student, right now i need to do my final year project, i...
Cars made of strange stuff
Quote: Everyone knows you make cars out of steel. Or maybe aluminium if you're Audi, carbonfibre if you're McLaren, or even glassfibre plastic if you're...
hello sir
i am a 3rd year cse student
i want to know about 6month training in cloud computing in chd
is dere any company