Help with Query issues...

[d1s0wn3d]

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!

 

[benanamen]

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

[Barand]

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

[d1s0wn3d]

Not exactly what I was looking for but can work with it...

 

Thanks Barand!