Jump to content

help writing a (somewhat) complex query...


glennn.php

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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'];
}

Link to comment
Share on other sites

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 -

 

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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... :o)

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.