Barand

The .php extension merely tells your server to processs any php code that may be in the file. In this case there isn't any, so it is just an HTML file.

Perhaps the PHP isn't working because there isn't any PHP code in there

Reloaded your data and ran this query below (for branch 801) to count the customers from each section of the UNION ... billing machine master not in billing SELECT count(distinct customerid) as total_custs , SUM(JanBilling <> '-') as from_billing , SUM(JanBilling = '-') as from_mach_mast FROM ( SELECT branchname , plantName , 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, p.plantName , 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 sbms.billing b JOIN sbms.plant p ON b.sales_office = p.plantcode JOIN sbms.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') AND br.branchcode = '801' GROUP BY branchname, customerid UNION SELECT m.branchname, Null , m.customerid , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM sbms.machinemaster m LEFT JOIN sbms.billing b ON m.customerid = b.sold_party WHERE m.machinestatus = 'A' AND b.sold_party IS NULL AND m.branchcode='801' GROUP BY branchcode, customerid ) bill LEFT JOIN sbms.customermaster USING (customerid) ORDER BY branchname, customerid ) data; giving these counts +-------------+--------------+----------------+ | total_custs | from_billing | from_mach_mast | +-------------+--------------+----------------+ | 844 | 609 | 235 | +-------------+--------------+----------------+ 1 row in set (2.95 sec) I also ran this one to get the counts for all branches SELECT branchname , SUM(JanBilling <> '-') as from_billing , SUM(JanBilling = '-') as from_mach_mast FROM ( SELECT branchname , plantName , 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, p.plantName , 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 sbms.billing b JOIN sbms.plant p ON b.sales_office = p.plantcode JOIN sbms.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, Null , m.customerid , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM sbms.machinemaster m LEFT JOIN sbms.billing b ON m.customerid = b.sold_party WHERE m.machinestatus = 'A' AND b.sold_party IS NULL GROUP BY branchcode, customerid ) bill LEFT JOIN sbms.customermaster USING (customerid) ORDER BY branchname, customerid ) data GROUP BY branchname; giving +--------------+--------------+----------------+ | branchname | from_billing | from_mach_mast | +--------------+--------------+----------------+ | AHMEDABAD | 624 | 282 | | BANGALORE | 464 | 246 | | BHUVANESHWAR | 245 | 197 | | CHENNAI | 609 | 235 | | COIMBATORE | 418 | 157 | | DELHI | 398 | 216 | | Export | 0 | 66 | | GUWAHATI | 328 | 156 | | HYDERABAD | 348 | 92 | | INDORE | 335 | 212 | | JAIPUR | 290 | 106 | | KOCHI | 266 | 104 | | KOLKATA | 385 | 112 | | KOLKATTA | 0 | 7 | | LUCKNOW | 276 | 128 | | MOHALI | 511 | 266 | | MUMBAI | 420 | 291 | | NAGPUR | 235 | 126 | | PATNA | 233 | 61 | | PROD | 0 | 11 | | Projects | 0 | 23 | | PUNE | 717 | 383 | | QUALITY | 0 | 2 | | RAIPUR | 200 | 56 | | RANCHI | 176 | 76 | | SCEN | 0 | 1 | | SCHWING | 0 | 11 | | VISAKAPATNAM | 241 | 76 | | XCMG | 0 | 1 | +--------------+--------------+----------------+ 29 rows in set (6.18 sec) So where are you getting your number 658 and 71 from?

Try adding some content to the div. Such as... <div class="div-with-curve"> On what planet is this curve at the top? </div>

You customermaster table isn't masterful enough - several used ids are not in there mysql> SELECT COUNT(DISTINCT m.customerid) as unmatched -> FROM machinemaster m -> LEFT JOIN -> customermaster c USING (customerid) -> WHERE m.machinestatus = 'A' -> AND c.customerid IS NULL; +-----------+ | unmatched | +-----------+ | 242 | +-----------+ Try using a LEFT JOIN to the customermaster at the bottom of my query in case the missing rows are amongst those.

I can't reproduce your count with data you provided mysql> SELECT COUNT(DISTINCT customerid) -> FROM machinemaster -> WHERE machinestatus = 'A'; +----------------------------+ | COUNT(DISTINCT customerid) | +----------------------------+ | 8169 | +----------------------------+

When it encounters the "<p>" it is still in the php section of the code and the "<p>" isn't valid php code. You need to exit from php before you enter the html. Move the "?>" line ...

try setting the line-height to same as gradient length...

Any chance you could post a dump of the structure and data for those tables?

@Danishhafeez Again (with amazing consistency)... All you (and your pet AI?) have done is replicate the original problem, demonstrated below <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Bovine Excrement</title> <style type='text/css'> .class { color: transparent; /* Hides the original text */ background: linear-gradient(to bottom, #FFFFFF 0%, #CCCCCC 100%); -webkit-background-clip: text; /* For Safari */ background-clip: text; -webkit-text-fill-color: transparent; /* Ensures the text is transparent */ font-weight: bold; /* Optionally, if your text is bold */ } </style> </head> <body> <h1 class='class'> Line 1<br> Line 2<br> Line 3 </h1> </body> </html> Giving

One way is to find the positions of the "]" and and the "(" and grab the text between those points. $qa = [ "[Question - Geography Chapter2] How would you describe humans' relationship with the physical environment? (Page 42)", "[Question - Geography Chapter4] What is a natural resource? (Page 67)", "[Question - Geography Chapter3] What are two or three resources which you cannot do without? What are the reasons for your choices? (Page 52)" ]; foreach ($qa as $k => &$q) { $p1 = strpos($q, ']'); $p2 = strpos($q, '('); $q = trim(substr($q, $p1+1, $p2-$p1-1)); }

This code will migrate your data from "users_shifts" to the new format "user_shifts"... <?php $pdo->exec("DROP TABLE IF EXISTS user_shifts"); $pdo->exec("CREATE TABLE `user_shifts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `dow` tinyint(4) DEFAULT NULL COMMENT '0 (Mon) - 6 (Sun)', `start_time` time DEFAULT NULL, `finish_time` time DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_user_shifts_user_id` (`user_id`,`dow`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci "); $res = $pdo->query("SELECT users_shift_id , user_id , monday_start_time , monday_finish_time , tuesday_start_time , tuesday_finish_time , wednesday_start_time , wednesday_finish_time , thursday_start_time , thursday_finish_time , friday_start_time , friday_finish_time , saturday_start_time , saturday_finish_time , sunday_start_time , sunday_finish_time FROM users_shifts "); while ($r = $res->fetch(PDO::FETCH_NUM)) { $data = []; for ($d=0; $d<7; $d++) { if ($r[$d*2+2]) { $data[] = sprintf("(%d,%d,'%s','%s')", $r[1], $d, $r[2*$d + 2], $r[2*$d + 3] ); } else { $data[] = sprintf("(%d,%d,null,null)", $r[1], $d ); } } $pdo->exec("INSERT INTO user_shifts (user_id, dow, start_time, finish_time) VALUES " . join(',', $data) ); } echo "FINISHED"; ?> and this is the query revised to use the new format... WITH minutes as ( WITH RECURSIVE dates(n, dt) AS ( SELECT 1, '2024-06-14' UNION ALL SELECT n+1, dt+INTERVAL 1 DAY FROM dates WHERE dt < CURDATE() + INTERVAL 60 DAY ) SELECT user_id , DAYNAME(dt) as day , dt , COALESCE(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30, 0) as mins , SUM(COALESCE(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30, 0)) OVER (PARTITION BY user_id ORDER BY dt) as cum FROM dates d JOIN user_shifts us ON weekday(d.dt) = us.dow ) SELECT * FROM minutes WHERE cum - mins < 2400 ORDER BY user_id, dt; giving

Does this fit the bill? WITH minutes as ( WITH RECURSIVE dates(n, dt) AS ( SELECT 1, '2024-06-14' UNION ALL SELECT n+1, dt+INTERVAL 1 DAY FROM dates WHERE dt < CURDATE() + INTERVAL 60 DAY ) SELECT user_id , DAYNAME(dt) , dt , COALESCE(CASE WEEKDAY(dt) WHEN 0 THEN TIMESTAMPDIFF(MINUTE, monday_start_time, monday_finish_time)-30 WHEN 1 THEN TIMESTAMPDIFF(MINUTE, tuesday_start_time, tuesday_finish_time)-30 WHEN 2 THEN TIMESTAMPDIFF(MINUTE, wednesday_start_time, wednesday_finish_time)-30 WHEN 3 THEN TIMESTAMPDIFF(MINUTE, thursday_start_time, thursday_finish_time)-30 WHEN 4 THEN TIMESTAMPDIFF(MINUTE, friday_start_time, friday_finish_time)-30 WHEN 5 THEN TIMESTAMPDIFF(MINUTE, saturday_start_time, saturday_finish_time)-30 WHEN 6 THEN TIMESTAMPDIFF(MINUTE, sunday_start_time, sunday_finish_time)-30 ELSE 0 END, 0) as mins , SUM(COALESCE(CASE WEEKDAY(dt) WHEN 0 THEN TIMESTAMPDIFF(MINUTE, monday_start_time, monday_finish_time)-30 WHEN 1 THEN TIMESTAMPDIFF(MINUTE, tuesday_start_time, tuesday_finish_time)-30 WHEN 2 THEN TIMESTAMPDIFF(MINUTE, wednesday_start_time, wednesday_finish_time)-30 WHEN 3 THEN TIMESTAMPDIFF(MINUTE, thursday_start_time, thursday_finish_time)-30 WHEN 4 THEN TIMESTAMPDIFF(MINUTE, friday_start_time, friday_finish_time)-30 WHEN 5 THEN TIMESTAMPDIFF(MINUTE, saturday_start_time, saturday_finish_time)-30 WHEN 6 THEN TIMESTAMPDIFF(MINUTE, sunday_start_time, sunday_finish_time)-30 ELSE 0 END, 0)) OVER (PARTITION BY user_id ORDER BY dt) as cum FROM dates CROSS JOIN users_shifts ) SELECT * FROM minutes WHERE cum - mins < 2400 ORDER BY user_id, dt; +---------+-------------+------------+------+------+ | user_id | DAYNAME(dt) | dt | mins | cum | +---------+-------------+------------+------+------+ | 1 | Friday | 2024-06-14 | 450 | 450 | | 1 | Saturday | 2024-06-15 | 0 | 450 | | 1 | Sunday | 2024-06-16 | 0 | 450 | | 1 | Monday | 2024-06-17 | 450 | 900 | | 1 | Tuesday | 2024-06-18 | 450 | 1350 | | 1 | Wednesday | 2024-06-19 | 450 | 1800 | | 1 | Thursday | 2024-06-20 | 450 | 2250 | | 1 | Friday | 2024-06-21 | 450 | 2700 | | 2 | Friday | 2024-06-14 | 450 | 450 | | 2 | Saturday | 2024-06-15 | 450 | 900 | | 2 | Sunday | 2024-06-16 | 450 | 1350 | | 2 | Monday | 2024-06-17 | 0 | 1350 | | 2 | Tuesday | 2024-06-18 | 0 | 1350 | | 2 | Wednesday | 2024-06-19 | 450 | 1800 | | 2 | Thursday | 2024-06-20 | 450 | 2250 | | 2 | Friday | 2024-06-21 | 450 | 2700 | +---------+-------------+------------+------+------+ I would strongly recommend that you redesign your "users_shifts" so that, instead of a spreadsheet, you have only a single set of start/finish times per row (ie 7 rows per user per week). This will remove the need for the CASE statements (Using dates d JOIN user_shifts us ON weekday(d.dt) = us.dow will match the day of week for you). CREATE TABLE `user_shifts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `dow` tinyint(4) DEFAULT NULL COMMENT '0 (Mon) - 6 (Sun)', `start_time` time DEFAULT NULL, `finish_time` time DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_user_shifts_user_id` (`user_id`, `dow`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; I'll try it out at my end and post the revised query.

Having had a closer look at your query, I am wondering why you have these lines... LEFT JOIN users u ON u.user_id = us.user_id LEFT JOIN users_tasks ut on ut.user_id = u.user_id They are both LEFT-JOINED, so you don't need them to be there, and you never reference any columns from them (so you aren't checking if they aren't there or using anything from them). The LEFT JOINS will slow down the query though.

It flows from top to bottom, as normal.

In other words I have no idea what the original source of your data looks like, and I have no idea what you want the final display to look like. Any processing to go from (1) to (2) is completely dependent on knowing those. All you have given us so far is an intermediate array that isn't what you want.

Not without any psychic abilities.

As an aside - if the array you posted isn't in the structure you need, why did you create it like that in the first place?

Without your actual data and structure I can't do more than give abstract examples.

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