Barand

Looks like you were trying to write an empty string value into an column whose type is integer.

Existing offers that will be affected by the new offer are those ... WHERE item_id = new_tem_id AND start_time <= new_end_time AND end_time >= new_start_time new start new end | | | | |--------------| | | not affected | | | | |-------------| not affected | | | | |--------------------------------------------| change | | |-------------------| | change | | | |-----------------| change | | | |---------| | superseded | | | |

Your input dates will be in the $_POST array. Don't put user input data directly into your sql, use prepared queries. <form action="vehicle_cleaning-stats.php" method="post"> <input type="date" name="date_from"><input type="date" name="date_to"><input type="submit" value="Search"> </form> php if ($_SERVER['REQUEST_METHOD'] == 'POST') { $stmt = $pdo->prepare("SELECT bus_no , COUNT(*) as total FROM `vehicle_cleaning` WHERE date_cleaned BETWEEN ? AND ? GROUP BY bus_no "); $stmt->execute( [ $_POST['date_from'], $_POST['date_to'] ] ); echo "<table>"; foreach ($stmt as $row) { echo "<tr>"; echo "<td>" . $row['bus_no'] . "</td>"; echo "<td>" . $row['total'] . "</td>"; echo "</tr>"; } echo "</table>"; }

Have you tried it with COUNT(*) in php? foreach($dbh->query("SELECT bus_no , COUNT(*) as total FROM `vehicle_cleaning` WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' GROUP BY bus_no") as $row) { echo "<tr>"; echo "<td>" . $row['bus_no'] . "</td>"; echo "<td>" . $row['total'] . "</td>"; echo "</tr>"; } Re your datetime problem, what is the timezone setting on your mysql server?

That message is telling you there is not a column called "message" in the contact_data table. Check your database - could be a spelling mistake in your table definition. BTW, dont use "-" in SQL identifiers (table and column names) but use "_" instead. Hyphens can be interpreted as minus signs in queries. For example SELECT first-name FROM user will be interpreted as column first minus column name.

"comment" table should not contain the user's name (that should only be in the "users" table) - you have the user_id therefore you know the name. I'd go with CREATE TABLE `news` ( `news_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `item_text` text, `category` varchar(20) DEFAULT NULL, `item_update` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `author` varchar(50) DEFAULT NULL, PRIMARY KEY (`news_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, `email` varchar(50) DEFAULT NULL, `username` varchar(50) DEFAULT NULL, `password` varchar(300) DEFAULT NULL, `status` enum('admin','user') DEFAULT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `comment` ( `comment_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `news_id` int(11) DEFAULT NULL, `comment` text, `comment_update` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`comment_id`), KEY `idx_comment_user_id` (`user_id`), KEY `idx_comment_news_id` (`news_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The "days_ago" will evaluate to a negative number if the date_cleaned or date_inservice is in the future. date_cleaned should always be historical (or it's an error) but it is feasible that there may be new buses in the table which come into service in the next few days. We can check that only current buses are reported by WHERE CURDATE() BETWEEN date_inservice AND IF(date_outservice=0, '9999-12-31', date_outservice) If you only want to list buses where cleaning is required, the condition would be WHERE DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency If you only want to show the bus no and date_last_cleaned, only select those. Then the query becomes SELECT v.bus_no as 'Bus No' , COALESCE(last_cleaned, date_inservice) as 'Last Cleaned' FROM vehicle_specs v LEFT JOIN ( SELECT bus_no , MAX(date_cleaned) as last_cleaned FROM vehicle_cleaning GROUP BY bus_no ) c USING (bus_no) WHERE CURDATE() BETWEEN date_inservice AND IF(date_outservice=0, '9999-12-31', date_outservice) AND DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency +--------+--------------+ | Bus No | Last Cleaned | +--------+--------------+ | EV-02 | 2022-04-22 | | EV-03 | 2022-04-22 | | EV-04 | 2018-01-01 | +--------+--------------+

Just this once <?php const HOST = 'localhost'; const USERNAME = '????'; const PASSWORD = '????'; const DATABASE = 'test'; // default db function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } $pdo = pdoConnect(); $result = $pdo->query("SELECT v.bus_no as BusNo , v.rego as RegNo , COALESCE(last_cleaned, date_inservice) as last_cleaned , DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) as days_ago , v.frequency , CASE WHEN DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency THEN 'REQUIRED' ELSE 'OK' END as cleaning FROM vehicle_specs v LEFT JOIN ( SELECT bus_no , MAX(date_cleaned) as last_cleaned FROM vehicle_cleaning GROUP BY bus_no ) c USING (bus_no)"); $row = $result->fetch(); $tdata = ''; $theads = "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n"; do { $tdata .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n"; } while ($row = $result->fetch()) ; ?> <!DOCTYPE html> <html> <head> <meta http-equiv="content-language" content="en"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <head> <title>Example</title> <style type='text/css'> body { font-family: calibri, sans-serif; font-size: 12pt; } table { border-collapse: collapse; width: 600px; } th { background-color: black; color: white; padding: 8px; } td { padding: 4px 8px; } </style> </head> <body> <table border='1'> <?= $theads ?> <?= $tdata ?> </table> </body> </html>

You don't need the "WHERE 1" (even though Dreamweaver always output that in its generated queries). Just omitting the WHERE clause altogether will give the same result

Try SELECT v.bus_no , v.rego , COALESCE(last_cleaned, date_inservice) as last_cleaned , DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) as days_ago , v.frequency , CASE WHEN DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency THEN 'REQUIRED' ELSE 'OK' END as cleaning FROM vehicle_specs v LEFT JOIN ( SELECT bus_no , MAX(date_cleaned) as last_cleaned FROM vehicle_cleaning GROUP BY bus_no ) c USING (bus_no); +--------+--------+--------------+----------+-----------+----------+ | bus_no | rego | last_cleaned | days_ago | frequency | cleaning | +--------+--------+--------------+----------+-----------+----------+ | EV-01 | XQ63MV | 2022-04-18 | 12 | 5 | REQUIRED | | EV-02 | 914XZP | 2022-04-22 | 8 | 5 | REQUIRED | | EV-03 | 913XZP | 2022-04-22 | 8 | 5 | REQUIRED | | EV-04 | 915XZP | 2018-01-01 | 1580 | 5 | REQUIRED | | EV-05 | 916XZP | 2022-04-17 | 13 | 15 | OK | +--------+--------+--------------+----------+-----------+----------+ Note EV-04 hasn't been cleaned since it came into service - so must be due by now!

Two-thirds of the way there. What have you tried so far?

mysqli has a fetch_all() method and PDO has a fetchAll() method. Both of the above would create that array for you. Does ADODB have the equivalent?

It might be useful to use that in your code. Your code only shows comments for the $_SESSION{'id'] value. Would an admin not want to see/delete all comments? Can a none admin see all comments even they can only delete their own?

How do you know if a user is an admin or not?

I don't understand why you would end offers at random times during they day. You buy four items for which the offer label said 50% off. Part way through your checkout the offer finishes, so you you get one of items half price and pay full price for the other three. 1 unhappy customer. You may as well do away with checkout tills and instal a roulette wheel as customers, if they carry on shopping there, won't know what they will be charged.

If the terms column always contained a qty and a value I would agree with you, but supermarkets have many different types of offer. Buy one get one free Buy 2 cabbages, get an Easter egg half price Buy 1 item from the main course range, one from the desserts range and one from the specified wine selection all for $10. In these cases the attributes are different for the various offer types, which could be handled by an EAV model but they are a PITA to query. JSON is another way to do it.

With more than a little rewriting (avoiding missing variable definitions, mis-spelt function names and illegal syntax) <?php const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = ''; const DATABASE = 'test'; // default db function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } ### CODE ABOVE BELONGS IN AN INCLUDED FILE ################################################################### $pdo = pdoConnect('Water Tower 2000 r'); if ($_SERVER['REQUEST_METHOD']=='POST') { $stmt = $pdo->prepare("SELECT type FROM login WHERE username = ? AND password = ? "); $stmt->execute( [ $_POST['username'], $_POST['pwd'] ] ); $type = $stmt->fetchColumn(); switch ($type) { case 'Admin': header("Location: admin.html"); exit; case 'Parent': header("Location: parent.html"); exit; case 'Swimmer': header("Location: swimmer.html"); exit; default: exit("Unknown user type"); } }

That code wouldn't run even with a mysql_ library, so why bother?

Your deleteList() function (as it says on the tin) deletes the whole list. You need to to delete just the list item that is doubleclicked Here's my version <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script> <script type='text/javascript'> $().ready(function() { $("#addNew").click(function () { let txt = $("#addItem").val() // get the item text let li = $("<li>", {"text":txt, "class":"sItem"}) // create an <li> item $(li).dblclick(function() { // add dblclick event handler $(this).remove() $("#addItem").focus() }) $("#sList").append(li); // append li to ol item $("#addItem").val("").focus() // clear input field and ready for next input }) $("#addItem").focus() }) </script> [edit] PS Yours always deletes the first item in the list; mine deletes the one that is doubleclicked.

Paying attention to the spelling would give it more of a chance. $email= new PHPMailer(); ^ echo "4"; $mail->setFrom('[email protected]', 'Darth Vader'); ^

Try this for size. (Note that my item_price table uses DATE types and not DATETIME. If you are using DATETIME, use NOW() where I have used CURDATE() and use "- INTERVAL 1 SECOND" where I have used "- INTERVAL 1 DAY" so the previous price ends just before the new one comes into effect) Starting with the current item_price table item_price +---------------+-------------+-----------+------------+-------------+---------+ | item_price_id | sales_price | our_price | valid_from | valid_until | item_id | +---------------+-------------+-----------+------------+-------------+---------+ | 30 | 175.00 | 0.00 | 2021-08-08 | 2021-08-07 | 30 | | 79 | 175.00 | 0.00 | 2021-08-08 | NULL | 30 | | 31 | 100.00 | 0.00 | 2021-08-08 | NULL | 31 | | 86 | 105.00 | 0.00 | 2022-04-09 | NULL | 31 | | 32 | 65.00 | 0.00 | 2021-08-08 | NULL | 32 | | 33 | 65.00 | 0.00 | 2021-08-08 | NULL | 33 | | 34 | 75.00 | 0.00 | 2021-08-08 | NULL | 34 | | 35 | 85.00 | 0.00 | 2021-08-08 | NULL | 35 | +---------------+-------------+-----------+------------+-------------+---------+ Write you POST data to a temporary table (new_price) instead of to an array new_price +---------+-------------+-----------+ | item_id | sales_price | our_price | +---------+-------------+-----------+ | 30 | 180.00 | 0.00 | changed | 34 | 75.00 | 0.00 | no change | 40 | 90.00 | 80.00 | new | 41 | 80.00 | 70.00 | new | 42 | 110.00 | 0.00 | new | 43 | 70.00 | 60.00 | new +---------+-------------+-----------+ If the new prices are the same as those that are still current then they can be ignored, so we can remove those, creating a separate temporary table (changed_price) which will be used to update the item_price table CREATE TEMPORARY TABLE changed_price SELECT n.item_id , n.sales_price , n.our_price , CURDATE() as valid_from FROM new_price n LEFT JOIN item_price p ON n.item_id = p.item_id AND CURDATE() BETWEEN p.valid_from AND coalesce(p.valid_until, '9999=12-31') AND n.sales_price = p.sales_price AND n.our_price = p.our_price WHERE p.item_id IS NULL; changed_price +---------+-------------+-----------+------------+ | item_id | sales_price | our_price | valid_from | +---------+-------------+-----------+------------+ | 30 | 180.00 | 0.00 | 2022-04-27 | | 40 | 90.00 | 80.00 | 2022-04-27 | | 41 | 80.00 | 70.00 | 2022-04-27 | | 42 | 110.00 | 0.00 | 2022-04-27 | | 43 | 70.00 | 60.00 | 2022-04-27 | +---------+-------------+-----------+------------+ Update the item price table setting the valid until to just before our valid from for the new price UPDATE item_price p JOIN changed_price c ON p.item_id = c.item_id AND c.valid_from BETWEEN p.valid_from AND coalesce(p.valid_until, '9999-12-31') SET p.valid_until = c.valid_from - INTERVAL 1 DAY; then add the new prices to the item price table INSERT INTO item_price(item_id, sales_price, our_price, valid_from) SELECT * FROM changed_price; Our resulting ite price table is now +---------------+-------------+-----------+------------+-------------+---------+ | item_price_id | sales_price | our_price | valid_from | valid_until | item_id | +---------------+-------------+-----------+------------+-------------+---------+ | 30 | 175.00 | 0.00 | 2021-08-08 | 2021-08-07 | 30 | | 79 | 175.00 | 0.00 | 2021-08-08 | 2022-04-25 | 30 | ** updated | 120 | 180.00 | 0.00 | 2022-04-26 | NULL | 30 | ** new | 31 | 100.00 | 0.00 | 2021-08-08 | NULL | 31 | | 86 | 105.00 | 0.00 | 2022-04-09 | NULL | 31 | | 32 | 65.00 | 0.00 | 2021-08-08 | NULL | 32 | | 33 | 65.00 | 0.00 | 2021-08-08 | NULL | 33 | | 34 | 75.00 | 0.00 | 2021-08-08 | NULL | 34 | | 35 | 85.00 | 0.00 | 2021-08-08 | NULL | 35 | | 121 | 90.00 | 80.00 | 2022-04-26 | NULL | 40 | ** new | 122 | 80.00 | 70.00 | 2022-04-26 | NULL | 41 | ** new | 123 | 110.00 | 0.00 | 2022-04-26 | NULL | 42 | ** new | 124 | 70.00 | 60.00 | 2022-04-26 | NULL | 43 | ** new +---------------+-------------+-----------+------------+-------------+---------+ This should execute far faster than creating and manipulating arrays then looping through those arrays to run queries.

Always put FPDF code in a script by itself. Link to that script to get the pdf file <a href='create_pdf.php'>Create PDF</a> The TCPDF library has a "WriteHTML()" function. Don't expect much by way of documentation for this library. Apart from the source code, I haven't found any yet.

Here's one method $bidders = ['bobby', 'kevin', 'bill', 'kevin', 'brian', 'bobby', 'bobby' ]; $uniq = array_unique($bidders); for ($i=1; $i<=count($uniq); $i++) { $anon[] = "Bidder$i"; } $trans = array_combine($uniq, $anon); $anonBidders = array_map( function($v) use ($trans) { return $trans[$v]; }, $bidders ); to test echo '<pre>'; vprintf("Bidders | %-8s | %-8s | %-8s | %-8s | %-8s | %-8s | %-8s <br>", $bidders); vprintf("anonBidders | %-8s | %-8s | %-8s | %-8s | %-8s | %-8s | %-8s <br>", $anonBidders); giving Bidders | bobby | kevin | bill | kevin | brian | bobby | bobby anonBidders | Bidder1 | Bidder2 | Bidder3 | Bidder2 | Bidder4 | Bidder1 | Bidder1

I'll attempt to describe this process as I see it. There will be some assumptions here but this is my best guess. You have a table of prices which you are querying and creating an array. Similarly, you have new prices which you are loading into a second array. You then compare these two arrays and split the contents into 2 new arrays (updates & inserts) You then loop through these new arrays updating and inserting records into the prices table. Have you considered the simple approach, namely SQL. price (before) new_price +-----+-----------------+-----------------+ +-----+-----------------+-----------------+ | id | sales_price | our_price | | id | sales_price | our_price | +-----+-----------------+-----------------+ +-----+-----------------+-----------------+ | 41 | 80.00 | 0.00 | | 40 | 90.00 | 80.00 | | 42 | 100.00 | 0.00 | | 41 | 80.00 | 70.00 | +-----+-----------------+-----------------+ | 42 | 110.00 | 0.00 | | 43 | 70.00 | 60.00 | +-----+-----------------+-----------------+ INSERT INTO price(id, sales_price, our_price) SELECT id, sales_price, our_price FROM new_price ON DUPLICATE KEY UPDATE sales_price = VALUES(sales_price), our_price = VALUES(our_price); price (after) +----+-------------+-----------+ | id | sales_price | our_price | +----+-------------+-----------+ | 40 | 90.00 | 80.00 | | 41 | 80.00 | 70.00 | | 42 | 110.00 | 0.00 | | 43 | 70.00 | 60.00 | +----+-------------+-----------+

Curly braces should do the trick $d_row['FormId'] = '98765'; echo "<td><a href='delete.php?user_id={$d_row['FormId']}' onclick=\"return confirm('Are you sure?')\">Delete</a></td>"; giving <td><a href="delete.php?user_id=98765" onclick="return confirm('Are you sure?')">Delete</a></td>