Barand

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.

Don't run queries inside loops. Use a single query using JOIN(s) to get all the data you need. Example... SELECT t.id as ThreadID , title , LEFT(thread_body, 50) as body , date_format(threadtimestamp, '%D %b %Y %l:%i %p') as ftime , count(p.id) as num_posts FROM thread t LEFT JOIN post p ON t.id = p.thread_id GROUP BY t.id ORDER BY threadtimestamp DESC; +----------+----------+----------------------------------------------------+-----------------------+-----------+ | ThreadID | title | body | ftime | num_posts | +----------+----------+----------------------------------------------------+-----------------------+-----------+ | 2 | Thread 2 | Pellentesque porttitor, velit lacinia egestas auct | 6th Apr 2021 1:52 PM | 3 | | 1 | Thread 1 | Lorem ipsum dolor sit amet, consectetuer adipiscin | 5th Apr 2021 10:25 AM | 4 | +----------+----------+----------------------------------------------------+-----------------------+-----------+

Why would you want to do that? Table third, fourth, fifth and sixth all have identical structures - combine into a single table with atype identifier column. It will make life much simpler. becomes

$testvar = nl2br({$row['CommentText']}) ; ^ ^ and remove the curly braces. Turn on php error reporting, that line above should give

You cannot place a function call in a string and expect it to be executed - it just becomes part of the string. Instead you need to concatenate. For example echo "<div class='divTableCell'>" . nl2br($row['CommentText']) . "</div>";

If you want to use AJAX, here is the same example using that method. The main difference is that instead of using the array naming convention for the selects (name="rating[A001]") it uses class and data attributes <select class="rating" data-id="A001"> ... </select> Again, the "stars" are used as visual confirmation that the update was successful. Code // // PROCESS POSTED DATA // if ($_SERVER['REQUEST_METHOD'] == 'POST') { if ($_POST['ajax'] == 'update-all') { $updt = $pdo->prepare("UPDATE product SET rating = ? WHERE product_code = ? "); foreach ($_POST['rating'] as $rdata) { $updt->execute( [ $rdata['rate'], $rdata['id'] ] ); $stars[] = ["id" => $rdata['id'], "stars" => stars($rdata['rate'])]; } exit(json_encode($stars)); } } // // MAIN PROCESSING // $res = $pdo->query("SELECT product_code , product_name , price , rating+0 as rating -- force numeric value FROM product ORDER BY product_code "); $products = ''; foreach ($res as $r) { $products .= "<tr> <td>{$r['product_code']}</td> <td>{$r['product_name']}</td> <td>&pound;{$r['price']}</td> <td> <select class='w3-input w3-border rating' data-id='{$r['product_code']}' > " . ratingOptions($r['rating']) . "</select> </td> <td class='stars' data-id='{$r['product_code']}'>" . stars($r['rating']) . "</td> </tr>\n"; } // // FUNCTIONS // function ratingOptions($current) { $ratings = [1 => 'vpoor', 'poor', 'ok', 'good', 'vgood']; $opts = "<option value=''>- select -</option>\n"; foreach ($ratings as $r => $rdesc) { $sel = $r == $current ? 'selected' : ''; $opts .= "<option $sel value='$r'>$rdesc</option>\n"; } return $opts; } function stars($n) { if ($n > 5) $n = 5; return "<span style='color:gold'>" . str_repeat("<i class='fas fa-star'></i>", $n) . "</span>" . "<span style='color:#e7e7e7'>" . str_repeat("<i class='fas fa-star'></i>", 5-$n) . "</span>"; } ?> <!DOCTYPE html> <html lang="en"> <head> <title>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"> <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script> <script type='text/javascript'> $().ready( function() { $("#btnSave").click( function() { var rating = [] $("select.rating").each( function(k,v) { var id = $(v).data("id") var rate = $(v).val() rating.push( { "id":id, "rate":rate } ) }) $.post ( "", {"ajax":"update-all", "rating":rating}, function(resp) { $.each(resp, function(k, v) { $(".stars[data-id="+v.id+"]").html(v.stars) }) }, "JSON" ) }) }) </script> </head> <body> <div class="w3-content"> <div class="w3-panel w3-black w3-padding"> <h1>Example</h1> </div> <table class='w3-table-all'> <tr class='w3-dark-gray'> <th>Code</th> <th>Product</th> <th>Price</th> <th colspan="2">Rating</th> </tr> <?=$products?> </table> <br> <button class="w3-button w3-blue w3-right" id="btnSave">Save</button> </div> </body> </html>

That is getting the query to run by changing a blank value to "0" thus preventing a syntax error. You should be checking that $_GET['id'] has a value before calling the query

It sounds like $_GET['id'] does not have a value. If you want a list of threads, why are you selecting them individually? You should be using prepared statements and not placing user supplied data directly into the query Post your code.

The simplest way is to name your selects using the id of the related item. In my example below, the product codes are the ids. TABLE: product; +--------------+------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------------------------------+------+-----+---------+-------+ | product_code | varchar(5) | NO | PRI | NULL | | | product_name | varchar(45) | YES | | NULL | | | price | decimal(8,2) | YES | | NULL | | | rating | enum('vpoor','poor','ok','good','vgood') | YES | | NULL | | +--------------+------------------------------------------+------+-----+---------+-------+ +--------------+--------------+-------+--------+ | product_code | product_name | price | rating | +--------------+--------------+-------+--------+ | A001 | Widget | 10.99 | vpoor | | B002 | Gizmo | 3.49 | ok | | C003 | Thingy | 56.25 | good | | D444 | Wotsit | 2.25 | vgood | +--------------+--------------+-------+--------+ and therefore the selects are named like this... <select name="rating[A001]"> ... </select> <select name="rating[B002]"> ... </select> etc Whn POSTed the post data looks like [rating] => Array ( [A001] => 4 [B002] => 3 [C003] => 2 [D444] => 1 ) and processing is simply foreach ($_POST['rating'] as $prodid => $rating) update product setting rating to $rating where product code is $prodid end foreach Here's my example code // // PROCESS POSTED DATA // if ($_SERVER['REQUEST_METHOD'] == 'POST') { $updt = $pdo->prepare("UPDATE product SET rating = ? WHERE product_code = ? "); foreach ($_POST['rating'] as $code => $rating) { $updt->execute( [ $rating, $code ] ); } header("Location: #"); // reload page exit; } // // MAIN PROCESSING // $res = $pdo->query("SELECT product_code , product_name , price , rating+0 as rating -- force numeric value FROM product ORDER BY product_code "); $products = ''; foreach ($res as $r) { $products .= "<tr> <td>{$r['product_code']}</td> <td>{$r['product_name']}</td> <td>&pound;{$r['price']}</td> <td> <select class='w3-input w3-border' name='rating[{$r['product_code']}]' > " . ratingOptions($r['rating']) . "</select> </td> <td>" . stars($r['rating']) . "</td> </tr>\n"; } // // FUNCTIONS // function ratingOptions($current) { $ratings = [1 => 'vpoor', 'poor', 'ok', 'good', 'vgood']; $opts = "<option value=''>- select -</option>\n"; foreach ($ratings as $r => $rdesc) { $sel = $r == $current ? 'selected' : ''; $opts .= "<option $sel value='$r'>$rdesc</option>\n"; } return $opts; } function stars($n) { if ($n > 5) $n = 5; return "<span style='color:gold'>" . str_repeat("<i class='fas fa-star'></i>", $n) . "</span>" . "<span style='color:#e7e7e7'>" . str_repeat("<i class='fas fa-star'></i>", 5-$n) . "</span>"; } ?> <!DOCTYPE html> <html lang="en"> <head> <title>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'> </style> </head> <body> <div class="w3-content"> <div class="w3-panel w3-black w3-padding"> <h1>Example</h1> </div> <form method='POST'> <table class='w3-table-all'> <tr class='w3-dark-gray'> <th>Code</th> <th>Product</th> <th>Price</th> <th colspan="2">Rating</th> </tr> <?=$products?> </table> <br> <input class="w3-button w3-blue w3-right" type="submit" value="Save"> </form> </div> </body> </html>