Handy PHP and MySQL Queries for Developers

MySQL was initially introduced in back 1995 based on C and C++ programming languages. It is the most used open source database today because of the great verstality in online applications. MySQL is claimed over 65,000 downloads per day. It is also the most preferred choice of  new generation and is best used with PHP / Perl / Python. Millions of websites and companies like Google, Facebook, Alcatel Lucent and Zappos are relying on MySQL. It runs on almost all major platforms including Mac OS, Windows, Linux and Solaris.

mysql-logo

MySQL is loved by a broad number of database developers, DBAs and IT managers. The reason behind is the high performance database that is reliable, affordable, and easy to use. Whether you’re a new user or an experienced developer, you’ll prefer MySQL because of its wide development tools, training and support services that are key factor for any successful development.

In today’s article, we’re covering clever ways of tricking MySQL and PHP to perform your work smartly.

1- Cloning a database or table using MySQL queries.

Below are the simplest way to clone any database table using the query which helped me a lot in my developments. Categorizing the queries into two sets will ease understanding them.

a- Clone Table Structure and Keys Only

Firstly we’re assuming that your script needs to create a fresh table for user with structure similar to the existing user tables. This is the situation where you just need to duplicate a structure and keys of some table but not the data, this can be achieved with below query.

[code type=php]CREATE TABLE newTableName
LIKE oldTableName[/code]

The same above query works for databases as well but you must need the permissions to do so.

b- Clone Database Table with all data

In case you need to clone the whole table along with the data, below methods can help you.

[code type=mysql]CREATE TABLE newTableName
SELECT *
FROM oldTableName[/code]

The same can be achieved with the help of two queries like below:

[code type=mysql]CREATE TABLE newTableName
LIKE oldTableName;

INSERT INTO newTableName
SELECT *
FROM oldTableName;[/code]

For newbies, complete syntax for Create Table is here.

2- Return One Variable from MySQL Query

PHP is the leading language today that is preferably used with MySQL databases. We can make some smart functions to ease our work. This query is tech savvy and will help you get the one variable value using a simple PHP function.

[code type=php]
function quickVarGet($myquery){
$execQuery = mysql_query($myquery);
$myRow = mysql_fetch_array($execQuery);
mysql_free_result($execQuery );
$output = $myRow[0];
return $output;
}
[/code]

So you’re done. Use the below command anywhere to return the one variable.

[code type=php]$email = quickVarGet(“SELECT email from users where id = 1”);[/code]

The above query will return the email address only, the output may be “[email protected]” considering there was a record in the database.

3- Get the ID of last MySQL Last Entry

Sometimes we need to get the ID of last entry that some query just made. In order to achieve this thing below functions can help you.

[code type=php]
//Make sure database is connected
$getTheLastID = mysql_insert_id();
echo $getTheLastID;
[/code]

This function will return the ID of last MySQL query but this functions seem to work on queries that previously run in the same DB connection.

Anyway, let us know was it useful?

4- Making your Script MySQL Injection proof

Web is much save these days but still a lot of new developers don’t bother to make their scripts injection proof. Using below simple PHP function will make it impossible for injectors to hack into your database.

[code type=php]mysql_real_escape_string(trim($var));[/code]

This can be done more smartly by creating some function to make it easier to use on different locations.

[code type=php]function protect($var)
{
$var= mysql_real_escape_string(trim($var));
return $var;
}[/code]

Summing Up

Developers memorize a lot of queries in their mind, some are very complex while some are simpler. For every successful developer, its important to have some tech savvy smart queries in mind to trick the code easily. This not only will enhance the speed and workflow but also will ease your development with greater powers.


About the author

With a passion for Knowledge, Smashinghub has been created to explore things like Free Resources For Designers, Photographers, Web Developers and Inspiration.

Twitter Visit author website

Subscribe to Smashing Hub


8 Comments

  1. Avinash says:

    PDO is the great thing to work with PHP and MySQL.

  2. Conor says:

    The Mysql_ functions are depreciated. You should be directing your readers to use the MySQLi or PDO functions instead.

    • Nasir says:

      Good ask Conor, They’re deprecated actually. Infact we’ll be covering PDO stuff as well and will update this article once completed that one. I believe this one was for below PHP5 users. Thanks for the suggestion, we’ll consider it.

  3. Sean says:

    Nice article but I have a suggestion. I’ve been developing with PHP and MySQL for a few years now and I’ve been using the mysql_real_escape_string function all this time. However recently I’ve read a couple of articles that say that really isn’t the best way to go about that anymore.

    Supposedly the new way is via PDO and prepared statements because that evidently removes the possibility of sql injection. Now, I haven’t had the opportunity to really use this yet, partly because I’m still not exactly sure how to do it correctly. So my suggestion would be to have an article on PDO and in particular prepared statements. Something that goes in to more depth on the explanation, along with examples, would be really great.

  4. nice post Thanks for sharing this post very useful and valuable 🙂

  5. Murugan says:

    Good Information

  6. Rommel says:

    thanks for this, i found some useful points (: