MySQL: Alternate to INNER JOIN

I came across a good way to write queries in mysql. Instead of using INNER JOIN there is a easy way to do it, sort of renaming the tables while writing query. Below is the example:

Requires: MySQL

Using:

table1(
   id int,
   name varchar(30)
   age int)
table2(
   id int,
   address varchar(300))

SELECT a.name, a.age, b.address FROM table1 a, table2 b WHERE a.id = b.id;

Explanation:
Look at the query renaming of table is done before retrieving the information we want. WHERE clause is very important in the query because there should be something which is common in both the tables in order to fetch the data.

Note: You can also write queries instead of table name in FROM clause i.e., sub-queries.
          If you are not getting results as you expect, there is something wrong with the way query has been written. Try writing it some other way. And this will happen a lot with complex queries. :P

So, this method will make your queries really powerful and easy. Time to replace INNER JOIN.

Fetching from database using PHP

If you have database, then of course you will need to fetch data out of it. Four methods are described below to fetch from database and whats the difference between them.

Required: PHP, MySQL.

Using:

table1

name(varchar)

$query = "SELECT name FROM table1";

$rows = mysql_query($query);

$row = mysql_fetch_array($rows, $result_type); 

$row = mysql_fetch_assoc($rows);

$row = mysql_fetch_row($rows);

$row = mysql_fetch_object($rows, 'class', $params);

Note: All parameters are not defined here but are explained below.

Explanation:

Methods to fetch are described below:

1. mysql_fetch_array($rows, $result_type): First argument, $rows is same as we have described earlier. Second argument is the result type which has three values MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH, which is default and this argument is optional.
• MYSQL_ASSOC is used when we want to use associative key or table name for fetching. So, if we want to echo result it would be like
  echo $row['name'];
  
• MYSQL_NUM is used if we are going to use numbers as associative keys also called index. It would go like
  echo $row[0].
  
• MYSQL_BOTH is used if you don't know what you are going to use. With this way you can use both key and index.
  
2. This is just the same as mysql_fetch_array() with result type argument as MYSQL_ASSOC. Syntax will be
   $row['name'];
   
3. It is identical to mysql_fetch_array() with result type as MYSQL_NUM. Syntax will be
   echo $row[0];
   
4. mysql_fetch_object($rows, 'class', $params): This is same as mysql_fetch_assoc() but instead of array this returns object of class 'class'. That is it can be accessed by only with column name in table. Syntax would be:
   echo class->name;
   The last argument, $params, is used when parameters are needed to be passed in constructor of 'class', it has to be array. Also $params is optional.

Note: All the fetch function are almost equivalent in terms of performance.

         Also see PHP manual on these function.

Comparing timestamp in MySQL

Required: MySQL

Using:
table1
         id (int)
         jointime(timestamp)

SQL query

1. SELECT id FROM table1 WHERE jointime > '2011-10-08 00:00:00';

Explantion:

1. This query selects id from table where jointime is greater than 2011-10-08.

Note:

• Timestamp is a standard data type in MySQL and it should have both date and time.
• For current time use NOW()
• If you are using only date then use conversion from date to timestamp using TIMESTAMP()
• With PHP
• First get the time if current time use time(), it will give time in Unix Epoch.
• Convert above time to timestamp using date() and then use in query.
• If using some other time use mktime().
• Remember the quotes they are important.

Also see MySQL timestamp datatype and PHP date time functions, also other functions.

Protection from sql injection in PHP

There are many points which are needed to stop sql injection in PHP. One of them is discussed below.
We will focus on escaping the characters which allow sql injection. Let say we have a table:

Required: MySQL, PHP

Using:
table1:
         id (int)
         name (char)

Now if we use a query,

"SELECT id FROM table1 WHERE name = ".$name;

Here $name is a php vairable. Now lets try to hack this query. I will use one example here for that.
If we pass a value ALL (SELECT name FROM table1) into $name and it will reply to us with all the ids in table1. So in order to prevent such disaster we are going to use some methods of PHP itself.

First, is the mysql_real_escape_string() and the second one is addslashes().
While running query use the above functions like  addslashes(mysql_real_escape_string($name)).

Explanation:

mysql_real_escape_string() - what it does is escape the special characters in string. like hexadecimal.
addslashes() - this introduces slashed before the quotes.

Note:

1. First use mysql_real_escape_string() then addslashes over it if you are entering value in database.
2. To invert the procedure which will be needed when fetching values use stripslashes() twice over the value and it will give you the original value.