Barand

You'll find other thing you can't do in MariaDB that you could in mysql, but you'll have fun finding them. Anyway, welcome.

Have you enabled php_mysqli extension in your php.ini file?

You are grouping by the condition category_name = 'Meat' This has two possible values (0 , 1) so you get 1 row for each value. Tubers just happens to be one of those where the condition evaluates to 0. You should GROUP BY <column name> Don't use SELECT *. Specify column names that you need. CROSS JOIN gives every combination of rows in table A joined with rows in table B so why would you want that here? For example Table A TableB ------- ------ a 1 b 2 c 3 SELECT a.col, b.col FROM tableA a CROSS JOIN tableB b; a 1 a 2 a 3 b 1 b 2 b 3 c 1 c 2 c 3 You seem to have gone overboard with unnecessary subqueries too. Try mysql> SELECT o.id -> , o.order_id -> , p.id -> , p.product_name -> , c.id -> , c.category_name -> FROM order_table o -> INNER JOIN product p ON o.product_id = p.id -> INNER JOIN category c ON p.product_category = c.id -> WHERE category_name = 'meat'; +----+----------+----+--------------+----+---------------+ | id | order_id | id | product_name | id | category_name | +----+----------+----+--------------+----+---------------+ | 4 | 10004 | 4 | Beef | 4 | Meat | +----+----------+----+--------------+----+---------------+

Best way is to do it manually, noting the steps, and then replicate those steps in code. Here are my neighbours and their dogs' names. Some have no dog, some have two. Allocate the dogs to their correct owners TABLE: neighbour TABLE: dog +-------+----------------+ +-------+----------------+-----------+ | ID | name | | ID | name | owner_id | +-------+----------------+ +-------+----------------+-----------+ | 1 | Peter | | 1 | Maxwell | ? | | 2 | Paul | | 2 | Hector | ? | | 3 | Mary | | 3 | Hugo | ? | | 4 | Fred | | 4 | Jasper | ? | | 5 | Wilma | | 5 | Coco | ? | | 6 | Barny | | 6 | Jaba | ? | | 7 | Betty | | 7 | Oliver | ? | +-------+----------------+ +-------+----------------+-----------+ The only way to do it is by using the "sorting hat" from Hogworts. Or manually.

I can't see your code ... $file = $im; ... helping much. If you want to write the GD image in memory (that was created by imagecreatefromjpeg()) back to the file in jpeg format, then use imagejpeg($im, $file); I do not know if this sequence has any cleansing effect though.

@gizmola I note your comments regarding dates and, had this been for a commercial marina with many comings and goings, stays of varying duration and future bookings, I too would have put them in the schema. However I was under the impression that this situation was more like allocating semi-permanent car-parking spaces to employees.

If you have the information in your database, why do want to store it again in JSON format. Although I expect the drivers will be delighted to be handed their deleivery schedule printed in JSON format. You cannot correctly compare dates in m/d/Y format

Alternatively, get all the totals in one go $res = $pdo->query("SELECT role , count(*) as total FROM testuser GROUP BY role; "); $totals = array_column($res->fetchAll(), 'total', 'role'); echo $totals['MainAdmin']; echo $totals['normaAdmin'];

Don't retrieve all the columns in all the records just to count them. Get a record count. $urole = 'MainAdmin'; $res = $pdo->prepare("SELECT count(*) as total FROM testuser WHERE role = ?; "); $res->execute([$urole]); $total = $res->fetchColumn();

Truck numbers beginning with "F" evaluate to (int)0 Change if ($loadid != 0) { to if ($loadid !== 0) {

First thing you should do is turn on error reporting and clear all your syntax errors. Then you can worry about getting the query right.

Form inputs need a name attibute <select class="form-control" id="seller" name="seller"> You can then retrieve it in the receiving page from $_POST['seller'] (or $_GET['seller'] if your form method is "GET") Anything in the query string is retrieved from $_GET array eg $_GET['month']. If you form method is POST, I would put the date in a hidden form input value so all you date will be in the $_POST array.

I'll eat my words. I couldn't resist the challenge so, having slept on it, I wrote a an SQL function "isConsecutive(dates)" to find records where there are fewer than 10 dates and they are consecutive. TEST DATA and QUERY TABLE: ahtest +----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | adates | +----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 | | 2 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00 | | 3 | 2021-04-01 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00 | | 4 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00 | | 5 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00 | | 6 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00 | | 7 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00 | | 8 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 | | 9 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-11 12:00,2021-04-13 12:00,2021-04-15 12:00 | | 10 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-13 12:00,2021-04-14 12:00 | | 11 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00 | | 12 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00 | | 13 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-13 12:00 | | 14 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 | | 15 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00 | | 16 | 2021-04-01 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00 | | 17 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-09 12:00,2021-04-10 12:00,2021-04-11 12:00 | | 18 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 | | 19 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00,2021-04-10 12:00 | | 20 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-06 12:00,2021-04-08 12:00,2021-04-10 12:00,2021-04-11 12:00,2021-04-12 12:00,2021-04-13 12:00,2021-04-14 12:00,2021-04-15 12:00 | +----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ mysql> SELECT id -> , adates -> FROM ahtest -> WHERE isConsecutive(adates); +----+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | adates | +----+-----------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 | | 8 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 | | 14 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 | | 18 | 2021-04-01 12:00,2021-04-02 12:00,2021-04-03 12:00,2021-04-04 12:00,2021-04-05 12:00,2021-04-06 12:00,2021-04-07 12:00,2021-04-08 12:00 | +----+-----------------------------------------------------------------------------------------------------------------------------------------+ THE FUNCTION DELIMITER $$ CREATE FUNCTION `isConsecutive`(dates varchar(255)) RETURNS int(11) BEGIN DECLARE k INTEGER DEFAULT 1; DECLARE da DATE DEFAULT SUBSTRING_INDEX(dates, ',', 1); DECLARE db DATE ; DECLARE num INTEGER DEFAULT (LENGTH(dates)+1) DIV 17; DECLARE strx VARCHAR(255) DEFAULT SUBSTRING_INDEX(dates, ',', -(num-k)); DECLARE isconsec INTEGER DEFAULT 1; IF num >= 10 THEN RETURN 0; END IF; WHILE LENGTH(strx) > 0 DO SET db = SUBSTRING_INDEX(strx, ',', 1); if DATEDIFF(db, da) <> 1 THEN SET isconsec = 0; END IF; SET k = k + 1; SET da = SUBSTRING_INDEX(strx, ',', 1); SET strx = SUBSTRING_INDEX(strx, ',', -(num-k)); END WHILE; RETURN isconsec; END$$ DELIMITER ;

In a couple of weeks time, when your scalp is bleeding from all the head-scratching trying to do it with only SQL, have a go with this PHP/SQL hybrid. // // GENERATE TEST DATA // $pdo->exec("DROP TABLE IF EXISTS ahtest"); $pdo->exec("CREATE TABLE ahtest( id int not null auto_increment primary key, adates varchar(255))"); $data = []; for ($i=0; $i<20; $i++) { $numdates = rand(8,12); $dt = new DateTime('2021-04-01'); $dates = []; $incs = [1,1,1,1,1,2,1,1,1,1,1,2]; // 16.6% chance of non-consecutive dates for ($j=0; $j<$numdates; $j++) { $inc = $incs[array_rand($incs)]; $dates[] = $dt->format('Y-m-d h:i'); $dt->modify("+ $inc days"); } $data[] = sprintf("('%s')", join(',', $dates)); } $pdo->exec("INSERT INTO ahtest (adates) VALUES " . join(',', $data)); // // GET RECORDS WITH < 10 DATES // $res = $pdo->query("SELECT id , adates FROM ahtest WHERE LENGTH(adates) < 160 "); $tdata = ''; foreach ($res as $r) { $datelist = $r['adates']; $consec = isConsec($datelist) ? "<i class='fas fa-check'></i>" : "<i class='fas fa-times'></i>"; $tdata .= "<tr><td>{$r['id']}</td> <td>$datelist</td> <td>$consec</td> </tr> "; } function isConsec(&$datelist) { $dates = explode(',', $datelist); $newdates[] = $dates[0]; $result = 1; $k = count($dates); for ($i=1; $i<$k; $i++) { $a = new DateTime($dates[$i-1]); $b = new DateTime($dates[$i]); if ($a->diff($b)->days != 1) { $newdates[] = "<span class='nonconsec'>{$dates[$i]}</span>"; $result = 0; } else $newdates[] = $dates[$i]; } $datelist = join(', ', $newdates); return $result; } ?> <!DOCTYPE html> <html lang="en"> <head> <title>Dates Example</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css"> <style type='text/css'> .nonconsec { font-weight: 600; color: red; } </style> </head> <body> <div class="w3-container"> <div class="w3-panel w3-black w3-padding"> <h1>Dates Example - AdamHumbug</h1> </div> <table class='w3-table-all'> <tr class='w3-blue'> <th>ID</th> <th>Dates</th> <th>Consecutive</th> </tr> <?=$tdata?> </table> </div> </body> </html> Gives

Sorry, looks like I mis-spelled it.

Then normalize your data and you stand a chance. Or, as previously stated, be prepared to use php arrays. Arrays/delimited strings of data and SQL don't play nice. Fine if all you want to do is store them as string and retrieve them as a string. You might be able to extract the dates using SUBSTRING_INDEX but I wouldn't attempt it with so many dates and especially when the number of dates is variable.

try echo "<table>"; while ($row = odbc_fetch_array($result)) { if($row['UOMPTRUCKNUMBER'] != $loadid) { if ($loadid != 0) { echo "<tr style='background:#eee'> <td colspan='4'>TOTAL</td> <td>$TotalPcs</td> <td>&nbsp;</td> <td>$LoadTotal</td> </tr>"; } echo "<tr style='background:#82fbfd'>"; echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; } echo "<tr style='background:#eee'> <td colspan='4'>TOTAL</td> <td>$TotalPcs</td> <td>&nbsp;</td> <td>$LoadTotal</td> </tr> </table> ";

If you store data in that manner you deserve to have problems. You can use a query to get those with < 10 dates but you will need to resort to php arrays for the rest.

Most people would output the totals in separate rows

I use MySQL Workbench (it's free and I hate phpMyAdmin)

Not much modification required. I've commented the changes needed. while ($row = odbc_fetch_array($result)) { echo "<table>"; echo "<tr style='background:#82fbfd'>"; if($row['UOMPTRUCKNUMBER'] != $loadid) { if ($loadid != 0) { # ADD // OUTPUT PREVIOUS TRUCK TOTAL HERE # ADD } # ADD echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>"; echo "<th>Drop</th>"; echo "<th>OrderID</th>"; echo "<th>CustID</th>"; echo "<th>QTY</th>"; echo "<th>Cubes</th>"; echo "<th>Total</th></tr>"; $TotalPcs=0; $loadTotal=0; } ShowDetail($row); $loadid = $row['UOMPTRUCKNUMBER']; $TotalPcs+=round($row['QTY'],0); $loadTotal +=$row['total']; } // OUTPUT TOTALS FOR LAST TRUCK HERE # ADD

Now you have told us the actual context it makes a lot more sense than having reusable member IDs. You also introduced a couple of other attributes that would be stored in the "berth" table (renamed from member_no) viz. Size and Pier. Size required would need to be known at time of allocation to a member. Reallocation of a different berth would need to be a custom transaction, allocation on insert could be a trigger function, de-allocation could be a foreign key cascade option. Your assertion that a member is deleted when their boat changes is FUBAR. Change, or add, a record in the member boat table. The boat size in this table would determine the size required for the new berth. or

I would created a pre-populated table "member_no" which contains a row for each of those values. create table member_no ( memno varchar(3) not null primary key, member_id int ); The "member" table would have a conventional auto_incremented numeric id (not re-used). TABLE member_no TABLE member +----------+------------+ +-----------+------------------+------ | memno | member_id | | member_id | name | etc. +----------+------------+ +-----------+------------------+----- | 01 | 1 | | 1 | Curly | | 02 | 2 | | 2 | Larry | | 03 | 9 | | 8 | Mo | | 04 | 17 | | 9 | Fred | | 05 | 8 | | 15 | Wilma | | 06 | 15 | | 16 | Barny | | 07 | 16 | | 17 | Betty | | 08 | null | | ... | null | | 999 | null | When a new member joins (say, id=18) they assigned to the first memno with a null member_id (08). When a member leaves, their member_id in the member_no table is set back to null. You could use triggers on insert and delete to automate the process.

Given that for any piece of work, 90% of the task takes 90% of the effort. The final 10% takes another 90%. I'd go with the maximum and double it 😀

Only checked checkboxes are posted so doing it that way the indexes can get out of synch. I'd recommend using the id as the input array keys. <input type=checkbox name='service_id[$id]' .. > <input type='date' name='date[$id]' .. > etc