SQL statement with joining 4 tables and foreach loops

Question

Hello from Bulgaria, it is my very first post here so excuse me if I do not explain everything very well.

I'm doing admin panel in website(PHP, MYSQL) for Online Language Courses. I have to see:

1. A list with all the users registered in the website
2. If they are paid or not paid one of the courses ( they are 4 courses at the momment )
3. IF they are already input a Promo Code for any of the courses.

The table names and important table column's names are:

1. USERS: user_id, email, real_name
2. PAID_COURSES: paid_course_id, paid_course_name
3. USER_PAID_XREF: xref_id, user_id, paid_course_id
4. VOUCHERS: voucher_id, user_id, voucher, paid_course_id

I think the final result must be an array looks like:

    array(2)
    { 
    [1]=> array(5) { 
    ["user_id"]=> string(1) "1", 
    ["email"]=> string(15) "email@email.com", 
    ["real_name"]=> string(5) "ANJEL" ,
    ["paid_course_id"]=> array(4) {
    [1]=> string(1) "1" 
    [2]=> string(1) "0" 
    [3]=> string(1) "1" 
    [4]=> string(1) "0"
    },
    ["voucher"]=> array(4) {
    [1]=> string(20) "VOUCHER-11111111111"
    [2]=> string(1) "0"
    [3]=> string(19) "VOUCHER-AAAAAAAAAAA"
    [4]=> string(1) "0"
    }
     }
    [2]=> array(5) { 
    ["user_id"]=> string(1) "2" 
    ["email"]=> string(16) "office@email.com" 
    ["real_name"]=> string(5) "MITKO" 
    ["paid_course_id"]=> array(4) {
    [1]=> string(1) "0" 
    [2]=> string(1) "1" 
    [3]=> string(1) "1" 
    [4]=> string(1) "0"
    },
   ["voucher"]=> array(4) {
    [1]=> string(1) "0"
    [2]=> string(20) "VOUCHER-22222222222"
    [3]=> string(19) "VOUCHER-BBBBBBBBBBB"
    [4]=> string(1) "0"
    }
     }

     }

1. [1]=> is the key for user_id
2. In the array have 2 additional arrays with length of the number of the paid courses at all (Example at the momment have only 4 paid courses - English, spanish... etc)
3. ["paid_course_id"][1]=> the key [1] here is the paid_course_id from paid_courses table
4. ["paid_course_id"][2]=> string(1) "1" - The value "1" here is: If there is a row in the table user_paid_xref for the current paid_course_id (in the example paid_course_id=2) is with value 1 if there is not a row then the value is 0 ( With other words the user has paid this course or the user is not paid this course)
5. ["voucher"][3]=> string(19) "VOUCHER-BBBBBBBBBBB" - Same here as paid_course_id for a key of the array element.
6. ["voucher"][3]=> string(19) "VOUCHER-BBBBBBBBBBB" - The Value here is from table VOUCHERS column's name - voucher

So that's it I hope that someone will understand it. Thanks in advance

Answer 1

You can use the left join in order to get the multiple vouchers and multiple courses

SELECT * FROM `USERS` u
LEFT JOIN `VOUCHERS` v ON (u.user_id = v.user_id)
LEFT JOIN `USER_PAID_XREF` upx ON (u.user_id = upx.user_id)
LEFT JOIN `PAID_COURSES` pc ON (upx.paid_course_id = pc.paid_course_id)

Answer 2

SELECT u.user_id, u.email, u.real_name,
       GROUP_CONCAT(CONCAT(upx.paid_course_id IS NOT NULL, '|' IFNULL(v.voucher, '0'))) course_vouchers
FROM `USERS` u
LEFT JOIN `USER_PAID_XREF` upx ON (u.user_id = upx.user_id)
LEFT JOIN `VOUCHERS` v ON (u.user_id = v.user_id AND upx.paid_course_id = v.paid_course_id)
GROUP BY u.user_id

This will produce one row for each user, with comma-separated lists in course_vouchers. The elements of this list are pipe-separated course_id flag and voucher.

I left PAID_COURSES out of the query because your results don't seem to use anything from that table.

The PHP will be something like:

$result = array();
while ($row = $stmt->fetch_assoc()) {
    // Split apart the pieces of $row['course_vouchers']
    $courses = array_map(function($x) { return explode('|', $x); },
                         explode(',', $row['course_vouchers']));
    $row['paid_course_id'] = array_map(function($x) { return $x[0]; }, $courses);
    $row['voucher'] = array_map(function($x) { return $x[1]; }, $courses);
    unset($row['course_vouchers']);
    $result[] = $row;
}

Note that the default limit on the size of GROUP_CONCAT is 1024 characters. For data like yours, that looks like about 40 courses if they have vouchers for all of them. If a user may have more courses, you may need to increase this limit. See this question for how to do this.