excelmaster

@CroNiX, Wow!!! I'm impressed!!! That's pretty amazing code you've provided me with. It looks like poetry in motion...and certainly waaay better than how mine looks. I do reaize that I could have either done away witht he curly brackets and/or used the ternary operator (to compact that piece of code)...but inspite of that, I do agree that it makes more sense to do it your way. That being said...I've gotten used to using PHP to output HTML, and I find that easier to both, read as well as write. Bad habit, I know...but I'm gonna have to change it.

Oh Ok...thanks! Very simple...but a brilliant suggestion indeed. I'm trying it out...however I'm getting an errror that says: Notice: Undefined index: values in /var/www/create.php on line 233. I guess I'm gonna have to dig deeper and see how I can suss that one out.

@fastsol, Thanks for your reply. One question though....since I'm not sure I fully understand you...are you suggesting that I do the following? <?php if($values['store_id'] == "OTH") { echo '<div class="new-store-container" id="new-store-container" name="new-store-container" style="display:block;">'; } else { echo '<div class="new-store-container" id="new-store-container" name="new-store-container" style="display:none;">'; } ?> <div class="control-group"> <div class="other-store" id="new_store_name"> <?php echo standardInputField('New Store Name', 'new_store_name', '', $errors); ?> </div> </div> </div>

Hi, In reference to my first attached image, I have a form which displays two SELECT/drop-down fields (labeled "Store Name" and "Item Description".....and both of which pull-in values from two separate lookup/master tables, in addition to providing an additional option each for "NEW STORE" and "NEW ITEM"). Now, when first-run, and/or if "NEW STORE" and "NEW ITEM" are not selected from the drop-down's then the two fields in green ("New Store Name" and "New Item Name" are hidden, by means of the following code: <div class="new-store-container" id="new-store-container" name="new-store-container" style="display:none;"> <div class="control-group"> <div class="other-store" id="new_store_name"> <?php echo standardInputField('New Store Name', 'new_store_name', '', $errors); ?> </div> </div> </div> Conversely, if "NEW STORE" and/or "NEW ITEM" are selected from the two drop-down's then one (or both) of the "New Name" fields are unhidden by means of the following two pieces of code, one PHP and the second JS: <select class="store-name" name="store_id" id="store_id" onclick="toggle_visibility('store_id','new-store-container')"> <?php echo $store_options; ?> <?php if($values['store_id'] == "OTH") { echo "<option value='OTH' selected> <<<--- NEW STORE --->>> </option>"; } else { echo '<OPTION VALUE="OTH"> <<<--- NEW STORE --->>> </OPTION>'; } ?> </select> function toggle_visibility(fieldName, containerName) { var e = document.getElementById(fieldName); var g = document.getElementById(containerName); if (e.value == 'OTH') { if(g.style.display == 'none') g.style.display = 'block'; else g.style.display = 'none'; } } All of that is working just fine. The problem I'm having is that when I click the "Create" button, after having left any one of the form fields blank, the two "New Name" fields are hidden again, which I don't want to happen i.e. I want them to remain visible (since the values of "store_id" and/or "item_id" are "OTH"), so that the user can enter values into one or both of them, without havng to click on the drop-down a second time in order to execute the "on-click" code. The second attached image shows how the fields are hidden, after clicking "Create". How can I achieve that? It would be greate if someone could cobble-up the required code and provide it to me, since I'm relatively new to this. Thanks much.

Honestly.....I hadn't a clue....and obviously I didn't think that it would matter as to where the "WHERE" was placed, in relation to the "ORDER BY" clause...but of course, I couldn't have been more wrong! And of course...my bad for not checking the MySQL manual. Thank you Sir...for pointing this out to me, and for providing me the link to the SELECT syntax, which I've now bookmarked. Good day!

Hello all, Appreciate if you folks could pls. help me understand (and more importantly resolve) this very weird error: Everything seems to work fine when/if I use the following SQL query (which can also be seen commented out in my code towards the end of this post) : $sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name FROM shoplist, store_master, item_master WHERE shoplist.store_id = store_master.store_id AND shoplist.item_id = item_master.item_id"; However, the moment I change my query to the following, which essentially just includes/adds the ORDER BY clause, I receive the error quoted above: $sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name FROM shoplist, store_master, item_master ORDER BY purchased_flag ASC, purchase_later_flag ASC, shopper1_buy_flag ASC, shopper2_buy_flag ASC, store_name ASC) WHERE shoplist.store_id = store_master.store_id AND shoplist.item_id = item_master.item_id"; In googling for this error I came across posts that suggested using "ORDER BY FIND_IN_SET()" and "ORDER BY FIELD()"...both of which I tried with no success. Here's the portion of my code which seems to have a problem, and line # 67 is the 3rd from bottom (third last) statement in the code below: <?php /* $sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name FROM shoplist, store_master, item_master WHERE shoplist.store_id = store_master.store_id AND shoplist.item_id = item_master.item_id"; */ $sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name FROM shoplist, store_master, item_master ORDER BY FIND_IN_SET(purchased_flag ASC, purchase_later_flag ASC, shopper1_buy_flag ASC, shopper2_buy_flag ASC, store_name ASC) WHERE shoplist.store_id = store_master.store_id AND shoplist.item_id = item_master.item_id"; $result = $pdo->query($sql); // foreach ($pdo->query($sql) as $row) { foreach ($result as $row) { echo '<tr>'; print '<td><span class="filler-checkbox"><input type="checkbox" name="IDnumber[]" value="' . $row["idnumber"] . '" /></span></td>'; Thanks

That's an amazing approach indeed, and certainly one that I will attempt to emulate, albeit slowly but surely. >> But, you can typically use a single query for both add and edit operations. If you set up the database correctly, the ID field will be set as the primary ID and unique in the database. Then you can use the "ON DUPLICATE KEY UPDATE" clause in an INSERT query. Here is an example: Oh wow! I didn't know that was even possible...to use one query to perform both, add as well as update (using the ON DUPLICATE KEY UPDATE" clause. Again, I till try to incorporate that methodology into my app. If I may ask, would you have a bare-bones setup available (of your PHP files for add & update, read, delete etc.), that I could use as a template/starting-point...and perhpas, more so to learn from, if you don't mind sharing it, that is? So...just getting back to my question in post # 5 (and assuming I'm leaving things as-is for the time being), would I have to do the query (sanitizing the input field, of course) in order to update the store_id and item_id fields in the shoplist/transaction file..of is there a better/different way to do it? Thanks.

@Psycho: Using the awesome code (and suggestions) you provided me yesterday I was not only able to get the functionality I had originally requested (i.e. displaying the stored value in a lookup dropdown list) ,working like a dream, but I was also able to somewhat normalize my database, albeit very marginally for now. I will certainly take a second/better look at it later (just started reading up on "Database Normalization"), to see if I can normalize it further. With that being said, I do not quite have the know-how (and this is perhaps a very simple/stupid question to ask) to update the transaction table (shoplist), once a different "store name" and/or "item description" is selected from the dropdown list in the form. In other words, and I'm thinking aloud here (so to speak)....I would probably need to take/use the newly-selected "store name" and find/get the corresponding "store id" from the "store_master" table, and then write that value out to the shoplist/transaction file. Is that how you would do it, or am I making it overly complex, as usual? If the above is indeed the way to do it, then would I have to write a SQL statement (similar to the following) and execute it somewhere within the IF block that checks for !empty($_POST)? SELECT store_id FROM store_master WHERE store_name = $_POST['store_name'];

@Psycho: Thanks for being so kind and helpful for both, pointing out the flaws in my code and database design, as well as for providing advice and an awesome piece of code to help me get started in improving and streamlining my messy/over-complicated code. The code you provided has certainly over-exceeded my expectations, and to you I'm grateful for that. >> I do have one question regarding the SELECT queries you are using above. Why is there a DISTINCT in the queries for stores and products? Those are the master tables for those respective record types, why would there be duplicates? You're right!....My bad, those DISTINCTs were left there as an oversight (when I copied over that code from another PHP file). The master/lookup tables certainly have just one record each for a store and item respectively, and no duplicates. >> Second, why are you not pulling the IDs for those records and using those as the values in the select lists? Those records should have a primary ID field which is numeric. Those numeric IDs would then be stored in the shoplist table as foreign keys Again, bad database design on my part....I know! I will change my "shoplist" table to store the ID's from the lookup tables, instead of the names/descriptions. >> There are lots of other issues such as connecting and disconnecting from the database multiple times Fully agree with you, and thanks for pointing out those issues. I'm aware of some of them, but either I don't know how to how to tackle them, or I had planned to tackle them once I had a better understanding of PHP and MySQL. >> Here is a complete rewrite of your code in a much more logical format. It won't work out of the box as there were some things not provided above. But, it shouldn't take too much to put in what you need. This is exactly what I had in mind (to do at a later stage), but you have just given me a much-wanted head-start. I will certainly try to first understand the code, and then apply/tweak it as required. Cheers

Oops...sorry, forgot to attach screenshot...which is now attached to this post:

Hello all, For the UPDATE portion of my CRUD WebApp what I would like to do is to bring in (and display) the values (of a selected row) from my transaction table. This is working just fine for all fields which are of the "input type". The problem I'm having is with two fields which are of the "select type" i.e. dropdown listboxes. For those two fields, I would like to bring in all the valid choices from the respective lookup/master tables, but then have the default/selected value be shown based on what's in the transaction table. The way I have it right now, those two fields are showing (and updating the record with) the very first entry's in the two lookup tables/select query. The attached picture might make things a little bit clearer. You'll notice in the top screenshot that the first row (which is the one I'm selecting to update) has a "Store Name" = "Super Store" and an "Item Description" = "Old Mill Bagels". Now, when I click the "update" botton and I'm taken to the update screen, the values for those two fields default to the very first entries in the SELECT resultset i.e. "Food Basics" and "BD Cheese Strings". Cricled in green (to the top-left of that screenshot) is the result of an echo that I performed, based on the values that are in the transaction record. I cannot (for the life of me) figure out how to get those values to be used as default/selected values for the two dropdown's...so that if a user does not touch those two dropdown fields, the values in the transaction table will not be changed. Your help will be greatly appreciated. Here's a portion of the FORM code: <form class="form-horizontal" action="update.php?idnumber=<?php echo $idnumber?>" method="post"> <?php // Connect to Store_Name (sn) table to get values for dropdown $pdo = Database::connect(); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC"; $q_sn = $pdo->prepare($sql); $q_sn->execute(); $count_sn = $q_sn->rowCount(); $result_sn = $q_sn->fetchAll(); Database::disconnect(); // Connect to Item_Description (id) table to get values for dropdown $pdo = Database::connect(); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "SELECT DISTINCT product_name FROM product_master ORDER BY product_name ASC"; $q_id = $pdo->prepare($sql); $q_id->execute(); $count_id = $q_id->rowCount(); $result_id = $q_id->fetchAll(); Database::disconnect(); foreach($fields AS $field => $attr){ $current_store_name = $values['store_name']; $current_item_description = $values['item_description']; //Print the form element for the field. if ($field == 'store_name') { echo $current_store_name; echo '<br />'; echo $current_item_description; echo '<div class="control-group">'; echo "<label class='control-label'>{$attr['label']}: </label>"; echo '<div class="controls">'; //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission. echo '<select class="store-name" name="store_name">'; // echo '<option value="">Please select...</option>'; foreach($result_sn as $row) { echo "<option value='" . $row['store_name'] . "'>{$row['store_name']}</option>"; } // $row['store_name'] = $current_store_name; echo "</select>"; echo '</div>'; echo '</div>'; } elseif ($field == 'item_description') { echo '<div class="control-group">'; echo "<label class='control-label'>{$attr['label']}: </label>"; echo '<div class="controls">'; echo '<select class="item-desc" name="item_description">'; // echo '<option value="">Please select...</option>'; foreach($result_id as $row) { echo "<option alue='" . $row['product_name'] . "'>{$row['product_name']}</option>"; } echo "</select>"; echo '</div>'; echo '</div>'; } else { echo '<div class="control-group">'; echo "<label class='control-label'>{$attr['label']}: </label>"; echo '<div class="controls">'; //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission. echo '<input type="text" name="'.$field.'"' . (isset($values[$field]) ? ' value="'.$values[$field].'"' : '') . ' /></label>'; echo '</div>'; echo '</div>'; } And here's some other declarations/code which I think might be required. $fields = array( 'store_name' => array('label' => 'Store Name', 'error' => 'Please enter a store name'), 'item_description' => array('label' => 'Item Description', 'error' => 'Please enter an item description'), 'qty_pkg' => array('label' => 'Qty / Pkg', 'error' => 'Please indicate whether it\'s Qty or Pkg'), 'pkg_of' => array('label' => 'Pkg. Of', 'error' => 'Please enter the quantity'), 'price' => array('label' => 'Price', 'error' => 'Please enter the price'), 'flyer_page' => array('label' => 'Flyer Page #', 'error' => 'Please enter the flyer page #'), 'limited_time_sale' => array('label' => 'Limited Time Sale', 'error' => 'Please enter the days for limited-time-sale'), 'nos_to_purchase' => array('label' => 'No(s) to Purchase', 'error' => 'Please enter the No. of items to purchase') ); ... ... .... { // If [submit] isn't clicked - and therfore POST array is empty - perform a SELECT query to bring in // existing values from the table and display, to allow for changes to be made $pdo = Database::connect(); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "SELECT * FROM shoplist where idnumber = ?"; $q = $pdo->prepare($sql); $q->execute(array($idnumber)); $values = $q->fetch(PDO::FETCH_ASSOC); if(!$values) { $error = 'Invalid ID provided'; } Database::disconnect(); } If there's anything I've missed please ask and I'll provide. Thanks

@Ch0cu3r: Thank you (many times over) for your help, patience and understanding. Much appreciated!!! You are indeed one-of-a-kind, and a genius as well. Cheers

@Ch0cu3r: That fixed that...but a bunch of errors popped up (all pertaining to the column names) when I clicked the [Create] button The errors are: Notice: Undefined variable: store_name in /var/www/create.php on line 108 Notice: Undefined variable: item_description in /var/www/create.php on line 108 Notice: Undefined variable: qty_pkg in /var/www/create.php on line 108 Notice: Undefined variable: pkg_of in /var/www/create.php on line 108 Notice: Undefined variable: price in /var/www/create.php on line 108 Notice: Undefined variable: flyer_page in /var/www/create.php on line 108 Notice: Undefined variable: limited_time_sale in /var/www/create.php on line 108 Notice: Undefined variable: nos_to_purchase in /var/www/create.php on line 108 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'nos_to_purchase' cannot be null' in /var/www/create.php:108 Stack trace: #0 /var/www/create.php(108): PDOStatement->execute(Array) #1 {main} thrown in /var/www/create.php on line 108 This is line 108: $q->execute(array($store_name,$item_description,$qty_pkg,$pkg_of,$price,$flyer_page,$limited_time_sale,$nos_to_purchase,$initialize_flag,$initialize_flag,$initialize_flag,$initialize_flag));

@Ch0cu3r: Gotcha! It makes sense...to not use up more memory than is required. One question: does the code below look right to you? I ask because (on the input form) I am getting an error against each field that reads as follows: Notice: Undefined variable: errors in /var/www/create.php on line 137 Warning: in_array() expects parameter 2 to be array, null given in /var/www/create.php on line 137 Line 137 contains: if(in_array($field, $errors)) { $errors = array(); foreach($fields AS $field => $attr){ if(!isset($_POST[ $field ]) || empty($_POST[ $field ])) { $errors[] = $field; // add current field to the $errros array, this will be used later on to display the error message for the field } } Thanks

@ginerjm: Yes, perhaps I did miss posting some more of the code that's required for you to fully understand my problem. I guess that's the problem when I try to pull-out and post just bits-and-pieces of my code. Perhaps it might help if I post the entire CREATE.PHP, since it's not extremely lengthy to begin with. Thanks for bearing with me, and helping me learn along the way. BTW, in its long/inefficient form, my code was working just fine (except for the error messages part of course), and I wanted to take it to the next level, by making it shorter, and more efficient, and therefore this post <?php error_reporting(E_ALL | E_STRICT | E_NOTICE); ini_set('display_errors', '1'); require 'database.php'; // Create an array of the fields we want to capture, along with their labels $fields = array( 'store_name'=>'Store Name', 'item_description'=>'Item Description', 'qty_pkg'=>'Qty / Pkg', 'pkg_of'=>'Pkg. Of', 'price'=>'Price', 'flyer_page'=>'Flyer Page #', 'limited_time_sale'=>'Limited Time Sale Days', 'nos_to_purchase'=>'Nos. to Purchase' ); // Create an array-type variable to keep track of validation errors $fieldsErrorMsgs = array( 'storeNameError'=>'Please enter a Store Name', 'itemDescriptionError'=>'Please enter an Item Description', 'qtyPkgError'=>'Please enter Qty or Pkg', 'pkgOfError'=>'Please enter the Qty', 'priceError'=>'Please enter the Price', 'flyerPageError'=>'Please enter the Flyer Page #', 'limitedTimeSaleError'=>'Please enter the Limited Time Sale Days', 'nosToPurchaseError'=>'Please enter the No(s). to Purchase' ); // Setup an "initialize variable" to (initially) set the four flag columns to "N", when creating a new record $initialize_flag = "N"; if ( !empty($_POST)) { // Initialize all of the "error message" variables foreach($fieldsErrorMsgs AS $errorVariable=>$errorMessage){ $errorVariable = null; } // keep track of $_POST(ed) values $store_name = $_POST['store_name']; $item_description = $_POST['item_description']; $qty_pkg = $_POST['qty_pkg']; $pkg_of = $_POST['pkg_of']; $price = $_POST['price']; $flyer_page = $_POST['flyer_page']; $limited_time_sale = $_POST['limited_time_sale']; $nos_to_purchase = $_POST['nos_to_purchase']; // Create an array to hold the values we want to insert. $values = array(); // For each of the fields we want, check if the field was POST(ed), and if so trim it and use it. Otherwise use NULL. foreach($fields AS $field=>$label){ //The following line is using the ternary operator, it's basically a shorthand if/else assignment. $values[$field] = isset($_POST[$field]) ? trim($_POST[$field]) : NULL; } $valid = true; $errors = array(); foreach($fields AS $field=>$label){ //The following line is using the ternary operator, it's basically a shorthand if/else assignment. $errors[$field] = (!isset($values['store_name']) || !strlen($values['store_name'])) ? $errors[$label] = 'Mandatory field, input required!' : NULL; if(!strlen($errors[$label])){ $valid = false; } } // insert data if ($valid) { $pdo = Database::connect(); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO shoplist (store_name,item_description,qty_pkg,pkg_of,price,flyer_page,limited_time_sale,nos_to_purchase,shopper1_buy_flag,shopper2_buy_flag,purchased_flag,purchase_later_flag) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $q = $pdo->prepare($sql); $q->execute(array($store_name,$item_description,$qty_pkg,$pkg_of,$price,$flyer_page,$limited_time_sale,$nos_to_purchase,$initialize_flag,$initialize_flag,$initialize_flag,$initialize_flag)); Database::disconnect(); header("Location: index.php"); } } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <link href="css/bootstrap.min.css" rel="stylesheet"> <script src="js/bootstrap.min.js"></script> <!-- <link rel="stylesheet" type="text/css" media="all" href="style.css" /> --> </head> <body> <div class="container"> <div class="span10 offset1"> <div class="row"> <h3>Create an Item Record</h3> </div> <form class="form-horizontal" action="create.php" method="post"> <?php foreach($fields AS $field=>$label){ //Print the form element for the field. echo "<label>{$label}:<br>"; //If the field had an error, display it. if(isset($errors[$field])){ echo ' <span class="error">'.$errors[$field].'</span><br>'; } //Echo the actual input. If the form is being displayed with errors, we'll have a value to fill in from the user's previous submission. echo '<input type="text" name="'.$field.'"'; if(isset($values[$field])){ echo ' value="'.$values[$field].'"'; } echo '/></label>'; } ?> <div class="form-actions"> <button type="submit" class="btn btn-success">Create</button> <a class="btn" href="index.php">Back</a> </div> </form> </div> </div> <!-- /container --> </body> </html>