Barand

If you're unsure, you can cover the date range in the table with... WITH RECURSIVE dates(n, dt) AS ( SELECT 1, MIN(DATE(dateAdded)) FROM user_shifts UNION ALL SELECT n+1, dt + INTERVAL 1 DAY FROM dates WHERE dt < (SELECT MAX(dateAdded) FROM user_shifts) ) SELECT n, dt, DAYNAME(dt) as day FROM dates;

PS - If you want to see the non-work days... WITH RECURSIVE dates(n, dt) AS ( SELECT 1, '2024-06-17' UNION ALL SELECT n+1, dt+INTERVAL 1 DAY FROM dates WHERE n < 7 ) SELECT u.id , dayname(d.dt) as day , dt , coalesce(mins, 0) as mins , coalesce(cum, '-') as cum FROM dates d CROSS JOIN user u LEFT JOIN ( SELECT user_id , start_time , TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 as mins , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 as cum FROM worked_hours WINDOW w1 as (PARTITION BY user_id ORDER BY start_time) ) hrs ON d.dt = DATE(hrs.start_time) AND u.id = hrs.user_id HAVING cum - mins < 1500 OR mins = 0 ORDER BY u.id, dt; +----+-----------+------------+------+------+ | id | day | dt | mins | cum | +----+-----------+------------+------+------+ | 1 | Monday | 2024-06-17 | 476 | 476 | | 1 | Tuesday | 2024-06-18 | 481 | 957 | | 1 | Wednesday | 2024-06-19 | 462 | 1419 | | 1 | Thursday | 2024-06-20 | 490 | 1909 | | 2 | Monday | 2024-06-17 | 470 | 470 | | 2 | Tuesday | 2024-06-18 | 553 | 1023 | | 2 | Wednesday | 2024-06-19 | 495 | 1518 | | 3 | Monday | 2024-06-17 | 490 | 490 | | 3 | Tuesday | 2024-06-18 | 0 | - | | 3 | Wednesday | 2024-06-19 | 0 | - | | 3 | Thursday | 2024-06-20 | 476 | 966 | | 3 | Friday | 2024-06-21 | 488 | 1454 | | 3 | Saturday | 2024-06-22 | 491 | 1945 | +----+-----------+------------+------+------+

With MySQLv8 or MariaDBv11 you can easily generate the required dates on the fly using WITH RECURSIVE. eg to creates a 1 week's dates WITH RECURSIVE dates(n, dt) AS ( SELECT 1, CAST('2024-06-17' as DATE) UNION ALL SELECT n+1, dt + INTERVAL 1 DAY FROM dates WHERE n < 7 ) SELECT n , dt , DAYNAME(dt) as day FROM dates; +------+------------+-----------+ | n | dt | day | +------+------------+-----------+ | 1 | 2024-06-17 | Monday | | 2 | 2024-06-18 | Tuesday | | 3 | 2024-06-19 | Wednesday | | 4 | 2024-06-20 | Thursday | | 5 | 2024-06-21 | Friday | | 6 | 2024-06-22 | Saturday | | 7 | 2024-06-23 | Sunday | +------+------------+-----------+ I have created a user table with 3 users and also altered the test data user #2 reches 1500 mins in 3 days user #3 has no records for 18th/19th so now doesn't reach 1500 until saturday Revised data +----+---------+---------------------+---------------------+ | id | user_id | start_time | finish_time | +----+---------+---------------------+---------------------+ | 1 | 1 | 2024-06-17 09:00:00 | 2024-06-17 17:26:00 | | 2 | 1 | 2024-06-18 09:00:00 | 2024-06-18 17:31:00 | | 3 | 1 | 2024-06-19 09:00:00 | 2024-06-19 17:12:00 | | 4 | 1 | 2024-06-20 09:00:00 | 2024-06-20 17:40:00 | | 5 | 1 | 2024-06-21 09:00:00 | 2024-06-21 17:01:00 | | 6 | 1 | 2024-06-22 09:00:00 | 2024-06-22 17:36:00 | | 7 | 1 | 2024-06-23 09:00:00 | 2024-06-23 17:47:00 | | 8 | 1 | 2024-06-24 09:00:00 | 2024-06-24 17:19:00 | | 9 | 2 | 2024-06-17 09:00:00 | 2024-06-17 17:20:00 | | 10 | 2 | 2024-06-18 09:00:00 | 2024-06-18 18:43:00 | | 11 | 2 | 2024-06-19 09:00:00 | 2024-06-19 17:45:00 | | 12 | 2 | 2024-06-20 09:00:00 | 2024-06-20 17:35:00 | | 13 | 2 | 2024-06-21 09:00:00 | 2024-06-21 17:26:00 | | 14 | 2 | 2024-06-22 09:00:00 | 2024-06-22 17:14:00 | | 15 | 2 | 2024-06-23 09:00:00 | 2024-06-23 17:55:00 | | 16 | 2 | 2024-06-24 09:00:00 | 2024-06-24 17:15:00 | | 17 | 3 | 2024-06-17 09:00:00 | 2024-06-17 17:40:00 | no data for user #3 on 18th/19th | 20 | 3 | 2024-06-20 09:00:00 | 2024-06-20 17:26:00 | | 21 | 3 | 2024-06-21 09:00:00 | 2024-06-21 17:38:00 | | 22 | 3 | 2024-06-22 09:00:00 | 2024-06-22 17:41:00 | | 23 | 3 | 2024-06-23 09:00:00 | 2024-06-23 17:00:00 | | 24 | 3 | 2024-06-24 09:00:00 | 2024-06-24 17:04:00 | +----+---------+---------------------+---------------------+ The rewritten query... WITH RECURSIVE dates(n, dt) AS ( SELECT 1, '2024-06-17' UNION ALL SELECT n+1, dt+INTERVAL 1 DAY FROM dates WHERE n < 7 ) SELECT u.id , dayname(d.dt) as day , dt , mins , cum FROM dates d CROSS JOIN user u LEFT JOIN ( SELECT user_id , start_time , TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 as mins , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 as cum FROM worked_hours WINDOW w1 as (PARTITION BY user_id ORDER BY start_time) ) hrs ON d.dt = DATE(hrs.start_time) AND u.id = hrs.user_id HAVING cum - mins < 1500 ORDER BY u.id, start_time; +----+-----------+------------+------+------+ | id | day | dt | mins | cum | +----+-----------+------------+------+------+ | 1 | Monday | 2024-06-17 | 476 | 476 | | 1 | Tuesday | 2024-06-18 | 481 | 957 | | 1 | Wednesday | 2024-06-19 | 462 | 1419 | | 1 | Thursday | 2024-06-20 | 490 | 1909 | | 2 | Monday | 2024-06-17 | 470 | 470 | | 2 | Tuesday | 2024-06-18 | 553 | 1023 | | 2 | Wednesday | 2024-06-19 | 495 | 1518 | | 3 | Monday | 2024-06-17 | 490 | 490 | | 3 | Thursday | 2024-06-20 | 476 | 966 | | 3 | Friday | 2024-06-21 | 488 | 1454 | | 3 | Saturday | 2024-06-22 | 491 | 1945 | +----+-----------+------------+------+------+

Have you tried.. $response = array(); while ($row = mysqli_fetch_array($get_tour,MYSQLI_ASSOC)) { $response[] = array(["name"=>$row["tour_name"], "dates"=>$row["print_start"], "datee"=>$row["print_end"], "location"=>$row["city"] . ", " . $row["state"] ); } echo json_encode($response); or , using PDO (where $pdo is the pdo db connection)... $get_tour = $pdo->query("SELECT tour_name, print_start, print_end, city, state) FROM wherever"); echo json_encode($get_tour->fetchAll(PDO::FETCH_ASSOC) )

unset($data['sub']); unset($data['level']); Have you ever considered reading the manual at php.net?

Example... DATA mysql> select * from worked_hours; +----+---------+---------------------+---------------------+ | id | user_id | start_time | finish_time | +----+---------+---------------------+---------------------+ | 1 | 1 | 2024-06-17 09:00:00 | 2024-06-17 17:26:00 | | 2 | 1 | 2024-06-18 09:00:00 | 2024-06-18 17:31:00 | | 3 | 1 | 2024-06-19 09:00:00 | 2024-06-19 17:12:00 | | 4 | 1 | 2024-06-20 09:00:00 | 2024-06-20 17:40:00 | | 5 | 1 | 2024-06-21 09:00:00 | 2024-06-21 17:01:00 | | 6 | 1 | 2024-06-22 09:00:00 | 2024-06-22 17:36:00 | | 7 | 1 | 2024-06-23 09:00:00 | 2024-06-23 17:47:00 | | 8 | 1 | 2024-06-24 09:00:00 | 2024-06-24 17:19:00 | | 9 | 2 | 2024-06-17 09:00:00 | 2024-06-17 17:20:00 | | 10 | 2 | 2024-06-18 09:00:00 | 2024-06-18 17:43:00 | | 11 | 2 | 2024-06-19 09:00:00 | 2024-06-19 17:45:00 | | 12 | 2 | 2024-06-20 09:00:00 | 2024-06-20 17:35:00 | | 13 | 2 | 2024-06-21 09:00:00 | 2024-06-21 17:26:00 | | 14 | 2 | 2024-06-22 09:00:00 | 2024-06-22 17:14:00 | | 15 | 2 | 2024-06-23 09:00:00 | 2024-06-23 17:55:00 | | 16 | 2 | 2024-06-24 09:00:00 | 2024-06-24 17:15:00 | | 17 | 3 | 2024-06-17 09:00:00 | 2024-06-17 17:40:00 | | 18 | 3 | 2024-06-18 09:00:00 | 2024-06-18 17:15:00 | | 19 | 3 | 2024-06-19 09:00:00 | 2024-06-19 17:35:00 | | 20 | 3 | 2024-06-20 09:00:00 | 2024-06-20 17:26:00 | | 21 | 3 | 2024-06-21 09:00:00 | 2024-06-21 17:38:00 | | 22 | 3 | 2024-06-22 09:00:00 | 2024-06-22 17:41:00 | | 23 | 3 | 2024-06-23 09:00:00 | 2024-06-23 17:00:00 | | 24 | 3 | 2024-06-24 09:00:00 | 2024-06-24 17:04:00 | +----+---------+---------------------+---------------------+ QUERY WITH hrs as ( SELECT user_id , DAYNAME(start_time) as day , TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 as mins , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 as cum , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 - TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 <= 1500 as include FROM worked_hours WINDOW w1 as (PARTITION BY user_id ORDER BY start_time) ) SELECT user_id , day , mins , cum FROM hrs WHERE include; RESULTS

Using foreach() is still favourite, as it was last time you asked.

$data['sub'] = 'math'; $data['level'] = 'valley';

That code writes a value to $response in each iteration, each value overwriting the previous one. Try $response[] = ... instead of $response = ... to append to the $response array

The trick is to get the right ratio between gradient length and font size. With font "arial black" I found grad length px : font_size pt approx 1.8 : 1 <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Example</title> <style type='text/css'> body { background-color: #000; } .gradhead { font-family: "arial black"; font-size: 30pt; background: repeating-linear-gradient(#FFF , #EEE , #006EFC 55px); -webkit-background-clip: text; -webkit-text-fill-color: transparent; width: 30%; float: left; padding: 8px; } </style> </head> <body> <div class="gradhead"> LINE 1 </div> <div class="gradhead"> LINE 1<br> LINE 2 </div> <div class="gradhead"> LINE 1<br> LINE 2<br> LINE 3 </div> </body> </html>

You could have a look at https://developer.mozilla.org/en-US/docs/Web/CSS/gradient/repeating-linear-gradient

The gradient is applied across the whole element, not across each row. Put each row in it's own element

One way... <?php $data = array ( 0 => array ( 'Q_id' => '1.1.1|||1.1.2|||1.1.3|||1.1.4|||1.1.5|||1.1.6|||1.1.7|||1.1.8|||1.1.9|||1.1.10|||1.1.11|||1.1.12|||1.1.13|||1.1.14|||1.1.15|||1.1.16|||1.1.17|||1.1.18|||1.1.19|||1.1.20|||1.1.21|||1.1.22|||1.1.23|||1.1.24|||1.1.25|||1.1.26|||1.1.27|||1.1.28|||1.1.29|||1.1.30|||1.1.31|||1.1.32|||1.1.33|||1.1.34|||1.1.35|||1.1.36|||1.1.37|||1.1.38|||1.1.39|||1.1.40|||1.1.41|||1.1.42|||1.1.43|||1.1.44|||1.1.45|||1.1.46|||1.1.47|||1.1.48|||1.1.49|||1.1.50', 'QueNo' => NULL, 'qNo' => '1|||2|||3|||4|||5|||6|||7|||8|||9|||10|||11|||12|||13|||14|||15|||16|||17|||18|||19|||20|||21|||22|||23|||24|||25|||26|||27|||28|||29|||30|||31|||32|||33|||34|||35|||36|||37|||38|||39|||40|||41|||42|||43|||44|||45|||46|||47|||48|||49|||50', 'SECTION' => 'SECTION A', ), 1 => array ( 'Q_id' => '1.1.51|||1.1.51.2|||1.1.51.3|||1.1.51.4|||1.1.51.5|||1.1.51.6|||1.1.51.7|||1.1.51.8|||1.1.51.9|||1.1.51.10', 'QueNo' => 'b|||c|||d|||e|||f|||g|||h|||i|||j', 'qNo' => '51|||51|||51|||51|||51|||51|||51|||51|||51|||51','SECTION' => 'SECTION B', ), ) ; ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Example</title> <style type='text/css'> table { border-collapse: collapse; width: 500px; } td { padding: 8px } </style> </head> <body> <table border='1'> <?php foreach ($data as $k => $v) { foreach ($v as $k1 => $v1) { // do something (such as output) $vals = str_replace('|||', ', ', $v1); echo "<tr><td>$k</td><td>$k1</td><td>$vals</td></tr>"; } } ?> </table> </body> </html>

Can you post it again, using var_export() instead of var_dump() so we have something processable?

Again, with svg we can add the text shadow and letter spacing <div style='background-color: black; text-align: center;'> <svg width='380' height='80' viewBox='0 0 380 80'> <defs> <style type='text/css'> .olap { font-family: "arial black"; font-size: 60pt; letter-spacing: -15px; fill: #fff; text-shadow: -0.08em 0.03em 0.12em rgba(0, 0, 0, 0.7); } </style> </defs> <text class='olap' x='190' y='70' text-anchor='middle' >OVERLAP</text> </svg> </div>

Try using a shadowed font (such as Google's "Rubik Doodle Shadow) and set the element's letter-spacing attribute to a negative value

Sounds analagous to asking "How important is a knowledge of anatomy for being a surgeion?"

My version, once I'd unravelled your multiple keys (eg branchID & branchCode) on tables and weird naming conventions like "b.plantCode = a.sales_office". (More bruises on my forehead) I am all for single letter table aliases but please make them easier ("b" for billing, "p" for plant, "c" for customerMaster etc) and use two letters if there is a clash (such as "br" for branch). It makes it a lot easier to follow than just a, b, c, d, e regardless of the table name. SELECT branchname , CustomerID , Customername , CustomerSegment , CustomerType , COALESCE(bill.JanBilling, '-') as JanBilling , COALESCE(bill.FebBilling, '-') as FebBilling , COALESCE(bill.MarBilling, '-') as MarBilling , COALESCE(bill.AprBilling, '-') as AprBilling , COALESCE(bill.MayBilling, '-') as MayBilling , COALESCE(bill.JunBilling, '-') as JunBilling , COALESCE(bill.JulBilling, '-') as JulBilling , COALESCE(bill.AugBilling, '-') as AugBilling , COALESCE(bill.SepBilling, '-') as SepBilling , COALESCE(bill.OctBilling, '-') as OctBilling , COALESCE(bill.NovBilling, '-') as NovBilling , COALESCE(bill.DecBilling, '-') as DecBilling FROM ( SELECT br.branchname , b.sold_party as customerid , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END) / 100000 AS DecBilling FROM billing b JOIN plant p ON b.sales_office = p.plantcode JOIN branch br ON p.branchid = br.branchid WHERE b.sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') AND b.product_div NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') GROUP BY branchname, customerid UNION SELECT m.branchname , m.customerid , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM machinemaster m LEFT JOIN billing b ON m.customerid = b.sold_party WHERE m.machinestatus = 'A' AND b.sold_party IS NULL GROUP BY branchname, customerid ) bill JOIN customermaster USING (customerid) ORDER BY branchname, customerid;

I have crossed out columns that don't belong in the tables beacause they are dependent on foreign keys to other tables (such as customer, branch, product etc). I also highlighted those with incorrect types. It's indexing that greatly improves query performance. Fields used in joins should be indexed as should columns frequently used in where clauses. I'll continue to work on your query now.

Sorry, my head's hurting after banging it repeatedly aginst the wall in frustration after having looked at your table structures. Foe example... Columns which you are joining on, like CustomerID, are not indexed. ID columns, which (strangely) you are not using for your joins, are indexed twice (primary key and you also create another key on the same column). The gross_amount column that you are totalling is a varchar column!!! CustomerName, which belongs only in the customer table is repeated in billing and machinemaster tables. NORMALIZE!. And that's before I can comprehend what you want to do.

I know this isn't a CSS solution but, when in doubt, cheat. I used an SVG image. Fonts will differ I I don't know which you used. <body> <header style='text-align: center'> <svg width='90%' viewBox='0 0 500 100' style='max-width: 800;'> <defs> <style type='text/css'> #main { font-family: "arial black"; font-size: 20pt; font-weight: 800; fill: #000; } #better { font-family: "segoe script"; font-size: 40pt; font-weight: 600; fill: #80FF80; } </style> </defs> <text x='338' y='65' id='better'>better</text> <text x='12' y='60' id='main'>Making every day taste</text> </svg> </header> </body> screen width: 1024 screen width: 450

So you are only interested in the 203 customers that are in machinemaster but not in billing?

Could you explain that requirement, particularly "who are not doing the billing"?

Design the data model Create the tables Populate tables with test data And one thing I forgot to mention earlier... RTFM

How did you learn advanced SQL? With practice. How did you start tinkering with data? By designing and building databases to support applications I needed to develop, then developing them using the database. Problem is I've no data to tinker with. Then create some. (See tutorials link in my signature)