jcanker Posted February 24, 2013 Share Posted February 24, 2013 (edited) Helping my son's baseball program track their player, parent, and team information a bit better. MySQL 5.5, PHP 5.4.9 My dilemma involves three tables: TABLE: users which tracks the userID, first name, last name, etc for every user (both parent and player) TABLE: registrations which utilizes the child's userID within the registration data. Every registration is entered into this table, including the player's userID and the seasonID he is registered for. TABLE: parents which tracks the relationship between every child and their parents. Every child->parent relationship is a row in this table. In order to get a JSON list of all the parents of registered players (to use in jQuery autocomplete for selecting adults as coach/assist coach, when defining a team) I'm using the following query: $query = "SELECT users.userID, users.fName, users.lName FROM (SELECT parents.parentID FROM parents JOIN registrations ON parents.childID = registrations.userID WHERE registrations.seasonID =:seasonID) AS t12 JOIN users ON t12.parentID = users.userID WHERE users.fName != '' && users.lName like :lName"; $stmt=$conn->prepare($query); $stmt->execute(Array( ':seasonID' => $_SESSION['seasonID'], ':lName' => $name )); $result = $stmt->fetchALL(PDO::FETCH_ASSOC); $json=json_encode($result); print_r($json); This works like a charm, EXCEPT if a parent has two or more children registered in the program. By design, they exist: in TABLE:users once with a unique userID not at all in TABLE:registrations in TABLE: parents one for each child they have registered with the program The problem is that for the parents with multiple children registered, the parent's name is being returned for every child they have registered. This means they show up twice (or more) in the JSON used to build the autocomplete list. My Question: Is there a way to construct this query to eliminate additional instances of the parent? Or do I just have to step through the results, build a new array, check to see if the parent is already there before choosing to add/not add to the array, then run the json_encode on that new array? Edited February 24, 2013 by jcanker Quote Link to comment Share on other sites More sharing options...
Barand Posted February 24, 2013 Share Posted February 24, 2013 does this work SELECT DISTINCT users.userID, users.fName, users.lName ... Quote Link to comment Share on other sites More sharing options...
jcanker Posted February 24, 2013 Author Share Posted February 24, 2013 I'll try that and report back. In the meantime I have it working via PHP, which isn't my preference: $query = "SELECT users.userID, users.fName, users.lName FROM (SELECT parents.parentID FROM parents JOIN registrations ON parents.childID = registrations.userID WHERE registrations.seasonID =:seasonID) AS t12 JOIN users ON t12.parentID = users.userID WHERE users.fName != '' && users.lName like :lName"; $stmt=$conn->prepare($query); $stmt->execute(Array( ':seasonID' => $_SESSION['seasonID'], ':lName' => $name )); $resultArray = Array(); $i = 0; while($row=$stmt->fetch()) { if(!empty($resultArray)) { //reset the "Add this record" tripwire $add = 1; foreach($resultArray as $outer) { if(in_array($row['userID'],$outer)){$add=0;} }//end foreach if($add == 1) { //add them in $resultArray[$i]['userID']=$row['userID']; $resultArray[$i]['fName']=$row['fName']; $resultArray[$i]['lName']=$row['lName']; } }//if(!empty) else { //add them in $resultArray[$i]['userID']=$row['userID']; $resultArray[$i]['fName']=$row['fName']; $resultArray[$i]['lName']=$row['lName']; } $i++; } $json=json_encode($resultArray,JSON_PRETTY_PRINT); echo "<pre>";print_r($json); Quote Link to comment Share on other sites More sharing options...
jcanker Posted February 24, 2013 Author Share Posted February 24, 2013 Schweet! Nice, elegant SQL solution to my issue. Thank you so much. I'll mark this as solved. The code now reads: $query = "SELECT DISTINCT users.userID, users.fName, users.lName FROM (SELECT parents.parentID FROM parents JOIN registrations ON parents.childID = registrations.userID WHERE registrations.seasonID =:seasonID) AS t12 JOIN users ON t12.parentID = users.userID WHERE users.fName != '' && users.lName like :lName"; $stmt=$conn->prepare($query); $stmt->execute(Array( ':seasonID' => $_SESSION['seasonID'], ':lName' => $name )); $result = $stmt->fetchALL(PDO::FETCH_ASSOC); $json=json_encode($result,JSON_PRETTY_PRINT); echo "<pre>"; print_r($json); and works without all that messy PHP code to dig down to the 2nd level. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.