Hi, I'm trying to decide what to do with this statement. It's for a member profile page. The first two tables "member" and "room" retrieve the bulk of the info. The first four left joins join to tables that link the member table to itself in many-to-many relationships. They're there to determine the existence of links between the profile member and calling member. The final two left joins determine whether the member has uploaded an image.
AFAIK, the query is syntactically correct. It works fine. What worries me is the query's expense, particularly for a page that will be accessed frequently.
On the one hand, the tables will be properly indexed, on the other, they could eventually hold millions of rows.
Question 1: Am I in the clear here, or should I look for an alternate approach? If the latter, does anyone have any suggestions?
Question 2: Is it okay to violate the rules of normalization and duplicate a row in two tables if it would allow me to drop one of the tables and make it a seven table join?
Thanks in advance. Hopefully the query below will be formatted correctly:
$sth = $db['query']("SELECT m.*, FLOOR((TO_DAYS(CURRENT_DATE()) - TO_DAYS(m.dob))/365.25) AS age, r.*,
b1.er_id AS black_id, b2.er_id AS block_id, g1.er_id AS guest_id_1, g2.er_id AS guest_id_2, i1.image_id
FROM member AS m, room AS r
LEFT JOIN list_black AS b1 ON m.member_id = b1.ed_id AND b1.er_id = '" . $HTTP_SESSION_VARS['member_id'] . "'
LEFT JOIN list_block AS b2 ON m.member_id = b2.ed_id AND b2.er_id = '" . $HTTP_SESSION_VARS['member_id'] . "'
LEFT JOIN list_guest AS g1 ON m.member_id = g1.ed_id AND g1.er_id = '" . $HTTP_SESSION_VARS['member_id'] . "'
LEFT JOIN list_guest AS g2 ON m.member_id = g2.er_id AND g2.ed_id = '" . $HTTP_SESSION_VARS['member_id'] . "'
LEFT JOIN image AS i1 ON i1.member_id = r.member_id
LEFT JOIN list_image AS i2 ON i1.image_id = i2.image_id AND i2.room_num = '$room_num'
WHERE m.member_id = r.member_id
AND r.member_id = '$profile_id'
AND r.room_num = '$room_num'")
or die('Error querying: ' . $db['error']());
$rth = $db['fetch_array']($sth);
AFAIK, the query is syntactically correct. It works fine. What worries me is the query's expense, particularly for a page that will be accessed frequently.
On the one hand, the tables will be properly indexed, on the other, they could eventually hold millions of rows.
Question 1: Am I in the clear here, or should I look for an alternate approach? If the latter, does anyone have any suggestions?
Question 2: Is it okay to violate the rules of normalization and duplicate a row in two tables if it would allow me to drop one of the tables and make it a seven table join?
Thanks in advance. Hopefully the query below will be formatted correctly:
$sth = $db['query']("SELECT m.*, FLOOR((TO_DAYS(CURRENT_DATE()) - TO_DAYS(m.dob))/365.25) AS age, r.*,
b1.er_id AS black_id, b2.er_id AS block_id, g1.er_id AS guest_id_1, g2.er_id AS guest_id_2, i1.image_id
FROM member AS m, room AS r
LEFT JOIN list_black AS b1 ON m.member_id = b1.ed_id AND b1.er_id = '" . $HTTP_SESSION_VARS['member_id'] . "'
LEFT JOIN list_block AS b2 ON m.member_id = b2.ed_id AND b2.er_id = '" . $HTTP_SESSION_VARS['member_id'] . "'
LEFT JOIN list_guest AS g1 ON m.member_id = g1.ed_id AND g1.er_id = '" . $HTTP_SESSION_VARS['member_id'] . "'
LEFT JOIN list_guest AS g2 ON m.member_id = g2.er_id AND g2.ed_id = '" . $HTTP_SESSION_VARS['member_id'] . "'
LEFT JOIN image AS i1 ON i1.member_id = r.member_id
LEFT JOIN list_image AS i2 ON i1.image_id = i2.image_id AND i2.room_num = '$room_num'
WHERE m.member_id = r.member_id
AND r.member_id = '$profile_id'
AND r.room_num = '$room_num'")
or die('Error querying: ' . $db['error']());
$rth = $db['fetch_array']($sth);