johnc81

Hi, Thank you so much for your help, it works perfectly now. I will go through my code remove all queries in loops. Many thanks, J

Thank you for your example. I just compared against mine (I edited my previous comment) and it looks pretty similar apart from I use a while loop and you used foreach. I am not sure how I use this to get the original value from the main query and show that as the selected value. For example, if the operator selected the colour "Black" when creating the record, I want to show that as the selected value when echoing out the select values. Right now, it shows "Select....." for every row. Thank you for all your help so far, J

Hi, I am trying to find an example online and failing. I can put the query and while loop before the main query but I have 2 parts I am struggling with. Firstly, I have no idea how to generate a single $options variable with all the options from a while loop. *Edit* I have done this part: echo '<div class="divTable"> <div class="divTableBody">'; $stmt = $pdo->query(" SELECT itemID ,itemNumber ,categoryID ,itemDescription ,colourID FROM tbl_items WHERE itemActive = 1 ORDER BY categoryID ,itemDescription ASC"); $categoryoptions = ''; $stmt2 = $pdo->query("SELECT categoryID, categoryName FROM tbl_categories WHERE categoryActive = 1 ORDER BY categoryID ASC"); while ($row2 = $stmt2->fetch()) { $categoryoptions .= '<option value="'.$row2['categoryID'].'">'.$row2['categoryName'].'</option>'; } $colouroptions = ''; $stmt3 = $pdo->query("SELECT colourID, colourName FROM tbl_colours WHERE colourActive = 1 ORDER BY colourName ASC"); while ($row3 = $stmt3->fetch()) { $colouroptions .= '<option value="'.$row3['colourID'].'">'.$row3['colourName'].'</option>'; } $rowcount = $stmt->rowCount(); if($rowcount === 0){ echo '<div class="divTableRow" style="background-color: '.$colourrow1.';"> <div class="div100px">0 Records</div> <div class="div150px"></div> <div class="div200px"></div> <div class="div600px"></div> <div class="div250px"></div> <div class="div100px"></div> <div class="div100px"></div> </div>'; } else { while ($row = $stmt->fetch()) { $colour == $colourrow1 ? $colour=$colourrow2 : $colour=$colourrow1; echo '<form name="managerecords" enctype="multipart/data" method="POST"> <div class="divTableRow" style="background-color: '.$colour.';"> <div class="div100px">'.$row['itemID'].'</div> <div class="div150px"><input id="input150px" type="text" name="itemNumber" value="'.$row['itemNumber'].'" required /></div> <div class="div200px"> <select name="categoryID" class="select200px" required> <option name="" value="">Select...</option>'; echo $categoryoptions; echo '</select> </div> <div class="div600px"><input id="input600px" type="text" name="itemDescription" value="'.$row['itemDescription'].'" required /></div> <div class="div250px"> <select name="colourID" class="select250px" required> <option name="" value="">Select...</option>'; echo $colouroptions; echo '</select> </div> <div class="div100px"><input id="submitbtn" type="submit" name="update" value="Update" /></div> <div class="div100px"><input id="submitbtn" type="submit" name="archive" value="Archive" /></div> </div> <input type="hidden" name="itemID" value="'.$row['itemID'].'" /> </form>'; } } echo '</div> </div>'; Secondly, I have no idea how to get the selected value to display for each row. I am sorry if this is really basic, I am just trying to piece stuff together from YouTube and forum posts and just when I think I am getting somewhere, I find out it is completely wrong. Thanks, J

Hi, The options are pulled from a DB table and the user can create their own. I really don't want to have to hard code them in the HTML I just tried putting the SQL queries outside the main while loop but I only get the values for the select boxes for the first row, all the others default to "Select...." Thanks, J

Thank you for the responses. If I am not supposed to put the query inside the loop, how do I display the list of values to the user and also display the value they have selected for each row from the main query? What is best practice for how to do this? Do I just execute the SQL outside the loop and then have nested while loops? Thanks, J

Hello, I am building a script to be able to update item data, so I want to be able to display the list of items. Within the while loop to display the list, I have some dropdown select menus which the user can select from. These have their own queries and while loops. I had this working with mysqli, but now I move to PDO, I can only get it to display the first item in the table. Please can someone have a look at my code and see where I am going wrong? echo '<div class="divTable"> <div class="divTableBody">'; $stmt = $pdo->query(" SELECT itemID ,itemNumber ,categoryID ,itemDescription ,colourID FROM tbl_items WHERE itemActive = 1 ORDER BY categoryID ,itemDescription ASC"); $rowcount = $stmt->rowCount(); if($rowcount === 0){ echo '<div class="divTableRow" style="background-color: '.$colourrow1.';"> <div class="div100px">0 Records</div> <div class="div150px"></div> <div class="div200px"></div> <div class="div600px"></div> <div class="div250px"></div> <div class="div100px"></div> <div class="div100px"></div> </div>'; } else { while ($row = $stmt->fetch()) { $colour == $colourrow1 ? $colour=$colourrow2 : $colour=$colourrow1; echo '<form name="managerecords" enctype="multipart/data" method="POST"> <div class="divTableRow" style="background-color: '.$colour.';"> <div class="div100px">'.$row['itemID'].'</div> <div class="div150px"><input id="input150px" type="text" name="itemNumber" value="'.$row['itemNumber'].'" required /></div> <div class="div200px"> <select name="categoryID" class="select200px" required> <option name="" value="">Select...</option>'; $stmt = $pdo->query("SELECT categoryID, categoryName FROM tbl_categories WHERE categoryActive = 1 ORDER BY categoryID ASC"); while ($row2 = $stmt->fetch()) { echo '<option value="'.$row2['categoryID'].'" '.($row['categoryID']==$row2['categoryID'] ? 'selected' : '').'>'.$row2['categoryName'].'</option>'; } echo '</select> </div> <div class="div600px"><input id="input600px" type="text" name="itemDescription" value="'.$row['itemDescription'].'" required /></div> <div class="div250px"> <select name="colourID" class="select250px" required> <option name="" value="">Select...</option>'; $stmt = $pdo->query("SELECT colourID, colourName FROM tbl_colours WHERE colourActive = 1 ORDER BY colourName ASC"); while ($row2 = $stmt->fetch()) { echo '<option value="'.$row2['colourID'].'" '.($row['colourID']==$row2['colourID'] ? 'selected' : '').'>'.$row2['colourName'].'</option>'; } echo '</select> </div> <div class="div100px"><input id="submitbtn" type="submit" name="update" value="Update" /></div> <div class="div100px"><input id="submitbtn" type="submit" name="archive" value="Archive" /></div> </div> <input type="hidden" name="itemID" value="'.$row['itemID'].'" /> </form>'; } } echo '</div> </div>'; I know it is something to do with the dropdown select menus for category and colour because when I comment those out, the full item list is displayed. Many thanks for your time, J

Hi, Thank you very much for your help and detailed explanations. I still need to work through the rest of your list (and will do that over the next day or so), but now I have it posting correctly. I would really appreciate you having a quick look at the code to see if there are any problems with what I have now: <?php ERROR_REPORTING(E_ALL); require 'db/connect.php'; require 'db/pdoconnect.php'; require 'includes/rowcolours.php'; session_start(); $errors = []; if(isset($_SESSION['orderIDx'])) { $orderIDx = $_SESSION['orderIDx']; $orderActivex = $_SESSION['orderActivex']; } else { $orderIDx = 0; $orderActivex = 0; } // Create sales order form submitted if(isset($_POST['createsalesorder'])) { // Create array of data $data = [ 'shopID' => trim($_POST['shopID']) == '' ? null : $_POST['shopID'], 'orderReference' => trim($_POST['orderReference']) == '' ? null : $_POST['orderReference'], 'orderDate' => trim($_POST['orderDate']) == '' ? null : $_POST['orderDate'], 'customerName' => trim($_POST['customerName']) == '' ? null : $_POST['customerName'], 'customerAddress' => trim($_POST['customerAddress']) == '' ? null : $_POST['customerAddress'], 'countryID' => trim($_POST['countryID']) == '' ? null : $_POST['countryID'], 'paymentMethodID' => trim($_POST['paymentMethodID']) == '' ? null : $_POST['paymentMethodID'], 'orderPaid' => isset($_POST['orderPaid']) ? 1 : 0, 'orderPickDate' => trim($_POST['orderPickDate']) == '' ? null : $_POST['orderPickDate'], 'orderDespatchDate' => trim($_POST['orderDespatchDate']) == '' ? null : $_POST['orderDespatchDate'], 'shipperID' => trim($_POST['shipperID']) == '' ? null : $_POST['shipperID'], 'orderShippingReference' => trim($_POST['orderShippingReference']) == '' ? null : $_POST['orderShippingReference'], 'orderShippingCost' => trim($_POST['orderShippingCost']) == '' ? null : $_POST['orderShippingCost'], 'orderActive' => 1, ]; // Check for errors if($_POST['shopID'] === '' || $_POST['orderReference'] === '' || $_POST['orderDate'] === '' || $_POST['customerName'] === '') { $errors['data'] = 'Missing data. Record not created'; } // Post form if no errors if(empty($errors)) { $createsalesorder = "INSERT INTO tbl_order (shopID, orderReference, orderDate, customerName, customerAddress, countryID, paymentMethodID, orderPaid, orderPickDate, orderDespatchDate, shipperID, orderShippingReference, orderShippingCost, orderActive) VALUES (:shopID, :orderReference, :orderDate, :customerName, :customerAddress, :countryID, :paymentMethodID, :orderPaid, :orderPickDate, :orderDespatchDate, :shipperID, :orderShippingReference, :orderShippingCost, :orderActive)"; $stmt= $pdo->prepare($createsalesorder); $stmt->execute($data); } // Set session variables and refresh if(empty($errors)) { $lastID = $pdo->lastInsertId(); $_SESSION['orderIDx'] = $lastID; $_SESSION['orderActivex'] = 1; $_SESSION['success_message'] = "Sales order created"; die(header("Refresh:0")); } } ?> <!DOCTYPE html> <html lang="en"> <head> <title>Manage Sales Orders</title> <link rel="stylesheet" type="text/css" href="css/style.css" /> </head> <body> <?php require 'includes/navbar.php'; echo '<div><h1>Manage Sales Orders</h1></div>'; ?> <?php // output any errors if(!empty($errors)) { echo "<p>".implode('<br>',$errors)."</p>"; } // Output success message if(isset($_SESSION['success_message'])) { echo "<p>{$_SESSION['success_message']}</p>"; unset($_SESSION['success_message']); } ?> Even though I am only using this offline, I would be interested to know if the above would be enough to avoid SQL injection? Thanks, J

Hi, I am trying to work through the post method form processing you have above, using PDO: // detect if a post method form has been submitted before referencing any of the form data. if(isset($_POST['createsalesorder'])) { // keep the form data as a set in a php array variable, then operate on elements in this array variable throughout the rest of the code. $data = [ 'shopID' => $_POST['shopID'], 'orderReference' => $_POST['orderReference'], 'orderDate' => $_POST['orderDate'], 'customerName' => $_POST['customerName'], 'customerAddress' => $_POST['customerAddress'], 'countryID' => $_POST['countryID'], 'paymentMethodID' => $_POST['paymentMethodID'], 'orderPaid' => 0, 'orderPickDate' => $_POST['orderPickDate'], 'orderDespatchDate' => $_POST['orderDespatchDate'], 'shipperID' => $_POST['shipperID'], 'orderShippingReference' => $_POST['orderShippingReference'], 'orderShippingCost' => $_POST['orderShippingCost'], 'orderActive' => 1 ]; $createsalesorder = "INSERT INTO tbl_order (shopID, orderReference, orderDate, customerName, customerAddress, countryID, paymentMethodID, orderPaid, orderPickDate, orderDespatchDate, shipperID, orderShippingReference, orderShippingCost, orderActive) VALUES (:shopID, :orderReference, :orderDate, :customerName, :customerAddress, :countryID, :paymentMethodID, :orderPaid, :orderPickDate, :orderDespatchDate, :shipperID, :orderShippingReference, :orderShippingCost, :orderActive)"; $stmt= $pdo->prepare($createsalesorder); $stmt->execute($data); I think the above covers points 1 & 2. I have no idea how to implement points 3 & 4? I am also not sure how to handle checkbox (I just put a default value in). I am also getting an error when posting as well because countryID is an INT, but it thinks it is a string. How do I validate that? Thanks, J

Hi, Thank you for the detailed explanation. There is actually a lot more code to this page, but I cut it out for the purposes of showing the issue. For example, the session variable orderIDx is used to get the order, but orderActiveX is used to determine if it is open or closed and displays differently (editable or view mode). I haven't included a login because this website will never go online, it will be run locally and only by me. I tried to use PDO, but I couldn't get anything to work with that, hence why I decided to stick with mysqli. I would have preferred PDO but I every example I look at only does it a different way and I have no idea what is correct I am not sure what this means: "your code generally follows this, but it has things like two session_start() statements, that needs to be cleaned up." I start a session once the form has been posted, but unless I start another one after the page reloads, how do I get the use the session variables? I am also not sure how to change the code to see any errors coming from the form. It doesn't create the record in MySQL, so I guess there is something wrong and an error should be displayed? Is there a tutorial or something I can look at to show what good looks like because I am learning all this by following stuff online, but clearly it is not good. I just want to be able to see what the issue is and why it won't post so I can start trying to fix that. Thanks, J

Hi, Thank you for your response. I will post the code below. This is the form page, test.php. When I complete the mandatory fields and leave the remaining ones empty, I get the message to say the record has been created, but there is no row in the database. I do not receive any errors. <?php ERROR_REPORTING(E_ALL); ini_set('display_errors', 1); include 'db/connect.php'; include 'includes/rowcolours.php'; session_start(); if(isset($_SESSION['orderIDx'])) { $orderIDx = $_SESSION['orderIDx']; $orderActivex = $_SESSION['orderActivex']; } else { $orderIDx = 0; $orderActivex = 0; } session_destroy(); // Create a new sales order header if(isset($_POST['createsalesorder'])) { foreach($_POST as $key=>$value) { $$key = $value; } if (isset($_POST['orderPaid'])) { $orderPaidx = 1; } else { $orderPaidx = 0; } if($shopID == '' || $orderReference == '' || $orderDate == '' || $customerName == '') { header('Location: test.php?flag=2'); } else { $createsalesorder = "INSERT INTO tbl_order (shopID, orderReference, orderDate, customerName, customerAddress, countryID, paymentMethodID, orderPaid, orderPickDate, orderDespatchDate, shipperID, orderShippingReference, orderShippingCost, orderActive) VALUES ('$shopID', '$orderReference', '$orderDate', '$customerName', '$customerAddress', '$countryID', '$paymentMethodID', '$orderPaidx', '$orderPickDate', '$orderDespatchDate', '$shipperID', '$orderShippingReference', '$orderShippingCost', 1)"; mysqli_query($conn,$createsalesorder); $lastID = $conn->insert_id; session_start(); $_SESSION['orderIDx'] = $lastID; $_SESSION['orderActivex'] = 1; if($createsalesorder){ header('Location: test.php?flag=1'); } else { print 'ERROR : ('. $mysqli->errno .') '. $mysqli->error; } } } ?> <!DOCTYPE html> <html lang="en"> <head> <title>Manage Sales Orders</title> <link rel="stylesheet" type="text/css" href="css/style.css" /> </head> <body> <?php include 'includes/navbar.php'; echo '<div><h1>Manage Sales Orders</h1></div>'; if(isset($_GET['flag'])) { if ($_GET['flag'] == 1) { echo '<div class="flagresponse">Sales order created</div>'; } if ($_GET['flag'] == 2) { echo '<div class="flagresponse">Missing data. Record not created</div>'; } if ($_GET['flag'] == 3) { echo '<div class="flagresponse">Sales order updated</div>'; } if ($_GET['flag'] == 4) { echo '<div class="flagresponse">Sales order deleted</div>'; } if ($_GET['flag'] == 5) { echo '<div class="flagresponse">Sales order line created</div>'; } if ($_GET['flag'] == 6) { echo '<div class="flagresponse">Missing data. Record not updated</div>'; } if ($_GET['flag'] == 7) { echo '<div class="flagresponse">Sales order line updated</div>'; } if ($_GET['flag'] == 8) { echo '<div class="flagresponse">Sales order line deleted</div>'; } if ($_GET['flag'] == 9) { echo '<div class="flagresponse">Sales order closed</div>'; } } ?> <?php if($orderIDx == 0) { echo '<h2>Create New Sales Order</h2> <form name="createsalesorder" action="" enctype="multipart/data" method="POST" id="createsalesorder"> <div class="divTable"> <div class="divTableBody"> <div class="divTableHeader"> <div class="div250px"><h3>Shop</h3></div> <div class="div250px"><h3>Order Ref</h3></div> <div class="div150px"><h3>Order Date</h3></div> </div> </div> </div> <div class="divTable"> <div class="divTableBody"> <div class="divTableRow" style="background-color: '.$colourrow1.'"> <div class="div250px"> <select name="shopID" class="select250px" required> <option name="" value="">Select...</option>'; $sql = "SELECT shopID, shopName FROM tbl_shops"; $result = $conn->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { echo '<option value="'.$row['shopID'].'">'.$row['shopName'].'</option>'; } } else { echo '0 Results'; } echo '</select> </div> <div class="div250px"><input id="input250px" type="text" name="orderReference" id="orderReference" required /></div> <div class="div150px"><input id="input150px" type="date" name="orderDate" id="orderDate" required /></div> </div> </div> </div> <p /> <div class="divTable"> <div class="divTableBody"> <div class="divTableHeader"> <div class="div250px"><h3>Customer Name</h3></div> <div class="div600px"><h3>Address</h3></div> <div class="div250px"><h3>Country</h3></div> <div class="div250px"><h3>Payment Method</h3></div> <div class="div100px"><h3 style="text-align: center;">Paid</h3></div> <div class="div150px"><h3>Pick Date</h3></div> </div> </div> </div> <div class="divTable"> <div class="divTableBody"> <div class="divTableRow" style="background-color: '.$colourrow1.'"> <div class="div250px"><input id="input250px" type="text" name="customerName" id="customerName" required /></div> <div class="div600px"><input id="input600px" type="text" name="customerAddress" id="customerAddress" /></div> <div class="div250px"> <select name="countryID" class="select250px"> <option name="" value="">Select...</option>'; $sql = "SELECT countryID, countryName FROM tbl_countries"; $result = $conn->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { echo '<option value="'.$row['countryID'].'">'.$row['countryName'].'</option>'; } } else { echo '0 Results'; } echo '</select> </div> <div class="div250px"> <select name="paymentMethodID" class="select250px"> <option name="" value="">Select...</option>'; $sql = "SELECT paymentMethodID, paymentMethodName FROM tbl_paymentmethods"; $result = $conn->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { echo '<option value="'.$row['paymentMethodID'].'">'.$row['paymentMethodName'].'</option>'; } } else { echo '0 Results'; } echo '</select> </div> <div class="div100px"><input id="input100px" class="checkbox" type="checkbox" value="1" name="orderPaid" id="orderPaid" /></div> <div class="div150px"><input id="input150px" type="date" name="orderPickDate" id="orderPickDate" /></div> </div> </div> </div> <p /> <div class="divTable"> <div class="divTableBody"> <div class="divTableHeader"> <div class="div250px"><h3>Shipper</h3></div> <div class="div250px"><h3>Shipper Ref</h3></div> <div class="div150px"><h3>Shipping Cost</h3></div> <div class="div150px"><h3>Despatch Date</h3></div> <div class="div100px"><h3>Create</h3></div> </div> </div> </div> <div class="divTable"> <div class="divTableBody"> <div class="divTableRow" style="background-color: '.$colourrow1.'"> <div class="div250px"> <select name="shipperID" class="select250px"> <option name="" value="">Select...</option>'; $sql = "SELECT shipperID, shipperName FROM tbl_shippers"; $result = $conn->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { echo '<option value="'.$row['shipperID'].'">'.$row['shipperName'].'</option>'; } } else { echo '0 Results'; } echo '</select> </div> <div class="div250px"><input id="input250px" type="text" name="orderShippingReference" id="orderShippingReference" /></div> <div class="div150px"><input id="input150px" type="text" name="orderShippingCost" id="orderShippingCost" /></div> <div class="div150px"><input id="input150px" type="date" name="orderDespatchDate" id="orderDespatchDate" /></div> <div class="div100px"><input type="submit" value="Create" name="createsalesorder" id="submitbtn" /></div> </div> </div> </div> </form>'; } ?> </body> </html> There are 4 includes, firstly connect.php <?php $servername = "localhost"; $username = "USERNAME"; $password = "PASSWORD"; $dbname = "testdb"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } ?> Second, rowcolours.php <?php $colourrow1 = "#dddddd"; $colourrow2 = "#efefef"; $colour = ''; ?> Third, navbar.php <?php echo '<div id="navbar"> <div><a id="navbar" href="index.php">Home</a></div> </div>'; ?> Fourth, the CSS, style.css body { font-family: arial,verdana,helvetica,sans-serif; font-size: 10pt; } h1 { font-size: 14pt; text-align: left; margin: 10px 0px 4px 0px; } h2 { font-size: 11pt; text-align: left; margin: 20px 0px 4px 0px; } h3 { font-size: 10pt; text-align: left; margin: 5px 0px 2px 0px; } label { font-size: 10pt; font-weight: bold; } input, select { font-family: "Lucida Sans Typewriter", "Lucida Console", monaco, "Bitstream Vera Sans Mono", monospace; width: 400px; height: 20px; margin: 2px 0px; -moz-box-sizing: border-box; -webkit-box-sizing: border-box; box-sizing: border-box; } .select250px { font-family: "Lucida Sans Typewriter", "Lucida Console", monaco, "Bitstream Vera Sans Mono", monospace; width: 250px; height: 20px; margin: 2px 0px; -moz-box-sizing: border-box; -webkit-box-sizing: border-box; box-sizing: border-box; } .select600px { font-family: "Lucida Sans Typewriter", "Lucida Console", monaco, "Bitstream Vera Sans Mono", monospace; width: 600px; height: 20px; margin: 2px 0px; -moz-box-sizing: border-box; -webkit-box-sizing: border-box; box-sizing: border-box; } .checkbox { padding: 0px; margin: 0px; position: relative; vertical-align: middle; width: 14px; height: 14px; } input#submitbtn { width: 100px; } input#submitbtn200px { width: 200px; } button { width: 100px; } input#sml { width: 50px; } input#med { width: 100px; } td#formlabel { width: 100px; } td#inputfield { width: 500px; } div#navbar { width: 100%; background-color: #333333; padding: 10px; } a:link#navbar, a:visited#navbar { background-color: #f44336; font-size: 14pt; color: white; padding: 14px 25px; text-align: center; text-decoration: none; display: inline-block; margin-right: 10px; } a:hover#navbar, a:active#navbar { background-color: red; } .grid { display: flex; width: 100%; flex-wrap: wrap; } .content { color: #333333; background-color: #dddddd; font-weight: bold; text-align: center; height: 150px; padding: 10px; width: 150px; border: 10px solid #333333; border-radius: 7px; margin: 10px; } #blackbox:hover { opacity: 0.6; transition: 0.3s; } #blackbox { width: 100%; height: 100%; opacity: 1; transition: 0.3s; } .formlabel { width:100px; display: inline-flex; } .divTable{ display: table; padding: 0 0 0 10px; } .divTableHeader { display: table-row; background-color: #333333; color: #ffffff; } .divTableRow { display: table-row; padding: 10px; } .divTableFooter { display: table-row; background-color: #333333; color: #ffffff; } .divTableBody { display: table-row-group; } .div100px { width: 100px; display: table-cell; padding: 2px 5px 2px 5px; } input#input100px { width: 100px; } .div150px { width: 150px; display: table-cell; padding: 2px 5px 2px 5px; } input#input150px { width: 150px; } .div200px { width: 200px; display: table-cell; padding: 2px 5px 2px 5px; } input#input200px { width: 200px; } .div250px { width: 250px; display: table-cell; padding: 2px 5px 2px 5px; } input#input250px { width: 250px; } .div300px { width: 300px; display: table-cell; padding: 2px 5px 2px 5px; } input#input300px { width: 300px; } .div400px { width: 400px; display: table-cell; padding: 2px 5px 2px 5px; } input#input400px { width: 400px; } .div600px { width: 600px; display: table-cell; padding: 2px 5px 2px 5px; } input#input600px { width: 600px; } .flagresponse { color: #dd0000; font-size: 10pt; font-weight: bold; padding: 5px 0 5px 0; } hr { border: 0; height: 1px; background: #777777; background-image: linear-gradient(to right, #ccc, #777, #ccc); margin: 20px 0 20px 0; } .hrreport { border: 0; height: 1px; background: #777777; background-image: linear-gradient(to right, #ccc, #777, #ccc); margin: 4px 0 4px 0; } .hrreporttotal { border: 0; height: 2px; background: #777777; background-image: linear-gradient(to right, #ccc, #777, #ccc); margin: 4px 0 4px 0; } h2.bgcolour { background-color: #cccccc; padding: 5px 5px 5px 10px; } .closebutton { color: #f44336; font-weight: bold; } .txtright { text-align: right; } .boldfont { font-weight: bold; } The structure for the sales order table, tbl_order. The columns that are not required in the initial record create are all set to allow NULL DROP TABLE IF EXISTS `tbl_order`; CREATE TABLE IF NOT EXISTS `tbl_order` ( `orderID` int(11) NOT NULL AUTO_INCREMENT, `shopID` int(11) NOT NULL, `orderReference` varchar(80) NOT NULL, `orderDate` date NOT NULL, `customerName` varchar(80) NOT NULL, `customerAddress` text, `countryID` int(11) DEFAULT NULL, `paymentMethodID` int(11) DEFAULT NULL, `orderPaid` tinyint(1) DEFAULT NULL, `orderPickDate` date DEFAULT NULL, `orderDespatchDate` date DEFAULT NULL, `shipperID` int(11) DEFAULT NULL, `orderShippingReference` varchar(80) DEFAULT NULL, `orderShippingCost` decimal(7,2) DEFAULT NULL, `orderActive` tinyint(1) NOT NULL, PRIMARY KEY (`orderID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; The shops table, tbl_shops DROP TABLE IF EXISTS `tbl_shops`; CREATE TABLE IF NOT EXISTS `tbl_shops` ( `shopID` int(11) NOT NULL AUTO_INCREMENT, `shopName` varchar(45) NOT NULL, `shopActive` tinyint(1) NOT NULL, PRIMARY KEY (`shopID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- -- Dumping data for table `tbl_shops` -- INSERT INTO `tbl_shops` (`shopID`, `shopName`, `shopActive`) VALUES (1, 'Ebay', 1), (2, 'Etsy', 1); The Country table, tbl_countries DROP TABLE IF EXISTS `tbl_countries`; CREATE TABLE IF NOT EXISTS `tbl_countries` ( `countryID` int(11) NOT NULL AUTO_INCREMENT, `countryName` varchar(50) NOT NULL, `twoCharCountryCode` char(2) NOT NULL, `threeCharCountryCode` char(3) NOT NULL, `countryActive` tinyint(1) NOT NULL, PRIMARY KEY (`countryID`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- -- Dumping data for table `tbl_countries` -- INSERT INTO `tbl_countries` (`countryID`, `countryName`, `twoCharCountryCode`, `threeCharCountryCode`, `countryActive`) VALUES (1, 'United Kingdom', 'GB', 'GBR', 1), (2, 'Denmark', 'DK', 'DNK', 1), (3, 'France', 'FR', 'FRA', 1); The payment methods table, tbl_paymentmethods DROP TABLE IF EXISTS `tbl_paymentmethods`; CREATE TABLE IF NOT EXISTS `tbl_paymentmethods` ( `paymentMethodID` int(11) NOT NULL AUTO_INCREMENT, `paymentMethodName` varchar(45) NOT NULL, `paymentMethodActive` tinyint(1) NOT NULL, PRIMARY KEY (`paymentMethodID`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- -- Dumping data for table `tbl_paymentmethods` -- INSERT INTO `tbl_paymentmethods` (`paymentMethodID`, `paymentMethodName`, `paymentMethodActive`) VALUES (1, 'PayPal', 1), (2, 'Bank Transfer', 1), (3, 'Cash', 1); The shippers table, tbl_shippers DROP TABLE IF EXISTS `tbl_shippers`; CREATE TABLE IF NOT EXISTS `tbl_shippers` ( `shipperID` int(11) NOT NULL AUTO_INCREMENT, `shipperName` varchar(45) NOT NULL, `shipperActive` tinyint(1) NOT NULL, PRIMARY KEY (`shipperID`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- -- Dumping data for table `tbl_shippers` -- INSERT INTO `tbl_shippers` (`shipperID`, `shipperName`, `shipperActive`) VALUES (1, 'Royal Mail', 1), (2, 'Evri', 1), (3, 'DPD', 1), (4, 'DHL', 1), (5, 'FedEx', 1); I think this is everything to be able to recreate the scenario above. Any support with this would be really appreciated. I have been struggling with this for a couple of days and am out of ideas. I have tried using if statements for each field being posted to see if it is empty and then setting default values. I have tried using mysqli and PDO but nothing seems to work. Thanks, J

Hello, I have a PHP form which I am posting to update an existing record. This is a sales order form and will be updated with data as it becomes available. The form will have missing data when it is posted (input / select fields left empty). The problem is that unless I complete all the fields, the record is not updating. Am I missing something? All the fields I have left empty are allowed NULLs in the table. They are a mixture of INT, DATE and VARCHAR data types. I have a mix of input=text and select dropdown fields in my form. Please can someone let me know how this process normally works because I assumed it would just fill in what it could and leave the rest as NULL? Many Thanks, J