Using inline queries, but doesn't reproduce desired result as comma in not supported in MySQL

Question

My database table structure is like below:

UID	referred	referrer
300	302,304
302	303	300
303	305,306,307	302
304	308	300
308	309	304

Now I am trying to count the numbers at the bottom level of the referral chain, which are 305, 306, 307 and 309. here is an image for reference, Sample Image

I want to show this for user 300. I am using these SQL Queries.

$sql = "SELECT GROUP_CONCAT(uid) FROM mybb_users WHERE referrer='300'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

  while($row = mysqli_fetch_assoc($result)) {
    $abc= $row["uid"];

this produces the uids in comma seperated format. EG: For 300, these are 302 and 304, (it might be more, but right now its these two). lets say these are produced in comma seperated format, i.e. 302,304

Now in next query I want users referred by 302 and 304 in comma seperated format. I used the below query:

SELECT uid FROM mybb_users WHERE referrer='$abc'
 $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
       while($row = mysqli_fetch_assoc($result)) {
       $wbc= $row["uid"];

Now this is supposed to fetch the users referred by 302 and 304, which should be 303 and 308. Again, these might be in comma seperated format such as 303,308 and now I want to use this value in the third inline query to get the desired uid numbers for the level C. I am using this query for this:


       $sql = "SELECT count(*) FROM mybb_users WHERE referrer='$wbc'";
       $result = mysqli_query($conn, $sql);
       if (mysqli_num_rows($result) > 0) {
         while($row=mysqli_fetch_assoc($result)) {
        echo $row['count(*)'];          }
    } else {
       echo "0";  
      }
      }

And finally this should produce the list of 305, 306, 307 and 309. Instead it's just producing the list of 305, 306 and 307 but not for 309. and count it which would be 4. But it's showing 3. Which means that it's just processing one leg and ignoring the other one. Please let me know how can I achieve the desired result. Thanks.

You shouldn't use comma separated lists. This whole process would be considerably easier if standard schema was used. — user3783243, Dec 03 '22 at 14:16
See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — user3783243, Dec 03 '22 at 14:40
It is unclear how you will do this: "this should produce the list of 305, 306, 307 and 309.", with the give data. — Luuk, Dec 03 '22 at 15:01

score 1 · Answer 1 · answered Dec 03 '22 at 14:15

You override $wbc in any iteration instead of appending the values. Also note that the order of ids count here (1,2 is not 2,1 but represents the same in your datamodel). If you want all the ids in $wbc as a list then make it an array and implode later:

$wbc[] = $row["uid"];
//...
implode("," $wbc) // Produces comma separated list

You should not use a list of ids in your databse to refer to other records. For many-to-many relationships you should use a join table like user_refferers with 2 columns user_id and refferer_id which links a user to many refferers with one row for each refferrer.

Can you please link an example? – Higgs Boson Dec 03 '22 at 14:35 — Higgs Boson, Dec 03 '22 at 14:35

Using inline queries, but doesn't reproduce desired result as comma in not supported in MySQL

1 Answers1