Jump to content

Help with Query issues...


Go to solution Solved by Barand,

Recommended Posts

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!

 

Link to comment
https://forums.phpfreaks.com/topic/299430-help-with-query-issues/
Share on other sites

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 by benanamen
  • Solution

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 |
+-------+-----------+-------+
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.