Useful tips for PHP developers using MySQL

MySQL is a very common database used while developing web application. When developers are using PHP as their main programming language, they prefer MySQL as database. We are going to discuss some of uncommon but useful queries and precautions every developer should follow and will be taken care of.

    1. Why not to use mysql_* functions in PHP:

Developers should not use mysql_* functions, such as: mysql_query(), mysql_fetch_assoc() and mysql_fetch_array(), as it does not support asynchronous queries, stored procedures, multiple statements, transactions and latest functionalities of MySQL. This function is officially deprecated by MySQL development and has lack of Object Oriented interface etc. There are two main alternatives can be used instead. One is PDO and another is use of mysqli_* functions. PDO stands for PHP data objects and is even better solution than using mysqli_*, just because its object oriented way to connect with database.

   2. When to use single quotes, double quotes and backticks:

Single quote should be used when dealing with string values such as when using INSERT query and VALUES(). Double quotes also work similar to single quote and for strings, but single quote is supported by other familiar and widely used RDBMS. It is good practice to use single quotes. On the other hand backticks are used for column and table name identifier. Backticks are only necessary when the identifier name is similar to MySQL reserved keywords or contains whitespace characters.

  3.  Use of REPLACE and INSERT IGNORE for duplicate entries:

If database contains a large number of entries and developer want to add more values in it and also to ignore duplicate entries then they should go with these queries. In REPLACE, if any record exists then it will be overwritten and if a new record does not then will be created. Do not use this query when you want to skip an already existing record. In INSERT IGNORE, if a data already exist then it will be skipped otherwise a new record will be inserted into database.

Example of REPLACE:

REPLACE INTO `table_name`
SET `column_name_1` = ‘value_1′,
`column_name_2` = ‘value_2’,
` column_name_3` = ‘value_3’;

Example of INSERT IGNORE:

INSERT IGNORE INTO `table_name`
SET `column_name_1` = ‘value_1′,
`column_name_2` = ‘value_2’,
` column_name_3` = ‘value_3’;