One to many query

[Twitch]

Well my friends, I have been pulling my hair out for over a day and I finally had to admit I needed help.  I haven't had much experience with one to many relationships and this particular query is driving me batty.

 

Ok, I am generating some checkboxes via a table like so:

<?php do { ?>
              <input <?php if (!(strcmp($row_venues_RS['userID'],116))) {echo "checked=\"checked\"";} ?> name="venueID_<?php echo $row_venues_RS['venueID']; ?>" id="venueID_<?php echo $row_venues_RS['venueID']; ?>" type="checkbox" value="<?php echo $row_venues_RS['venueID']; ?>" />
              <?php echo $row_venues_RS['venueName']." ".$row_venues_RS['venueID']; ?><br />
     
<?php } while ($row_venues_RS = mysql_fetch_assoc($venues_RS)); ?>

 

This query almost works.  The proper venue (or venues) is checked, but as you can guess I get duplicates:

SELECT venues.venueID, venues.venueName, venue_user_access.userID FROM
  venues INNER JOIN venue_user_access USING (venueID) WHERE venues.venueMasterID = %s AND venueStatus = 1 ORDER BY venueName ASC

 

These checkboxes are on an update record page so my goal is to show all venues tied to the venueMasterID (the value is stored in a sessions variable) from the venues table and check the ones that userID 116 has access.  The access table essentially has a venueID and userID field.  Is this possible with a single query?  I haven't written a lot of complex ones therefore I may be missing some function to make this easier.

 

I have read up on and tried DISTINCT and GROUP BY, but never got the results I desired.

 

Thanks in advance,

Twitch

 

[Twitch]

Maybe my question is too vague and that's why I haven't gotten a response.  Maybe a look at my structure will help.  Maybe what I'm asking can't be done or my db structure is incorrect. 

 

venue_user_access table:

 

userID  | venueID

116      |  65

116      |  66

114      |  65

etc      |  etc

 

venues table:

 

venueID | venueMasterID | venueName |  other columns with venue info

65        | 113                | My Club      |  blah blah blah etc

66        | 113                | Club 2        |  blah blah

 

 

The page I'm doing this query on is a page to update the user's (in my test case userID 116) access to clubs owned by venueMasterID 113.  I would like to generate the checkboxes on the update user page based on clubs owned by venueMasterID 113 and have the ones that userID 116 has access to show up as checked and the other ones unchecked.

 

Seems like a simple doable thing, but it's been a hair puller haha.

 

Again, the help is certainly appreciated.

 

-Twitch

[Psycho]

Let me paraphrase what I understand before I provide a solution.

 

On the page in question you have a venu master ID and a user ID. You want to list ALL the venus that match the venu ID and provide a checkbox for each. in addition, you want the checkboxes checked IF there is a matching record to associate the user to the particular venue.

 

Here is the basic syntax for the query that you need:

SELECT *
FROM `venus` as v
JOIN venue_user_access as va
  ON v.venueID = va.venueID AND va.userID = 116
WHERE v.venueMasterID = 113

 

 

[Twitch]

mjdamato,

 

Thank you so much for the reply.  The query you provided almost works for what I need.  The query does indeed show me checked checkboxes of venues that venueID 116 has access to and they are only venues that venueMasterID 113 owns, but the problem is I need a list of all the venues that venueMasterID 113 owns with only the ones that venueID 116 has access to checked.

 

For instance, your query gives me checkboxes like so:

 

[x] Club 1

[x] Club 2

 

 

if I delete Club 1 access by getting rid of the entry in the venue_user_access table I get:

 

[x] Club 2

 

I need the checkboxes to look like:

 

[ ] Club 1

[x] Club 2

 

Essentially a venue master can create venue users and give them access to the venue.  One venue with many users.  Generating the checkboxes for the insert page for this function works great because all I have to do is query the venues table and get all the venues with the venueMasterID of 113.  The problem is generating the checkboxes for the venue user update page.

 

Again, your reply and help is much appreciated.  This has been driving me crazy.  I've tried DISTINCT and GROUP BY combinations and nothing working... haha  I've got to be missing something simple in the query.

 

-Twitch

 

[Psycho]

Ok, I don't know what happened. I was in a rush to get to a meeting, so I must have had a copy/paste error because I did validate my solution before I posted.

 

Anyway, the problem is I left off the "LEFT" for the JOIN. Using a LEFT join tells the query to get all records from the left (i.e. first) table even if there is no matching record from the JOIN statement. In those cases the results from the JOINed table are null.

 

By the way, you should only select the fields you need instead of using *. In fact, this is the query I would probably use:

SELECT v.venueID, v.venueName, IF(va.userID=116, 1, 0) as access
FROM `venus` as v
LEFT JOIN venue_user_access as va
  ON v.venueID = va.venueID AND va.userID = 116
WHERE v.venueMasterID = 113

 

That will return a result set as follows (assuming the user 116 does not have access to "My Club" as you proposed in your last post)

venueID | venueName | access
65        My Club     0
66        Club 2      1

 

Then just use the value of the access field to determine whether to check the checkbox or not.

[Twitch]

Preliminary results are that your first query with the addition of the LEFT did the trick!  THANK YOU THANK YOU THANK YOU.  Man that was driving me crazy.  I tried all manner of LEFT JOIN, INNER JOIN etc etc.  I think I finally got to a point where I couldn't see straight...hahah

 

Normally I would agree about putting an access column with 0 or 1 but if I did that, when an owner created a new venue all users would have to be added to the access table with that venue.

 

I think this...

SELECT *
FROM `venues` as v
LEFT JOIN venue_user_access as va
  ON v.venueID = va.venueID AND va.userID = 116
WHERE v.venueMasterID = 113

 

...did the trick as far as I can tell right now.  Now I can replace the hard coded numbers with post or get or session or whatever.  Can't thank you enough my friend.

 

-Twitch

[Psycho]

Normally I would agree about putting an access column with 0 or 1 but if I did that, when an owner created a new venue all users would have to be added to the access table with that venue.

 

Huh? I don't think you are understanding what I provided in that second example. The "access" field is a dynamically created field using an IF statement in the query - it does not require that there are corresponding records in the access table. Did you even try it? It will make your code much easier to write and more logical.

 

The value for "access" is determined based on whether there was a record in the access table associating the user to the venue. If there was, then the value of access will be "1" (for true). If there was no corresponding record to associate the user with the venue (i.e. the result is null) then the value of "access" is determine to be "0" (false).

 

[Twitch]

When I first read your post I thought that was what you meant but then I guess I over thought it and decided you meant to put an access column in the table...ha ha Sorry.  I will give the code a try.

[Twitch]

Works like a champ my friend.  You are query guru.  And since you are maybe you can spot what is wrong with my insert and delete queries to update the access...haha

 //cycle through the venue checkboxes and add/delete to venue_user_access table
   if (isset($_POST["Update"])) {
	foreach($_POST as $key => $value) {
		if(strpos($key, 'venueID') !== false) {
			mysql_query("INSERT INTO venue_user_access (userID, venueID) VALUES (" . $_GET['userID'] . ", " . $value . ") WHERE NOT EXISTS (SELECT * FROM venue_user_access WHERE userID=". $_GET['userID'] ." AND venueID=". $value .")");
		}else{
			mysql_query("DELETE FROM venue_user_access WHERE userID=" . $_GET['userID'] . " AND venueID= " . $value." WHERE EXISTS (SELECT * FROM venue_user_access WHERE userID=". $_GET['userID'] ." AND venueID=". $value .")");

		}//end if(strpos($key, 'venueID') !== false)
	}

}
 //end cycle through the venue checkboxes and add/delete to venue_user_access table

 

[Psycho]

... maybe you can spot what is wrong with my insert and delete queries to update the access...

I'm happy to help, but it would be good manners to provide at least the basics of what you want the code to do and what it is doing differently - especially explaining any errors you might be getting.

[Twitch]

Sorry about that, you're right. 

Essentially, the query that you helped me with helps generate checkboxes of the venues on an update user page.  Now I am trying to update the access based on whether the checkbox is checked or not. 

 

This code is what I use to insert the access from checkboxes on the insert user page...

 

 //cycle through the venue checkboxes and add to venue_user_access table
   if (isset($_POST["Send"])) {
	foreach($_POST as $key => $value) {
		if(strpos($key, 'venueID') !== false) {
			mysql_query("insert into venue_user_access (userID, venueID) values (" . $_SESSION['createdUserID'] . ", " . $value . ")");
		}
	}
}
 //end cycle through the venue checkboxes and add to venue_user_access table

 

...it works perfectly...

 

...so I thought something like this...

//cycle through the venue checkboxes and add/delete to venue_user_access table
   if (isset($_POST["Update"])) {
	foreach($_POST as $key => $value) {
		if(strpos($key, 'venueID') !== false) {
			mysql_query("INSERT INTO venue_user_access (userID, venueID) VALUES (" . $_GET['userID'] . ", " . $value . ") WHERE NOT EXISTS (SELECT * FROM venue_user_access WHERE userID=". $_GET['userID'] ." AND venueID=". $value .")");
		}else{
			mysql_query("DELETE FROM venue_user_access WHERE userID=" . $_GET['userID'] . " AND venueID= " . $value." WHERE EXISTS (SELECT * FROM venue_user_access WHERE userID=". $_GET['userID'] ." AND venueID=". $value .")");

		}//end if(strpos($key, 'venueID') !== false)
	}

}
 //end cycle through the venue checkboxes and add/delete to venue_user_access table

 

...would work to update...

 

I don't get an error, nothing happens.

 

 

[Psycho]

Ok, after looking at the code briefly, I think you need to rewrite the form code. It is fine to create your checkbox IDs like this:

id="venueID_<?php echo $row_venues_RS['venueID']; ?>"

 

That makes sense since the IDs all have to be unique. BUt, the checkbox names (in this instance) should be set up as an array like this:

name="venueID[]"

 

Then you can just check $_POST['venueID'] to see if there are any checked fields instead of checking the names of all the submitted fields. And, even, better, the values of all the checked fields are passed as an array.

 

So, first, use the following to create your checkboxes:

while ($row = mysql_fetch_assoc($venues_RS))
{
    $checked = ($row['access']==1) ? ' checked="checked"' : '';
    echo "<input  type=\"checkbox\" name=\"venueID[]\" id=\"venueID_{$row['venueID']}\" value=\"{$row['venueID']}\"{$checked} />\n"
    echo "{$row['venueName']} {$row['venueID']}<br />\n";     
}

 

Second, your DELETE query has two WHERE clauses. I don't believe that is allowed. But, I think there is a better way to update the access records. First delete ALL access records for the selected user and venues. Then add back the ones which were checked. This will require that you pass the venueMasterID to the update script. Since you are using the venueMasterID to get the records to create the checkboxes, just create a hidden field with the venueMasterID.

 

Plus, you should NEVER run queuries in a loop. When adding multiple records, just create one single query for the insert. I typically use an array for each record and do an implode to add them to a query.

 

if (isset($_POST["Update"]))
{
    //Process the submitted data
    $venueMasterID = mysql_real_escape_string(trim($_POST['venueMasterID']));
    $userID = mysql_real_escape_string(trim($_POST['userID']));
    $associationRecords = array();
    foreach($_POST['venueID'] as $venueID)
    {
        if(ctype_digit($venueID))
        {
            $associationRecords[] = "({$userID}, {$venueID})";
        }
    }
    
    //Delete all current associations between user and the venues for the master venue
    $query = "DELETE
              FROM venue_user_access
              WHERE userID={$userID}
                AND venueID IN (SELECT venueID FROM venues WHERE venueMasterID = {$venueMasterID})";
    $result = mysql_query($query);
    
    //Add associations for the selected venues
    if(count($associationRecords))
    {
        $query = "INSERT INTO venue_user_access (userID, venueID)
                  VALUES " . implode(', ', $associationRecords);
        $result = mysql_query($query);
    }
}

[Twitch]

Man just when I think I'm learning php pretty well I come across someone like you...haha  I've never written code like that.  Pretty awesome.  I've got all the code on the page, but I'm getting a blank white page.  I think the issue is with the code to create the checkboxes but I can't tell why.

<?php while ($row = mysql_fetch_assoc($venues_RS))
{
    $checked = ($row['access']==1) ? ' checked="checked"' : '';
    echo "<input  type=\"checkbox\" name=\"venueID[]\" id=\"venueID_{$row['venueID']}\" value=\"{$row['venueID']}\"{$checked} />\n"
    echo "{$row['venueName']} {$row['venueID']}<br />\n";     
} ?>

 

 

By the way...thanks again for the help.

[Psycho]

If you are getting a blank page, then there is apparently an error somewhere. I won't guarantee the error is not in the code I provided as I didn't test it. If you think the problem is in that loop, then comment out those lines and see if the page displaye (albeit, without the checkboxes). If it does, then you know the problem is in those lines.

[Twitch]

I found the error.  Pesky ;  The first echo statement was missing it.  Problem now is only checkboxes for clubs that the user has access to are showing up which is weird cause I didn't change the query.  They are checked, but if the user doesn't have access that club the checkbox doesn't show up.

 

How long you been working with php that you can rattle off code on the fly like that without seeing all of the code?  I get confused and I have all the code in front of me...haha

[Twitch]

Never mind, I fixed the looping issue and I think the update and delete is working.  Will test more to be sure.  You are DA MAN!

 

Thanks again!

 

-Twitch

[Psycho]

How long you been working with php that you can rattle off code on the fly like that without seeing all of the code?  I get confused and I have all the code in front of me...haha

 

I started using PHP about 6 years ago, but it is just a hobby. I work as a QA manager for a desktop software company. I used to work for a web development company for about 3 years prior to that doing mostly project management, but also dabbled in coding using VBScript, JavaScript and PostScript.