Whats new in PHP5

There are somany changes were added to php5 and now it occupies 3rd position in the worlds famous programing languages. see the all new features added to php5 @ http://devzone.zend.com/article/1714

Joins in mysql

There are different joins available in mysql to retrieve results from multiple tables.
Join, left join, right join, inner join, outer join

Here i explained each one by taking two tables as an example.

mysql>Products Table;
+----+--------------+--------------+
| id | product_name | manufacturer |
+----+--------------+--------------+
| 1 | Shoes | Company1 |
| 2 | Laptop | Company2 |
| 3 | Monitor | Company3 |
| 4 | DVD | Company4 |
+----+--------------+--------------+
4 rows in set (0.00 sec)

Table buyers:
mysql> SELECT * FROM buyers;
+----+------+------------+----------+
| id | pid | buyer_name | quantity |
+----+------+------------+----------+
| 1 | 1 | Steve | 2 |
| 2 | 2 | John | 1 |
| 3 | 3 | Larry | 1 |
| 4 | 3 | Michael | 5 |
| 5 | NULL | Steven | NULL |
+----+------+------------+----------+
5 rows in set (0.00 sec)

Normal Join

mysql> SELECT buyers.buyer_name, buyers.quantity, products.product_name FROM buyer
s join products on buyers.pid=products.id;
(OR)
mysql> SELECT buyers.buyer_name, buyers.quantity, products.product_name FROM buyer
s,products WHERE buyers.pid=products.id;

+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve | 2 | Shoes |
| John | 1 | Laptop |
| Larry | 1 | Monitor |
| Michael | 5 | Monitor |
+------------+----------+--------------+
4 rows in set (0.00 sec)

The above query displays results only with matching pids in both tables.



Left Join

mysql> SELECT buyer_name, quantity, product_name FROM buyers LEFT JOIN products ON
buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve | 2 | Shoes |
| John | 1 | Laptop |
| Larry | 1 | Monitor |
| Michael | 5 | Monitor |
| Steven | NULL | NULL |
+------------+----------+--------------+
5 rows in set (0.00 sec)

Explaination:
Mysql starts with the left table (buyers) as main source then For each row from the table buyers mysql scans the table products with pid (i.e id of the product in products table) and returns the product name. Then the product name is joined with the matching row to the table buyers. For unmatched rows it returns null.

The above query is same as below (except with null values)

mysql> SELECT buyers.buyer_name, buyers.quantity, products.product_name FROM buyer
s,products WHERE buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve | 2 | Shoes |
| John | 1 | Laptop |
| Larry | 1 | Monitor |
| Michael | 5 | Monitor |
+------------+----------+--------------+
4 rows in set (0.00 sec)

The above query displays results for only matching pids in both tables.

Right Join

mysql> SELECT buyer_name, quantity, product_name FROM buyers RIGHT JOIN products ON
buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve | 2 | Shoes |
| John | 1 | Laptop |
| Larry | 1 | Monitor |
| Michael | 5 | Monitor |
| NULL | NULL | DVD |
+------------+----------+--------------+
5 rows in set (0.00 sec)

Explaination:
Mysql starts with the Right table (products) as main source then For each row from the table products mysql scans the table buyers with id (i.e pid of the buyers table) and returns the product name. Then the product name is joined with the matching row to the table products. For unmatched rows it returns null. From my example above it returns NULL for DVD because no one bought DVD.

Inner join is same as join.

The word OUTER may be added after the word LEFT or RIGHT in the left/right joins - it's provided for ODBC compatibility and doesn't add an extra capabilities.

Sql Difference between where clause and Having clause

Hi, below are differences between where clause and having clause in mysql select statements.

1. Having clause is usually used with Group By clause although it can be used without it too.
2. 'Having' is just an additional filter to 'Where' clause.
3. 'Where' clause applies to the individual rows whereas 'Having' clause is used to test some condition on the group(usually aggregate methods) rather than on individual rows.
4. WHERE clause is used to impose condition on SELECT statement as well as single row function and is used before GROUP BY clause where as HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query. For example queries look at http://www.allinterview.com/showanswers/6252.html