Page 1 of 1

Help with SQL Subquerys

Posted: Fri Jun 18, 2010 1:29 pm
by bla5e

Code: Select all

$query = "SELECT *, (SELECT groupid FROM intranet.users_associatedgroups WHERE userid=intranet.users.id) AS groupid FROM intranet.users WHERE id={$req_vars['id']}";
$info = pg_fetch_assoc(pg_query($db, $query));
query worked fine until i added the subquery. Not getting any errors, just no data is being shown on my table now. not the groupid, or anything coming from the main querys table.

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:08 pm
by requinix
Don't use a subquery when you can just use a JOIN.

Code: Select all

SELECT u.*, uag.groupid
FROM intranet.users u
JOIN intranet.users_associatedgroups uag ON u.id = uag.userid
WHERE u.id = <ID>
If you're still having problems, what do you get if you run the query manually?

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:09 pm
by Eran

Code: Select all

$query = "SELECT users.*,users_associatedgroups.groupid 
FROM users 
LEFT JOIN users_associatedgroups ON users_associatedgroups.userid=users.id
WHERE users.id={$req_vars['id']}";
Edit: Oops, tasairis beet me to it

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:22 pm
by bla5e
pytrin wrote:

Code: Select all

$query = "SELECT users.*,users_associatedgroups.groupid 
FROM users 
LEFT JOIN users_associatedgroups ON users_associatedgroups.userid=users.id
WHERE users.id={$req_vars['id']}";
Edit: Oops, tasairis beet me to it

this is working, but the associatedgroups table is populating a MULTIPLE dropdown select box. With this query, it only selected 1 of the options in the dropdown, even when the user is in MULTIPLE groups?

Code: Select all

{foreach from=$grps item=grp}
  <option value="{$grp.id}" {if $info.groupid == $grp.id}SELECTED{/if}>{$grp.description}</option>
{/foreach}

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:30 pm
by Jade
Are you getting multiple results for a single user in the data the query returns? I would check the query data...

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:39 pm
by bla5e
Jade wrote:Are you getting multiple results for a single user in the data the query returns? I would check the query data...
yes

Code: Select all

SELECT * from intranet.users_associatedgroups WHERE userid=64; 

Code: Select all

userid | groupid 
--------+---------
     64 |       1
     64 |       2

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:44 pm
by Jade
Make sure you have a space between the <option value="something" and the word selected. Also, make sure that you have multiple in your select tag.

Code: Select all

<select multiple size=5>
<option value="1"SELECTED>1</option> //this is WRONG, some browsers *cough* namely IE *cough* won't read this properly
<option value="1" SELECTED>1</option> //this one has space so everything is fine and dandy
</select>

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:53 pm
by Eran
this is working, but the associatedgroups table is populating a MULTIPLE dropdown select box. With this query, it only selected 1 of the options in the dropdown, even when the user is in MULTIPLE groups?
I didn't understand the question, but the query fetches all the groups related to the user in question. Is it not what you wanted?

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:55 pm
by bla5e
Jade wrote:Make sure you have a space between the <option value="something" and the word selected. Also, make sure that you have multiple in your select tag.

Code: Select all

<select multiple size=5>
<option value="1"SELECTED>1</option> //this is WRONG, some browsers *cough* namely IE *cough* won't read this properly
<option value="1" SELECTED>1</option> //this one has space so everything is fine and dandy
</select>
yeah it is spaced properly

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 2:58 pm
by bla5e
Here is the whole situation

Smarty HTML

Code: Select all

<select name="groups[]" id="groups" MULTIPLE>
<option value="">-- select group(s) --</option>
{foreach from=$grps item=grp}
  <option value="{$grp.id}" {if $info.groupid == $grp.id} SELECTED{/if} >{$grp.description}</option>
{foreachelse}
  <option>[ERROR]No Groups Exist?</option>
{/foreach}
</select>
Smarty PHP

Code: Select all

$query2 = "SELECT u.*, uag.groupid                                                                                                                       
                 FROM intranet.users u                                                                                                                                        
                 JOIN intranet.users_associatedgroups uag ON u.id = uag.userid                                                                                                
                 WHERE u.id={$req_vars['id']}";

$info = pg_fetch_assoc(pg_query($db, $query2));
$smarty->assign('info', $info);


//User Groups                                                                                                                                              
$grp_sql = pg_query($db, "SELECT * FROM intranet.users_groups");
while ($groups = pg_fetch_assoc($grp_sql)){
  $grps[] = $groups;
}
$smarty->assign('grps', $grps);

Re: Help with SQL Subquerys

Posted: Fri Jun 18, 2010 3:41 pm
by McInfo
My guess is that, in the $info array, items are indexed with the table alias attached, like

Code: Select all

$info['uag.groupid']
I'm not sure how Smarty::assign() handles the dot between the table name and the column name. I doubt it keeps it because it would conflict with Smarty's array syntax. PHP, in some situations, changes dots into underscores to avoid conflict with the concatenation operator.

In your query, it might help to alias the columns. For example,

Code: Select all

SELECT uag.groupid AS groupid ...