help writing a (somewhat) complex query...

[glennn.php]

thanks for your help -

 

for a particular project ID# (say, '123') i have to get all users from table 'users' AND IF a user, proj_id exists in table 'users_projects' (echo ' checked')...

 

what i guess i'd do is

 

SELECT * FROM 'users'...
while ($row = ...) {
echo "<checkbox"...
SELECT * from users_projects WHERE projid == '123'...
while ($row1 == ...) {
echo ' checked';
}
}

 

but i can tell that that's either just plain wrong or at least very obtuse...

 

if someone can decipher my intent could you possibly offer a more concise query that will get me ALL users AND tell me if the user, projid exists in users_projects, please?

 

thanks much

[kickstart]

Hi

 

Looks like you need a LEFT OUTR JOIN.

 

Something like this (quickly knocked up).

 

$sSql = "SELECT users.*, users_projects.projid AS UserProjectFound FROM users LEFT OUTER JOIN users_projects ON users.projid = users_projects.projid";
$rs = mysql_query($sSql) or die(mysql_error());

while ($row = mysql_fetch_assoc($rs)) 
{
echo "<input type='checkbox' name='project".$row['projid']."' ".(($row['UserProjectFound'] == '') ? '' : "checked='checked'" )." />";
}

 

All the best

 

Keith

[glennn.php]

thanks, Kieth - i'll go work with this and tell you of my success. thanks

[glennn.php]

i played with this a while, got empty checkboxes OR duplicate checkboxes with some checked, some not.

 

ok, i'm guilty of very poor explanation, i s'pose. let me describe mo' better:

 

RE rfpb_id: "123" ($rfpb_id)

 

'users_users'.'userid' - i need all of them.

 

'users_users_rfpb'.'user_id' and 'users_users_rfpb'.'rfpb_id'

 

while returning a checkbox for EACH userid, if userid exists in 'users_users_rfpb' with $rfpb_id i want to check the box.

 

sorry for my ambiguity. thanks for your help... big time...

[The Little Guy]

Still not 100% sure if I get what you are looking for, so take this and run with it:

 

$sql = mysql_query("select *, if(up.project_id is null, 0, 1) as isProject from users_users uu left join users_projects up on(uu.userid = up.userid and up.project_id = 123)");
while($row = mysql_fetch_assoc($sql)){
$projectExists = (bool)$row['isPorject'];
echo "<input type='checkbox' name='something' ".($projectExists?"checked='checked'":"")." />".$row['userid'];
}

[glennn.php]

ok, let's try this:

 

table 'users_users' has many users: 18, 19, 20 and 21

 

i'm printing

 

<input chckbx nme value="18" />18

<input chckbx nme value="19" />19

<input chckbx nme value="20" />20

<input chckbx nme value="21" />21

 

table 'users_users_rfpb' contains records:

user_id | rfpb_id

  20      |  ABC...

  21      |  123

 

while i'm showing all the users for rfpb "123", i really want to print

<input chckbx nme value="18" />18

<input chckbx nme value="19" />19

<input chckbx nme value="20" />20

<input chckbx nme value="21" "checked" />21

 

sorry again. i really appreciate the help -

 

 

 

[kickstart]

Hi

 

So you need to join on the user id.

 

Is this to provide a list of users and then a list of projects, with a tick box for any project which is checked if the user is on it?

 

If so something like this:-

 

$sSql = "SELECT users.*, projects.*, users_projects.projid AS UserProjectFound FROM users CROSS JOIN projects LEFT OUTER JOIN users_projects ON users.userid = users_projects.userid and projects.projid = users_projects.projid";
$rs = mysql_query($sSql) or die(mysql_error());

while ($row = mysql_fetch_assoc($rs)) 
{
echo $row['usersname']." - ".$row['projectname']."<input type='checkbox' name='project".$row['projid']."' ".(($row['UserProjectFound'] == '') ? '' : "checked='checked'" )." />";
}

 

This is doing a cross join between users and projects to get a list of every project for every user (you might want to add an order by clause to make the order more useful), and then a left join to get the details for that user / project.

 

Idea is that for a user you will get a row returned for every project, and the details if they exist if that user is assigned to that project.

 

All the best

 

Keith

[glennn.php]

well, i'm just showing the list of users as checkboxes for a given project, so all i need is the list of ALL users <checkboxes> (as defined in users_users) and the ones that are already assoc. with the project (as defiined in users_users_rfpb) would be ticked, leaving the option for admin to tick others that are unticked (the ones that are NOT in users_users_rfpb).

 

i'll go work on what you just sent. thanks, so far... )

[glennn.php]

this is what i want to end up with for a project 123:

 

<input chckbx nme=user[] value="18" />18

<input chckbx nme=user[] value="19" />19

<input chckbx nme=user[] value="20" />20

<input chckbx nme=user[] value="21" "checked" />21

 

that's all.

 

[kickstart]

Hi

 

If you only want a single project at once then you can bypass the CROSS JOIN.

 

However you need to check the project ID in the ON clause of the OUTER JOIN (not in a WHERE clause).

 

$sSql = "SELECT users.*, users_projects.projid AS UserProjectFound FROM users LEFT OUTER JOIN users_projects ON users.userid = users_projects.userid and users_projects = 123";
$rs = mysql_query($sSql) or die(mysql_error());

while ($row = mysql_fetch_assoc($rs)) 
{
echo "<input type='checkbox' name='user[]' value='".$row['userid']."' ".(($row['UserProjectFound'] == '') ? '' : "checked='checked'" )." />".$row['userid'];
} 

 

All the best

 

Keith

[glennn.php]

" Unknown column 'users_users_projects' in 'on clause' "

 

(users_users_projects is correct, all these tables have a prefix "users_")

 

this can NOT be this hard. i don't know why i can't understand it...

[kickstart]

Hi

 

Sorry, my fault. Should be a column name (missed it from the last one). Add .projid to the table name there, which I seemed to removed in the last edit.

 

Basic lesson.

 

Normal SQL from a table is pretty fast, but there is a fair overhead for just doing a query. So doing one query and then looping round doing another query for each row suffers a big overhead.

 

In a query you can JOIN two or more tables together. Couple of types of JOIN:-

 

INNER JOIN - this brings back a row where there is a matching column value on each table. Say you had a table of bank accounts and a table of transactions, if one account had no transactions then no row would come back for that account

 

OUTER JOIN - this will bring back a row when there is no matching row on the other table. If the bank account situation it would bring back a row for the account but the columns that should refer to the transactions would be NULL for any account with no transactions.

 

CROSS JOIN - this brings back every combination of row. You might use this if you wanted a list of all the possibilities, and then use an outer join onto a 3rd table when that possibility had occured. For example, say you had a list of people and a list of days of the week, plus a table containing days that a particular person had been shopping, you could get a list of all the people and days and a count of how many times (if at all) that person had been shopping on that day.

 

In your basic query you need an OUTER JOIN. This is giving you a list of all the people, and then joining that with the table of projects people are in. Putting the check for the project in the ON clause means it is only matching on projects you are interested in. So if Fred is involved in project 123 it will match that row, but if not there will be no row matched.

 

All the best

 

Keith

[The Little Guy]

There is also:

 

LEFT JOIN - Returns all the results from table A, then connects the primary key from table A to the foreign key on table B, if the values are NOT in table B the fields in table B will be filled with NULL. (I believe this is the most common join)

 

RIGHT JOIN - opposite of LEFT JOIN

[glennn.php]

yes, that fixed it, thanks so much - i had tried adding field names but obviously not the correct combination. i'll read over your notes, thanks again