d1s0wn3d Posted November 11, 2015 Share Posted November 11, 2015 I'm having a bit of an issue... So what I'm trying to do is this: I have a user table: ------------------------- | Table: userlist | ------------------------- | Id | GuestOf | User | |-----------------------| | 0 | System | Bill | |-----------------------| | 1 | Bill | Jason | |-----------------------| | 2 | Jason | Jack | |-----------------------| | 3 | Jason | Jill | |-----------------------| | 4 | Jack | Matt | |-----------------------| | 5 | Jill | Stu | |-----------------------| | 6 | Jack | Gwen | |-----------------------| | 7 | Jill | Frank | |-----------------------| Details: Jason's guests are Jack and Jill, Jack's guests are Matt and Gwen, and Jill's Guests are Stu and Frank. Now what I want to do is find out if Jason's guests both have their two guests. But I can only check for this each time a new guest is added to the list. In this case, When Frank is inserted. So what should happen is, when Frank gets inserted, I get Jill. Check if she has her 2 guests. Then check who invited her (Jason), check if he has 2 guests, now get the other guest (Jack) and see if he has 2 guests. If they all have their 2 guests each, I should get a value of 7. Which will signal completion of that group of people and guests. When the next person gets inserted, checkCompletion runs again for that insert... This is the code I'm currently using: function countGuests($User) { $sql = "SELECT COUNT(`Id`) CntId FROM `userlist` WHERE `GuestOf` LIKE '$User'"; $res = mysql_query($sql); $temp = mysql_fetch_array($res, MYSQL_BOTH); return $temp["CntId"]; } function checkCompletion($UserId) { $population = array(); $sql = "SELECT `User` FROM `userlist` WHERE `GuestOf` LIKE '$UserId'"; $res = mysql_query($sql); if (mysql_num_rows($res) > 0) { while ($temp = mysql_fetch_array($res, MYSQL_BOTH)) { $population[] = $temp["User"]; } $partialPop = 0; for ($i = 0; $i < count($populaton); ++$i) { $partialPop = $partialPop + countGuests($population[$i]); } } return $partialPop; } Which should give me the result: 7 (Jason, Jack, Jill, Matt, Gwen, Stu, Frank) However, I get the result: 0. What's wrong with my query? I would appreciate any help/corrections... Thank you! Quote Link to comment Share on other sites More sharing options...
benanamen Posted November 11, 2015 Share Posted November 11, 2015 (edited) I will leave the logic part to someone else to answer. But for my contribution... Your code is vulnerable to SQL Injection. You NEVER EVER send user supplied data directly to the database. You are also using deprecated code that will not work at all in the latest version of Php. You need to use PDO with prepared statements. Edited November 11, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted November 11, 2015 Solution Share Posted November 11, 2015 you could use something like this SELECT a.user , group_concat(b.user) as guests , count(b.guestof) as total FROM userlist a LEFT JOIN userlist b ON b.guestof = a.user GROUP BY a.user ; +-------+-----------+-------+ | user | guests | total | +-------+-----------+-------+ | Bill | Jason | 1 | | Frank | NULL | 0 | | Gwen | NULL | 0 | | Jack | Gwen,Matt | 2 | | Jason | Jill,Jack | 2 | | Jill | Frank,Stu | 2 | | Matt | NULL | 0 | | Stu | NULL | 0 | +-------+-----------+-------+ Quote Link to comment Share on other sites More sharing options...
d1s0wn3d Posted November 15, 2015 Author Share Posted November 15, 2015 Not exactly what I was looking for but can work with it... Thanks Barand! 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.