Several joins ?

webpoet

I have the following query:

SELECT pl.page_id, pg.* FROM pagegrants pg LEFT JOIN pages pl ON pg.page_id = pl.page_id WHERE page_location = '$_SERVER[SCRIPT_NAME]' AND userlevel_id = '$session_user_level'

Now I would like to change so that I don´t store userlevel in the session, only the users ID.
Instead I would like to add to this query so that it retrieves userlevel from my users table based on the userid. Is this possible and how would I go about doing it ?

OhLordy

Yes it is. Multiple joins can get difficult for mysql to handle, it all depends on how your tables were created in the first place and which columns you have indexed and how.

To make this query check your users table you can do it like this.

SELECT pl.page_id, pg.*, users.level
FROM pagegrants pg 
LEFT JOIN pages pl ON pg.page_id = pl.page_id 
LEFT JOIN users on users.user_id=pg.user_id
WHERE page_location = '$_SERVER[SCRIPT_NAME]' 
AND userlevel_id = '$session_user_level'

Use MySQL's explain function (simply type explain before the query when in MySQL) to get a breakdown of the query like below

+-------------------------+--------+---------------+---------+---------+------------------------------+------+-------+
| table                   | type   | possible_keys | key     | key_len | ref                          | rows | Extra |
+-------------------------+--------+---------------+---------+---------+------------------------------+------+-------+
| w_cosmetic_products     | ALL    | NULL          | NULL    |    NULL | NULL                         |  230 |       |
| w_cosmetic_series       | eq_ref | PRIMARY       | PRIMARY |       4 | w_cosmetic_products.csref_id |    1 |       |
| w_cosmetic_manufacturer | eq_ref | PRIMARY       | PRIMARY |       4 | w_cosmetic_series.cmref_id   |    1 |       |
| w_cosmetic_classes      | eq_ref | PRIMARY       | PRIMARY |       4 | w_cosmetic_products.ccref_id |    1 |       |
+-------------------------+--------+---------------+---------+---------+------------------------------+------+-------+

The column "rows" show how may rows have to be searched on that table. The rows must be multiplied to find the total number of possible row combinations that MySQL must check. In this case 230111 so only 230. However imagene what would happen if each of the other rows had 30 possible rows that would be 230303030 already you have 6210000.
Cheers
Rob