Hand Made 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.
1 2 | CREATE TABLE newTableName
LIKE oldTableName
|
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.
1 2 3 | CREATE TABLE newTableName SELECT * FROM oldTableName |
The same can be achieved with the help of two queries like below:
1 2 3 4 5 6 | CREATE TABLE newTableName LIKE oldTableName; INSERT INTO newTableName SELECT * FROM oldTableName; |
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.
1 2 3 4 5 6 7 | function quickVarGet($myquery){
$execQuery = mysql_query($myquery);
$myRow = mysql_fetch_array($execQuery);
mysql_free_result($execQuery );
$output = $myRow[0];
return $output;
}
|
So you’re done. Use the below command anywhere to return the one variable.
1 | $email = quickVarGet("SELECT email from users where id = 1");
|
The above query will return the email address only, the output may be “admin@smashinghub.com” 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.
1 2 3 | //Make sure database is connected
$getTheLastID = mysql_insert_id();
echo $getTheLastID;
|
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.
1 | mysql_real_escape_string(trim($var));
|
This can be done more smartly by creating some function to make it easier to use on different locations.
1 2 3 4 5 | function protect($var)
{
$var= mysql_real_escape_string(trim($var));
return $var;
}
|
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.
Source: Smashinghub.com
Posts
