Barand

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

What are you entering into your browser's address bar to execute it?

Does the file containing that code have a ".php" extension?

This is how my table looks... What have you got then?

Perhaps SELECT u.referenced_table_name , group_concat(u.referenced_column_name separator ', ') as ref_col_name , u.table_name , group_concat(u.column_name separator ', ') as col_name , u.constraint_name FROM information_schema.key_column_usage u JOIN information_schema.table_constraints c USING (constraint_schema,constraint_name) WHERE c.constraint_schema = ? AND c.constraint_type = 'FOREIGN KEY' GROUP BY u.constraint_name ORDER BY referenced_table_name, referenced_column_name

Because you haven't specified that phoneno is unique.

Those single quotes shouldn't be there $Thumbnail = "upload/Thumbnails/'$fileName'"; ^ ^

$_FILES['file']['error'] shouldn't be blank. Even if no file is selected for upload it will be "4", and "0" if there is no error. Post your input form code.

Are there any clues in $_FILES['file']['error'] ?

One way would be $jdata = '[{"id" : "1", "name": "fido", "age":"5"}, {"id" : "2", "name": "rover", "age":"3"},{"id" : "3", "name": "woofie", "age":"1"}]'; $stmt = $pdo->prepare("INSERT INTO mytest (jdata) VALUES (?)"); $stmt->execute([$jdata]); echo pdo2text($pdo, "SELECT * FROM mytest"); echo pdo2text($pdo, "SELECT id , jdata->>'$[0].name' as dog1 , jdata->>'$[1].name' as dog2 , jdata->>'$[2].name' as dog3 FROM mytest"); ?> +----+------+-------+--------+ | id | dog1 | dog2 | dog3 | +----+------+-------+--------+ | 1 | fido | rover | woofie | +----+------+-------+--------+ However, that requires that you know how many dogs. I'd go for 1 dog per row... +----+-------------------------------------------+ | id | jdata | +----+-------------------------------------------+ | 1 | {"id": "1", "age": "5", "name": "fido"} | | 2 | {"id": "2", "age": "3", "name": "rover"} | | 3 | {"id": "3", "age": "1", "name": "woofie"} | +----+-------------------------------------------+ SELECT id , jdata->>'$.name' as dog_name FROM mytest +----+----------+ | id | dog_name | +----+----------+ | 1 | fido | | 2 | rover | | 3 | woofie | +----+----------+

I couldn't get it to work with your numeric keys, but this worked $pdo->exec("DROP TABLE IF EXISTS mytest"); $pdo->exec("CREATE TABLE mytest ( id int not null auto_increment primary key, jdata JSON ) "); $my_array = ["one" => "foo", "three" => "bar", "eleven" => "baz"]; $jdata = json_encode($my_array); $stmt = $pdo->prepare("INSERT INTO mytest (jdata) VALUES (?)"); $stmt->execute([$jdata]); // THEN select id, jdata->>"$.three" from mytest; +----+-------------------+ | id | jdata->>"$.three" | +----+-------------------+ | 1 | bar | +----+-------------------+ Also, this worked $my_array = ["foo", "bar", "baz"]; $jdata = json_encode($my_array); $stmt = $pdo->prepare("INSERT INTO mytest (jdata) VALUES (?)"); $stmt->execute([$jdata]); // THEN select id, jdata->>"$[1]" from mytest; +----+----------------+ | id | jdata->>"$[1]" | +----+----------------+ | 1 | bar | +----+----------------+

You wouldn't write your ph script on a single line, you would use line breaks and indents to improve readability. Why do you, therefore, write a SQL query in one long line? Here's you original with the errors highlighted And here's a revised version select Threads.id as ThreadId , Title , LEFT(ThreadBody, 50) as body , date_format(Thread_date, '%D %b %Y %l:%i %p') as ftime , count(Posts.IdOfThread) as num_posts from Threads Left join Posts on Threads.id = Posts.IdOfThread group by Threads.id order by Thread_date desc;

You are doing better than I did. All I got was errors from your posted XML.