Search the Community

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?