Barand

Same thing using PHP <?php const HOST = '????'; const USERNAME = '????'; const PASSWORD = '????'; const DBNAME = '????'; $staffno = 3; $appno = 4; mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); // Tell mysql to report errors $conn = mysqli_connect(HOST,USERNAME,PASSWORD,DBNAME); // connect to DB $conn->set_charset('utf8'); ShowData($conn); $stmt = $conn->prepare("UPDATE test_91 SET staffno = ? WHERE appno = ? "); $stmt->bind_param('ii', $staffno, $appno); $stmt->execute(); echo "<br><b>Table updated here</b><br>"; ShowData($conn); function ShowData($conn) { $res = $conn->query("SELECT * FROM test_91"); echo '<pre>'; printf("%-10s%-10s%-10s<br><br>", 'AppID', 'AppNo', 'StaffNo'); foreach ($res as $row) { vprintf("%-10s%-10s%-10s<br>", $row); } echo "</pre>\n"; } ?> Output... AppID AppNo StaffNo 1 2 2 2 3 2 3 4 0 4 4 0 5 4 0 6 4 0 Table updated here AppID AppNo StaffNo 1 2 2 2 3 2 3 4 3 4 4 3 5 4 3 6 4 3

No problem with the query at this end mysql> select * from test_91; +-------+-------+---------+ | appid | appno | staffno | +-------+-------+---------+ | 1 | 2 | 2 | | 2 | 3 | 2 | | 3 | 4 | 0 | | 4 | 4 | 0 | | 5 | 4 | 0 | | 6 | 4 | 0 | +-------+-------+---------+ 6 rows in set (0.00 sec) mysql> UPDATE test_91 -> SET staffno = 3 -> WHERE appno = 4; Query OK, 4 rows affected (0.09 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from test_91; +-------+-------+---------+ | appid | appno | staffno | +-------+-------+---------+ | 1 | 2 | 2 | | 2 | 3 | 2 | | 3 | 4 | 3 | | 4 | 4 | 3 | | 5 | 4 | 3 | | 6 | 4 | 3 | +-------+-------+---------+ 6 rows in set (0.00 sec)

One query will suffice - you don't need four. And why are they updating 4 different tables? UPDATE tablename SET staffno = 3 WHERE appno = 4;

Did you solve the problem of those cumulative values when there were no minutes for the day? Was it anything to do with your use of @ variables?

A slightly shorter alternative with TIME type columns is TIME_TO_SEC(TIMEDIFF(finish_time, start_time)) DIV 60 - 30 AS mins

If you want to comment out a line of SQL, use "--" followed by a space, not "#" EG... -- This is a sql comment

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.