Barand

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.

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.