Barand

$result = $con->query("select fname , timestampdiff(year, dob, curdate()) as age from employee "); foreach ($result as $row) { echo "{$row['fname']} {$row['age']} <br>"; } giving

mysql> select * from employee; +-------+-------+---------+------------+ | empid | fname | lname | dob | +-------+-------+---------+------------+ | 1 | Peter | Smith | 1985-01-26 | | 2 | Paul | Hartley | 1973-12-02 | | 3 | Mary | Baker | 1980-04-11 | | 4 | Jane | Doe | 1970-11-28 | +-------+-------+---------+------------+ 4 rows in set (0.00 sec) mysql> select fname -> , timestampdiff(year, dob, curdate()) as age -> from employee; +-------+------+ | fname | age | +-------+------+ | Peter | 37 | | Paul | 48 | | Mary | 42 | | Jane | 51 | +-------+------+ 4 rows in set (0.00 sec)

You might prefer this version <?php class PriceCalculator { private $start; private $end; private $price = [ 0 => [ 98, 128], 1 => [ 88, 118], 2 => [ 88, 118], 3 => [ 88, 118], 4 => [ 88, 118], 5 => [ 88, 118], 6 => [ 98, 128] ]; public $periods = []; public $total = 0; public function __construct ($time1, $time2) { $this->start = new DateTime($time1); $this->end = new DateTime($time2); } public function calculate() { $total = 0; $dp = new DatePeriod($this->start, new DateInterval('PT1M'), $this->end ); $prevday = $prevpk = $prevh = null; foreach ($dp as $min) { $day = $min->format('w'); $peak = '02' <= $min->format('H') && $min->format('H') < '18' ? 0 : 1; if ($prevday != $min->format('Y-m-d') || $prevpk != $peak) { if (!isset($this->periods[$min->format('d H')])) { $this->periods[$min->format('d H')] = ['start' => $min->format('H:i:s'), 'end' => null, 'dow' => $min->format('D'), 'peak' => $peak, 'date' => $min->format('Y-m-d'), 'price' => 0 ]; } $prevday = $min->format('Y-m-d'); $prevpk = $peak; $prevh = $min->format('d H'); } $this->periods[$prevh]['end'] = $min->modify('+1 minute')->format('H:i:s'); $this->periods[$prevh]['price'] += $this->price[$day][$prevpk]/60; $this->total += $this->price[$day][$prevpk]/60; } } } $time1 = "2022-04-23 18:00:00"; $time2 = "2022-04-25 18:00:00"; $instance = new PriceCalculator($time1, $time2); $instance->calculate(); echo number_format($instance->total, 2) . '<br><pre>'; foreach ($instance->periods as $arr) { printf ('| %s | %s | %s - %s | %-8s | %10.2f |<br>', $arr['date'] , $arr['dow'] , $arr['start'] , $arr['end'] , $arr['peak'] ? 'Peak' : 'Off-peak' , $arr['price'] ); } echo '</pre>'; giving 5,004.00 | 2022-04-23 | Sat | 18:00:00 - 00:00:00 | Peak | 768.00 | | 2022-04-24 | Sun | 00:00:00 - 02:00:00 | Peak | 256.00 | | 2022-04-24 | Sun | 02:00:00 - 18:00:00 | Off-peak | 1568.00 | | 2022-04-24 | Sun | 18:00:00 - 00:00:00 | Peak | 768.00 | | 2022-04-25 | Mon | 00:00:00 - 02:00:00 | Peak | 236.00 | | 2022-04-25 | Mon | 02:00:00 - 18:00:00 | Off-peak | 1408.00 |

The rows that are output are the peak and off-peak for each day.

This may be what you want (who knows?) class PriceCalculator { private $start; private $end; private $price = [ 0 => [ 98, 128], 1 => [ 88, 118], 2 => [ 88, 118], 3 => [ 88, 118], 4 => [ 88, 118], 5 => [ 88, 118], 6 => [ 98, 128] ]; public $periods = []; public $total = 0; public function __construct ($time1, $time2) { $this->start = new DateTime($time1); $this->end = new DateTime($time2); } public function calculate() { $total = 0; $dp = new DatePeriod($this->start, new DateInterval('PT1M'), $this->end ); $prevday = $prevpk = null; foreach ($dp as $min) { $day = $min->format('w'); $peak = '02' <= $min->format('H') && $min->format('H') < '18' ? 0 : 1; if ($prevday != $min->format('Y-m-d') || $prevpk != $peak) { if (!isset($this->periods[$min->format('Y-m-d')][$peak])) { $this->periods[$min->format('Y-m-d')][$peak] = ['start' => $min->format('H:i:s'), 'end' => null, 'dow' => $min->format('D'), 'price' => 0]; } $prevday = $min->format('Y-m-d'); $prevpk = $peak; } $this->periods[$prevday][$prevpk]['end'] = $min->modify('+1 minute')->format('H:i:s'); $this->periods[$prevday][$prevpk]['price'] += $this->price[$day][$prevpk]/60; $this->total += $this->price[$day][$prevpk]/60; } } } $time1 = "2022-04-08 12:00:00"; $time2 = "2022-04-09 18:00:00"; $instance = new PriceCalculator($time1, $time2); $instance->calculate(); echo number_format($instance->total, 2) . '<br><pre>'; foreach ($instance->periods as $date => $darr) { foreach ($darr as $pk => $arr) { printf ('| %s | %s | %s | %s | %-8s | %10.2f |<br>', $date , $arr['dow'] , $arr['start'] , $arr['end'] , $pk ? 'Peak' : 'Off-peak' , $arr['price'] ); } } echo '</pre>'; Giving 3,060.00 | 2022-04-08 | Fri | 12:00:00 | 18:00:00 | Off-peak | 528.00 | | 2022-04-08 | Fri | 18:00:00 | 00:00:00 | Peak | 708.00 | | 2022-04-09 | Sat | 00:00:00 | 02:00:00 | Peak | 256.00 | | 2022-04-09 | Sat | 02:00:00 | 18:00:00 | Off-peak | 1568.00 |

I am beginning to understand. When you say "column" you really mean "row". I thought you wanted to process multiple rentals (from a database table, for example), but what you want is to process a single rental but break it down into multiple peak and off-peak periods. I'll await your answer to @mac_gyver's earlier question ...

Where does it say that? All their examples given show a date range and a total price for that range. (Plus the recent request for a grand total)

What date range? What columns? Can you explain the process you are having difficulty with?

The purpose of that is to change a date string (eg '19-04-2022') which is in %d-%m-%Y format to a correct DATE format. RTFM - https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date

<?php $test['0000'] = ['address' =>'tes0']; $test['1111'] = ['id'=>'tes1', 'name'=>'tes11', 'address' =>'tes111']; $test['2222'] = ['id'=>'tes2', 'name'=>'tes22', 'address' =>'tes222']; $test['3333'] = ['id'=>'tes3', 'name'=>'tes33']; $nil = ['id'=>'NIL', 'name'=>'NIL', 'address' =>'NIL']; $test = array_map( function($v) use ($nil) { return array_replace($nil, $v); } , $test); PS Definite feeling of deja vu here

What have you tried to get the total?

You can change the format of your d-m-Y dates to the correct Y-m-d format with this query UPDATE date_test SET birthday = str_to_date(birthday, '%d-%m-%Y') WHERE locate('-', birthday) = 3; For example mysql> CREATE TABLE `date_test` ( -> `date_test_id` int(11) NOT NULL AUTO_INCREMENT, -> `the_date` varchar(15) DEFAULT NULL, -> PRIMARY KEY (`date_test_id`) -> ) ; Query OK, 0 rows affected (0.28 sec) mysql> INSERT INTO `josen2`.`date_test` (`the_date`) VALUES ('2022-01-01'), ('01-01-2022'); Query OK, 2 rows affected (0.14 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM josen2.date_test; +--------------+------------+ | date_test_id | the_date | +--------------+------------+ | 1 | 2022-01-01 | | 2 | 01-01-2022 | +--------------+------------+ 2 rows in set (0.00 sec) mysql> UPDATE date_test -> SET the_date = str_to_date(the_date, '%d-%m-%Y') -> WHERE locate('-', the_date) = 3; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM josen2.date_test; +--------------+------------+ | date_test_id | the_date | +--------------+------------+ | 1 | 2022-01-01 | | 2 | 2022-01-01 | +--------------+------------+ 2 rows in set (0.00 sec) Once that has been done and all dates in the correct format you can change the column from varchar to date. (The ALTER query will fail if any dates have an incorrect format) mysql> ALTER TABLE `josen2`.`date_test` -> CHANGE COLUMN `the_date` `the_date` DATE NULL DEFAULT NULL; Query OK, 2 rows affected (0.87 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM josen2.date_test; +--------------+------------+ | date_test_id | the_date | +--------------+------------+ | 1 | 2022-01-01 | | 2 | 2022-01-01 | +--------------+------------+ 2 rows in set (0.01 sec)

I commented on your d-m-y date formats in your last topic

From the manual (https://www.php.net/manual/en/pdostatement.rowcount.php) You could use this to see if a row was returned... $stmt->execute(); return $stmt->fetch(); However a better way would be to put a UNIQUE constraint on the username column. You then just insert the new username but check for a "duplicate key" error. This saves you from having check every insert and you only need to take action if the insert failed.

The PHP DateTime::diff() method provides a very convenient way of getting the days, hours, minutes and seconds components of a time difference so this script uses an AJAX request on loading to get the time remaining. From then on, it calls a javascript function every second to reduce the time displayed by one second. This greatly reduces network traffic and gives a consistent update performance. Repeatedly using AJAX could sometimes result in delays preventing a regular countdown interval. <?php ################################################################################################################## # # # THIS SECTION HANDLES THE AJAX REQUEST AND EXITS TO SEND RESPONSE (Days,hrs, mins, secs remaining) # # # if (isset($_GET['ajax'])) { if ($_GET['ajax'] == 'countdown') { $remain = ['days' => 0, 'hrs' => 0, 'mins' => 0, 'secs' => 0]; $dt1 = new DateTime( $_GET['target'] ); $dt2 = new DateTime('now'); if ($dt1 > $dt2) { $diff = $dt1->diff($dt2); $remain['days'] = $diff->days; $remain['hrs'] = $diff->h; $remain['mins'] = $diff->i; $remain['secs'] = $diff->s; } exit(json_encode($remain)); } } # # ################################################################################################################### $target = '2022-04-30 23:59:59'; // SET OR GET TARGET TIME HERE $targ = new DateTime($target); $target_time = $targ->format('g:ia'); $target_date = $targ->format('F jS Y'); ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Countdown</title> <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script> <script type='text/javascript'> var inter $().ready( function() { get_time_remaining() // call AJAX request to get remaining time inter = setInterval(countdown, 1000) // set timer to call "countdown()" function every second }) function countdown() { let s = parseInt($("#secs").html()) // get current time remaining let m = parseInt($("#mins").html()) let h = parseInt($("#hrs").html()) let d = parseInt($("#days").html()) if (d==0 && h==0 && m==0 && s==0) { // exit when target time is reached clearInterval(inter) $(".remain").css("background-color", "red") return } s--; // reduce display by 1 second if (s < 0) { s = 59; m-- } if (m < 0) { m = 59 h-- } if (h < 0) { h = 23 d-- } if (d < 0) { d = 0 } $("#days").html(d) // redisplay new values $("#hrs").html(h) $("#mins").html(m) $("#secs").html(s) } function get_time_remaining() { $.get( // make AJAX request "", {"ajax":"countdown", "target":$("#target").val()}, function(resp) { // put response values in display fields $("#days").html( resp.days ) $("#hrs").html( resp.hrs ) $("#mins").html( resp.mins ) $("#secs").html( resp.secs ) }, "JSON" ) } </script> <style type='text/css'> body { font-family: verdana, sans-serif; font-size: 11pt; } header { padding: 8px; text-align: center; width: 600px; margin: 20px auto; background-color: #F0F0F0; } .target { color: #006EFC; font-size: 16pt; } table { border-collapse: collapse; width: 400px; margin: 0 auto; } td, th { padding: 8px; text-align: center; width: 25%; } .remain { font-size: 24pt; color: white; background-color: black; border: 1px solid white; } </style> </head> <body> <header> <p>Countdown to</p> <p class='target'><?=$target_time?> on <?=$target_date?> </p> <!-- make target time available to javascript --> <input type='hidden' id='target' value='<?=$target?>' > <table border='0'> <tr><th>Days</th><th>Hours</th><th>Mins</th><th>Secs</th></tr> <tr> <td class='remain' id='days'>0</td> <td class='remain' id='hrs'>0</td> <td class='remain' id='mins'>0</td> <td class='remain' id='secs'>0</td> </tr> </table> </header> </body> </html>

It's one of your next steps. The first thing you need to do is understand why that code I last posted works and yours didn't.

I'd define it something like this CREATE TABLE `comments` ( `post_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `email` varchar(50) DEFAULT NULL, `comments` text, `approvedby` varchar(50) DEFAULT NULL, `status` tinyint(4) DEFAULT NULL, PRIMARY KEY (`post_id`) ) then, as stated earlier, datetime and post_id are generated automatically and can be excluded from the INSERT. As @ginerjm said, don't use varchar for dates. They should be DATE, DATETIME or TIMESTAMP and the format should be yyyy-mm-dd. Store them for functionality, not prettiness. Format them as required on output or in the query.

Looks to me like you have 5 placeholders and 4 bound values for them, hence the error message (They usually tell you what the problem is - you just have to read them) What does your table structure look like? If datetime is the current timestamp and post_id is an auto_incremented primary key then they don't need to be in the insert statement.

Let's shoot this horse and put it out of its misery. There's still work to do but it illustrates another solution to your main problem. $vehicles = [ 'Plane', 'SuperCar', 'Yacht' ]; $res = $db->query("SELECT f.formid , f.firstname , f.lastname , v.vehselection FROM form f LEFT JOIN vehicle v USING (formid) ORDER BY lastname "); $data = []; foreach ($res as $r) { // process results // for each person create an array element // that contains an array of the vehicles owned // by that person if (!isset($data[$r['formid']])) { $data[$r['formid']] = [ 'name' => $r['firstname'] . ' ' . $r['lastname'], 'vehTypes' => [] ]; } $data[$r['formid']]['vehTypes'][] = $r['vehselection']; } // the data array looks like this // // $data = Array ( // // [193] => Array // ( // [name] => John Atkins // [vehTypes] => Array // ( // [0] => SuperCar // [1] => Plane // ) // // ) // // [192] => Array // ( // [name] => Frank Lampard // [vehTypes] => Array // ( // [0] => Yacht // [1] => Plane // ) // // ) // // ... // ) ?> <style type='text/css'> table { border-collapse: collapse; width: 600px; margin: 20px auto; } td, th { padding: 8px; } </style> <table border='1'> <tr> <th>Name</th> <th>Vehicles</th> </tr> <?php foreach ($data as $fid => $person) { echo "<tr><td>{$person['name']}</td><td>"; // now loop through the $vehicles array (top of script) // outputting a checkbox for each whic is checked // if the person owns one of that type foreach ($vehicles as $vtype) { $chk = in_array($vtype, $person['vehTypes']) ? 'checked' : ''; echo "<label> <input type='checkbox' name='vehicle[$fid][]' value='$vtype' $chk> $vtype </label> <br>"; } echo "</td></tr>\n"; } ?> </table>

Is that because they want or need to, or because there is nothing to stop them them doing it accidentally? You could add a UNIQUE constrint on (code, datetime) columns to prevent accidental duplicates. BTW, as you will be testing deletions (not undoable) I recommend you back up before testing on any live data.

As all records in a relational DB should have a primary key I'll assume yours do. BEFORE +---------------------+------+----------------+ | 1 | hide | 20211123073000 | | 2 | hide | 20211123073000 | | 3 | hide | 20211123074500 | | 4 | hide | 20211123074500 | | 5 | | 20211123080000 | | 6 | hide | 20211123080000 | | 7 | | 20211123081500 | | 8 | hide | 20211123081500 | | 9 | | 20211123083000 | | 10 | hide | 20211123083000 | +---------------------+------+----------------+ QUERY DELETE teacher_schedule FROM teacher_schedule JOIN ( SELECT code , sched_time , MAX(teacher_schedule_id) as teacher_schedule_id FROM teacher_schedule WHERE code = 'hide' GROUP BY code, sched_time HAVING count(*) > 1 ) dupes USING (teacher_schedule_id); AFTER +---------------------+------+----------------+ | teacher_schedule_id | code | sched_time | +---------------------+------+----------------+ | 1 | hide | 20211123073000 | | 3 | hide | 20211123074500 | | 5 | | 20211123080000 | | 6 | hide | 20211123080000 | | 7 | | 20211123081500 | | 8 | hide | 20211123081500 | | 9 | | 20211123083000 | | 10 | hide | 20211123083000 | +---------------------+------+----------------+

What is your table's definition? Does each record have a unique id?

Use a DatePeriod() object to generate the NIL array. I'd show the code but I am having difficulty processing that image of your data in my test code. (Hint: in future, use var_export() with arrays)

I'm guessing the implicit join is screwing with the aliases when it tries to join to the subquery

Let it be a lesson always to use explicit join syntax and not FROM A, B