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 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
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
The same can be achieved with the help of two queries like below:
[code type=mysql]CREATE TABLE newTableName
INSERT INTO newTableName
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.
$execQuery = mysql_query($myquery);
$myRow = mysql_fetch_array($execQuery);
$output = $myRow;
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.
//Make sure database is connected
$getTheLastID = mysql_insert_id();
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.
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)
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.
PDO is the great thing to work with PHP and MySQL.
The Mysql_ functions are depreciated. You should be directing your readers to use the MySQLi or PDO functions instead.
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.
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.
Agreed Sean, we’ll cover the simpler use of PDO and MySQLi in coming articles. Thanks for the detailed comment
nice post Thanks for sharing this post very useful and valuable 🙂
thanks for this, i found some useful points (: