Help with SQL Subquerys

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Help with SQL Subquerys

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Help with SQL Subquerys

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Help with SQL Subquerys

Post 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
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: Help with SQL Subquerys

Post 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}
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Help with SQL Subquerys

Post by Jade »

Are you getting multiple results for a single user in the data the query returns? I would check the query data...
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: Help with SQL Subquerys

Post 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
Last edited by bla5e on Fri Jun 18, 2010 2:45 pm, edited 1 time in total.
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Help with SQL Subquerys

Post 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>
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Help with SQL Subquerys

Post 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?
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: Help with SQL Subquerys

Post 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
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Re: Help with SQL Subquerys

Post 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);
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Help with SQL Subquerys

Post 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 ...
Post Reply