Database JOINS concept

Display all the tables in database

SHOW TABLES FROM society;

SHOW TABLES FROM database name;

Retrieving Row Data as an Array

You can access the row's data as an array using the toArray() method of the Row object. This returns an associative array of the column names to the column values.

$catgeory = $catgeories->fetchRow('id='.$cat_id);

$where = 'rss_cat='.$cat_id;

$result = $catgeory->toArray();

echo '<pre>';

print_r($result);

die();

The trim() function removes whitespaces and other predefined characters from both sides of a string.

Parameter Description string Required. Specifies the string to check charlist Optional. Specifies which characters to remove from the string. If omitted, all of the following characters are removed:

• "\0" - NULL
• "\t" - tab
• "\n" - new line
• "\x0B" - vertical tab
• "\r" - carriage return
• " " - ordinary white space

For disable layout the in zend:

$this->_helper->viewRenderer->setNoRender();

$this->view->layout()->disableLayout();

Database:

MySQL LEFT, RIGHT JOIN tutorial

MySQL joins are hard for beginners. At least for me when I was beginner.
I will try to explain the joins in the simplest possible way.

Join in MySQL is a query where you can join one or more tables.

For example we have two tables: products and buyers with the following structures.
Table products:

mysql> SELECT * FROM products;

+----+--------------+--------------+

| 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)

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)

What happened?
Mysql starts with the left table (buyers). For each row from the table buyers mysql scans the table products, finds the id of the product and returns the product name. Then the product name is joined with the matching row from the table buyers. For unmatched rows it returns null.
To make it simpler, the above query is same as (except the unmatched rows are not returned):

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)

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)

What happens here is Mysql starts with the Right table (products). For each id from the table products MySQL scans the left table - buyers to find the matching pid. When it finds the matching pid it returns the buyer_name and the quantity. For unmatched rows it returns null. From my example above it returns NULL for DVD because no one bought DVD.

You have seen MySQL pattern matching with LIKE ...%. MySQL supports another type of pattern matching operation based on regular expressions and the REGEXP operator. If you are aware of PHP or PERL then its very simple for you to understand because this matching is very similar to those scripting regular expressions.

Following is the table of pattern which can be used along with REGEXP operator.

Pattern	What the pattern matches
^	Beginning of string
$	End of string
.	Any single character
[...]	Any character listed between the square brackets
[^...]	Any character not listed between the square brackets
p1|p2|p3	Alternation; matches any of the patterns p1, p2, or p3
*	Zero or more instances of preceding element
+	One or more instances of preceding element
{n}	n instances of preceding element
{m,n}	m through n instances of preceding element

Examples:

Now based on above table you can device various type of SQL queries to meet your requirements. Here I'm listing few for your understanding. Consider we have a table called person_tbl and its having a field called name:

Query to find all the names starting with 'st'

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

Query to find all the names ending with 'ok'

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

Query to find all the names which contains 'mar'

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

Query to find all the names starting with a vowel and ending with 'ok'

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';