Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,602

  • Joined

  • Last visited

  • Days Won

    830

 Content Type 

Everything posted by Barand

  1. Barand
    I can't reproduce your count with data you provided mysql> SELECT COUNT(DISTINCT customerid) -> FROM machinemaster -> WHERE machinestatus = 'A'; +----------------------------+ | COUNT(DISTINCT customerid) | +----------------------------+ | 8169 | +----------------------------+
  2. Barand
    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 ...
  3. Barand
    try setting the line-height to same as gradient length...
  4. Barand
    Any chance you could post a dump of the structure and data for those tables?
  5. Barand
    @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
  6. Barand
    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)); }
  7. Barand
    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
  8. Barand
    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.
  9. Barand
    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.
  10. Barand
    It flows from top to bottom, as normal.
  11. Barand
    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.
  12. Barand
    Not without any psychic abilities.
  13. Barand
    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?
  14. Barand
    Without your actual data and structure I can't do more than give abstract examples.
  15. 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
  16. Barand
    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)
  17. Barand
    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;
  18. Barand
    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?
  19. Barand
    A slightly shorter alternative with TIME type columns is TIME_TO_SEC(TIMEDIFF(finish_time, start_time)) DIV 60 - 30 AS mins
  20. Barand
    If you want to comment out a line of SQL, use "--" followed by a space, not "#" EG... -- This is a sql comment
  21. 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;
  22. Barand
    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 | +----+-----------+------------+------+------+
  23. Barand
    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 | +----+-----------+------------+------+------+
  24. Barand
    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) )
  25. Barand
    unset($data['sub']); unset($data['level']); Have you ever considered reading the manual at php.net?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.