6 replies to this topic

[alrazy1]

ui am reading a book about creating ecommerce with php/mysql ,
i have encountered this weird mysql query :

private function sessionAuthenticate( $uid ) 

{ 


$sql = "SELECT u.ID, u.username, u.active, u.email, u.admin, 

u.banned, u.name, (SELECT GROUP_CONCAT( g.name SEPARATOR 

'-groupsep-' ) FROM groups g, group_memberships gm 

WHERE g.ID = gm.group AND gm.user = u.ID ) AS groupmemberships 

FROM users u WHERE u.ID={$uid}"; 


PeacockCarterFrameworkRegistry::getObject('db')-> 

executeQuery( $sql ); 

if( PeacockCarterFrameworkRegistry::getObject('db')-> 

numRows() == 1 ) 

i dont understand everything after $sql= thats all my problem ..

for example why he used u.ID and not regular variable name like user_id ?

what this mean SELECT GROUP_CONCAT() ?
what does SEPARATOR
'-groupsep-' meaning here ?
why he is nesting multiple queries one inside the other here?
simply i am lost here !
thanks

Edited by Roger, 27 January 2011 - 10:25 AM.

[sam_l]

MySQL :: MySQL 5.0 Reference Manual :: 11.15.1 GROUP BY (Aggregate) Functions

[WingedPanther]

alrazy1 said:

$sql = "SELECT u.ID, u.username, u.active, u.email, u.admin,
u.banned, u.name, (SELECT GROUP_CONCAT( g.name SEPARATOR
'-groupsep-' ) FROM groups g, group_memberships gm
WHERE g.ID = gm.group AND gm.user = u.ID ) AS groupmemberships
FROM users u WHERE u.ID={$uid}";

u.ID is equivalent to users.ID, where users is the table name, and it has been aliased as u in the FROM clause. ID, username, active, email, admin, banned, and name are all fields in table users.

The subselect is to get a string from groups that lists all the groups in table group that the user is a member of, based on u.ID=group_memberships.user (aliased as gm.user). It avoids having multiple rows per user, as a join would have.

[alrazy1]

why he use this g.ID = gm.group ?
he want to be sure the groupmemberships is found in the basic groups names table ? but why he should do that ?
and what is executed first g.ID = gm.group or gm.user = u.ID ?
i mean the query select first all the user memberships and after that select only legal group names found in g ?
so as i understand the where(1,2,3) command in mysql is executed from the end to the first from right to left 3,2,1 not 1,2,3 ??

[alrazy1]

in other words the "where" command here first select all the raws from gm(groupmemberships table) that correspond to this specific user u.ID >>>> gm.user = u.ID
and after that the "where" command select only raws having 100% legal name found in the basic groups table g.ID>>>> g.ID = gm.group
so "WHERE a AND B" is executed like this first B after that A ? am i right ?
do i understand from this that most mysql command is executed from right to left or in other words from the end to the start ?

[WingedPanther]

You have to change how you think about SQL: it doesn't execute things in steps (though the DB engine certainly does), it describes the results you are looking for.

Quote

$sql = "SELECT u.ID, u.username, u.active, u.email, u.admin,
u.banned, u.name, (SELECT GROUP_CONCAT( g.name SEPARATOR
'-groupsep-' ) FROM groups g, group_memberships gm
WHERE g.ID = gm.group AND gm.user = u.ID ) AS groupmemberships
FROM users u WHERE u.ID={$uid}";

What this is saying is you want to return records that contain the following fields: ID, username, active, email, admin, banned, and name. All of those come from table users. In addition, you want to concatenate together all the groups that the user is a member of (pulling the group name from groups, and determining membership from table group_memberships). The idea is that group_memberships links groups and users, but doesn't have the user-friendly name of the group we want. A group name will satisfy both groups.ID=group_memberships.group and group_memberships.user = users.ID. All of this is limited to information about a single user by the final WHERE clause, which specifies the only user ID we actually want.

[Parabola]

alrazy1 said:

SELECT u.ID, u.username, u.active, u.email, u.admin,
u.banned, u.name, (SELECT GROUP_CONCAT( g.name SEPARATOR
'-groupsep-' ) FROM groups g, group_memberships gm
WHERE g.ID = gm.group AND gm.user = u.ID ) AS groupmemberships
FROM users u WHERE u.ID={$uid}"

Wow.... that's actually pretty nice. I like it. Anyways, what's happening is that in one table we of course have the users. Another table has the groups. The third table links them, obviously. What it does is say for instance in users there is one user, and in groups there are 4 groups, but user is only in the first 3 groups. this would mean that groupmemberships (gm) has 3 entries:
209432: group1
209432: group2
209432: group3

And lets say user table is something like:
209432: myUserName: true: User@somewhere.com: False: False: myName

and group table would be something like:
group1: <description>: <member count>: <creation date>: <blah blah>
group2: <description>: <member count>: <creation date>: <blah blah>
group3: <description>: <member count>: <creation date>: <blah blah>
group4: <description>: <member count>: <creation date>: <blah blah>

So this query would concatenate those 3 groups into one row for the user, with a separator of -groupsep-, so it would return this one row:
209432: myUserName, True, User@somewhere.com: False: False: myName: group1-groupsep-group2-groupsep-group3

At least, that's how I read it. Never used that function ;)

By default though, I believe group_concat would normally use a comma, so it would have been
209432: myUserName, True, User@somewhere.com: False: False: myName: group1,group2,group3
had SEPERATOR not been used....

Oh and the way it knows which groups to put together is I believe the table (before concat happens) gets grouped by gm.user (user.ID)

That's the best I can make of it. Like I said- I like it. I may use it in fact... I have the perfect query to use that in.....
Hope that helps!