Barand

try SELECT r.sponsor , a.tot_sponsor , r.user_id , coalesce(b.tot_user, 0) as tot_user FROM user_referrals r JOIN ( SELECT sponsor , count(*) as tot_sponsor FROM user_referrals GROUP BY sponsor ) a ON r.sponsor = a.sponsor LEFT JOIN ( SELECT sponsor , count(*) as tot_user FROM user_referrals GROUP BY sponsor ) b ON r.user_id = b.sponsor WHERE r.sponsor = :root_sponsor +---------+-------------+---------+----------+ | sponsor | tot_sponsor | user_id | tot_user | +---------+-------------+---------+----------+ | 1 | 3 | 2 | 2 | | 1 | 3 | 3 | 1 | | 1 | 3 | 4 | 0 | +---------+-------------+---------+----------+ user_referrals: +----+---------+---------+ | id | sponsor | user_id | +----+---------+---------+ | 1 | 1 | 2 | | 2 | 1 | 3 | | 3 | 1 | 4 | | 4 | 2 | 5 | | 5 | 2 | 6 | | 6 | 3 | 7 | +----+---------+---------+ PS Alternative solution SELECT sponsor as user_id , count(*) as total , 'Direct' as type FROM user_referrals WHERE sponsor = 1 GROUP BY sponsor UNION ALL SELECT r.user_id , count(r2.sponsor) as total , 'Indirect' FROM user_referrals r LEFT JOIN user_referrals r2 ON r.user_id = r2.sponsor WHERE r.sponsor = 1 GROUP BY r2.sponsor ORDER BY type, user_id; +---------+-------+----------+ | user_id | total | type | +---------+-------+----------+ | 1 | 3 | Direct | | 2 | 2 | Indirect | | 3 | 1 | Indirect | | 4 | 0 | Indirect | +---------+-------+----------+

Except for the sample test data and expected results!

That's OK. I know what the query should look like - I just needed something to test it with. If you can't be bothered to help me to help you then I'll wish you luck.

Can you post some sample data and the results you expect from that data?

This time I have appended table1 and table2 data into a single table (table3) +---------+------------+--------+-------+ | company | sku_number | color | price | +---------+------------+--------+-------+ | ACME | sku12345 | beige | 6.00 | | ACME | sku99999 | pink | 44.00 | | ZUNFXZ | sku12345 | tan | 15.00 | | ZUNFXZ | sku50505 | orange | 7.00 | | ZUNFXZ | sku99999 | red | 30.00 | +---------+------------+--------+-------+ The query simplifies (no unions) to SELECT t3.company , t3.sku_number , t3.color , t3.price FROM table3 t3 JOIN ( SELECT sku_number , MAX(Price) as price FROM table3 GROUP BY sku_number ) max_prices USING (sku_number, price); +---------+------------+--------+-------+ | company | sku_number | color | price | +---------+------------+--------+-------+ | ACME | sku99999 | pink | 44.00 | | ZUNFXZ | sku12345 | tan | 15.00 | | ZUNFXZ | sku50505 | orange | 7.00 | +---------+------------+--------+-------+ NOTE: Table 3 primary key is (sku_number,company) to avoid duplicates

You need to lose the $ signs from your prices and store in numeric type column, such as DECIMAL(10,2). If they are stored as varchar() the sort order will be alphabetical instead of numeric For example mysql> select * from table3 order by price; +------------+--------+--------+ | sku_number | color | price | +------------+--------+--------+ | sku12345 | tan | $15.00 | | sku99999 | red | $5.00 | | sku50505 | orange | $7.00 | +------------+--------+--------+ mysql> select max(price) from table3; +------------+ | max(price) | +------------+ | $7.00 | +------------+ As I told you earlier, values other than sku (group by) and price (aggregated) are arbitrary (If you get what you want it's by luck not design.)

The tactic to use in this situation is create a subquery to get the max prices Join your original data to the subquery on the max price so you list those records that match the price. SELECT both_tables.sku_number , both_tables.color , both_tables.price FROM ( SELECT * FROM table1 UNION ALL SELECT * FROM table2 ) both_tables JOIN ( SELECT sku_number , MAX(Price) as price FROM ( SELECT sku_number, price FROM table1 UNION ALL SELECT sku_number, price FROM table2 ) both_prices GROUP BY sku_number ) max_prices USING (sku_number, price)

Why have you got 2 identically structured tables? [edit...] Why are creating a third table consisting of data derived from other tables? When you use an aggregation function the only values you can rely on on are the aggregated values and any GROUP BY values. All other values that are selected will be arbitrarily chosen from any record in the group.

Echo them and see which looks right. echo _ROOT_ . '<br>'; echo __ROOT__ . '<br>';

If it returns -1 then $this->getCredits() must be returning 0 (or false).

You can... $numLocations = 4; $stmt = $pdo->prepare ("SELECT city_name FROM city ORDER BY city_name LIMIT ? "); $stmt->execute([$numLocations]); $data = $stmt->fetchAll();

Welcome and enjoy.

Here's some ways to skin that cat ... A foreach ($res as $row) { echo "<div><ol>"; echo "<li>{$row['Nmbr_fid']}</li>"; echo "<li>{$row['Nmbr_value']}</li>"; echo "<li>{$row['Name_fid']}</li>"; echo "<li>{$row['Name_Value']}</li>"; . . . echo "</ol></div>"; } B foreach ($res as $row) { echo "<div><ol>"; foreach ($row as $col) { echo "<li>$col</li>"; } echo "</ol></div>"; } C foreach ($res as $row) { echo "<div><ol><li>" . join('</li><li>', $row) . '</li></ol></div>'; }

How are you getting "$data" array?

What have to tried so far? How are you connecting to the database? Have look at phpdelusions site If you aren't already using PDO the I recommend you do.

Are most of those the checkbox values?

https://www.php.net/manual/en/mysqli.construct.php See example #3 - manual error handling

You should be getting an error from the above - 4 ? placeholders but 7 values in the paremeter array

Try forcing an error (such as trying to connect with invalid password, for example) and see if that gets logged.

If errors aren't being reported they may be being logged. Have you checked error logs?

When you connect to the db, do you set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION so that pdo errors are reported?

Here is a fuller fix for those NULL gm values showing changes Output Data mysql> select * from item where cat_id=4; +----+---------------+------+------+--------+ | id | itemname | em | gm | cat_id | +----+---------------+------+------+--------+ | 10 | Glock 9mm | 125 | 1050 | 4 | | 11 | Colt .38 | 200 | 1175 | 4 | | 12 | Derringer .22 | 115 | NULL | 4 | NULL gm value +----+---------------+------+------+--------+

Not only not necessary but quite a bizarre combination. Do you want a horizontal menu bar or a card layout? Pick one.

Not unless you post the relevant code that's giving the problem

Perhaps it's the code that's wrong.