Page 1 of 1
[SOLVED]many-to-many query
Posted: Tue Mar 07, 2006 9:20 am
by rubberjohn
I have managed to get the following query to return the correct data from tables with a many to many relationship.
Code: Select all
$sql_check_tag = mysql_query("SELECT * FROM f_tag_f_user WHERE user_id='$_SESSION[user_id]'");
while ($check_tag = mysql_fetch_array($sql_check_tag, MYSQL_ASSOC)){
$sql_fetch_tag = mysql_query("SELECT tag FROM f_tags WHERE tag_id= '$check_tag[tag_id]'");
$fetch_tag = mysql_fetch_array($sql_fetch_tag, MYSQL_ASSOC);
$temp .= $fetch_tag['tag'] . ",";
}
Then afterwards I explode the $temp variable to get the values back into an array.
While this works, it does seem a little bit clumsy, is there a better, more efficient way of doing this (ie a many-to-many query)?
Am I right in thinking that a join could be used but there would be a performance overhead?
Thanks
r_j
Posted: Tue Mar 07, 2006 10:15 am
by feyd
Code: Select all
SELECT `tag`
FROM `f_tags`
INNER JOIN `f_tag_f_user`
ON `f_tag_f_user`.`tag_id` = `f_tags`.`tag_id`
WHERE `f_tag_f_user`.`user_id` = '$_SESSION[user_id]'
Posted: Tue Mar 07, 2006 10:46 am
by rubberjohn
cheers for that - other than cleaner code - why is this better than what i was using originally?
rj
Posted: Tue Mar 07, 2006 10:53 am
by feyd
multiple queries can fill the RAM and require more work for the database server than is necessary.
Posted: Tue Mar 07, 2006 11:50 am
by rubberjohn
cheers again for that.
will this type of join work in the following situation as well
I have three tables (many-to-many) - (USER) (USER_TAG_LINK) and (TAG)
- a user enters a tag to be INSERTed in the db
- before the tag is put in the db a check is done to see if the tag is already in the TAG table
- if it is - another check is done to check if the tag is linked to the current user in the (USER_TAG_LINK)
------- if the tag already exists for the user an error message is displayed
------- if the tag does not belong to the user a link is inserted into USER_TAG_LINK
- if the tag doen't exist it is INSERTed
I am in the middle of trying to do this in a similar way to the first code example i submitted.
Would it be better (or possible) to use a join to do this or would it be better to split these into two different queries -
one to check if the current user has already submitted the tag and then a separate one to see if any other user has submitted that tag?
Bit of a long one, sorry, but your help is appreciated.
Thanks
rj
Posted: Tue Mar 07, 2006 12:00 pm
by feyd
rubberjohn wrote:Would it be better (or possible) to use a join to do this or would it be better to split these into two different queries -
one to check if the current user has already submitted the tag and then a separate one to see if any other user has submitted that tag?
Although technically possible to do in one query, I, personally, would do this logic in two. A join isn't required to check if the tag exists, and the previous join will work against the current user for the second query.
Posted: Tue Mar 07, 2006 2:07 pm
by rubberjohn
ok cheers for that