Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,342

  • Joined

  • Last visited

  • Days Won

    795

 Content Type 

Everything posted by Barand

  1. Barand
    Process the $_POST data in a very similar method to that used for the shopping table inserts. I don't know if you are trying that as , once again, you have left us to guess what the code is that produced your error messages.
  2. Barand
    Not rocket science $salesdata = []; // initialize the array while ($row = odbc_fetch_array($result)) { $salesdata[$row['ARJSALESEMPLOYEEID']][] = $row; // restof your current processing in the loop // goes here } ... assuming you want a sheet for each ARJSALESEMPLOYEEID value;
  3. Barand
    1. The now extinct mysql library and the mysqli library are two completely different animals. 2. Forget about mysqli and use PDO. +---------+-------------+ | mysql | dodo | | mysqli | donkey | | PDO | racehorse | +---------+-------------+
  4. Barand
    I must be missing something because I cannot see the point in having a chain of functions which convert one cryptic string value into another one. Eventually you are going to have to to decide on a YES/NO value as to whether a tax is included or not. Why not go straight there? Just my 0.02 worth. I'll shut up now.
  5. Barand
    Correction to above. Did some further testing and repetetive adding/removing causes errors. Fixed by having "x" and "y". X is used for the id sufficies and is never decremented (thus maintaining uniqueness like mysql auto_increments). Y is used as the field counter and is incremented and decremented. Revised code <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type="text/javascript"> var max_fields = 10; //Maximum allowed input fields var x = 1; //Initlal input field id suffix is set to 1 var y = 1; //Initlal input field is set to 1 function loadOptions(x) { var select = $('#jobqueue'+x); //request the JSON data and parse into the select element var data = JSON.parse($("#jobdata").val()) //clear the current content of the select select.html(''); //iterate over the data and append a select option $.each(data, function(key, val){ select.append('<option id="' + val + '">' + val + '</option>'); }) } $(document).ready(function() { loadOptions(1); //When user click on add input button $("#add_fields").click(function(e){ e.preventDefault(); //Check maximum allowed input fields if(y < max_fields){ x++; //input field id increment y++; //input field increment //add input field $("#wrapper").append('<div><input type="hidden" name="input_array_kuemid[]" value=""/><input type="text" name="input_array_email[]" placeholder="Email Address" /> <input type="text" name="input_array_name[]" placeholder="Email Address Owner" /> <select id="jobqueue'+x+'" name="input_array_queue[]"></select><a href="javascript:void(0);" class="remove_field">Remove</a></div>'); loadOptions(x); // load options into new select object } }); //when user click on remove button $("#wrapper").on("click",".remove_field", function(e){ e.preventDefault(); $(this).parent('div').remove(); //remove inout field y--; //input field decrement }) }); </script> </head> <body> <button id="add_fields">Add</button> <br><br> <input type='hidden' id='jobdata' value='["Tech Support","AR","PNI","Shared","Mobile"]'> <div id="wrapper"> <div> <input type="hidden" name="input_array_kuemid[]" value=""/> <input type="text" name="input_array_email[]" placeholder="Email Address" /> <input type="text" name="input_array_name[]" placeholder="Email Address Owner" /> <select id="jobqueue1" name="input_array_queue[]"></select> <a href="javascript:void(0);" class="remove_field">Remove</a> </div> </div> </body> </html>
  6. Barand
    I didn't test the delete bit, just he append. EDIT: Just copy/pasted my code from my above post and append (includung adding options) and the delete all work OK.
  7. Barand
    One possible usage: const PST_RATE = 0.15; const GST_RATE = 0.10; $taxes = ['Wholesale Customer' => ['PST' => 1, 'GST' => 1], 'Wholesale Silvia Silver' => ['PST' => 1, 'GST' => 1], 'Wholesale Silvia Gold' => ['PST' => 1, 'GST' => 1], 'Wholesale Silvia Premium' => ['PST' => 1, 'GST' => 1], 'Wholesale Silvia Union' => ['PST' => 1, 'GST' => 1], 'wholesale_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_silvia_silver_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_silvia_gold_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_silvia_premium_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_silvia_union_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_tax_exempt' => ['PST' => 0, 'GST' => 0], 'wholesale_silvia_silver_tax_exempt' => ['PST' => 0, 'GST' => 0], 'wholesale_silvia_gold_tax_exempt' => ['PST' => 0, 'GST' => 0], 'wholesale_silvia_premium_tax_exempt' => ['PST' => 0, 'GST' => 0], 'wholesale_silvia_union_tax_exempt' => ['PST' => 0, 'GST' => 0] ]; $customer_role = 'Wholesale Silvia Silver'; // get role of current customer $PST_payable = $taxes[$customer_role]['PST']; $GST_payable = $taxes[$customer_role]['GST']; $net_value = 100.00; $pst = $net_value * PST_RATE * $PST_payable; // calculate tax values $gst = $net_value * GST_RATE * $GST_payable; $total_payable = $net_value + $pst + $gst; echo number_format($total_payable, 2) //--> 125.00
  8. Barand
    I would opt for a slightly different structure, viz $taxes = ['Wholesale Customer' => ['PST' => 1, 'GST' => 1], 'Wholesale Silvia Silver' => ['PST' => 1, 'GST' => 1], 'Wholesale Silvia Gold' => ['PST' => 1, 'GST' => 1], 'Wholesale Silvia Premium' => ['PST' => 1, 'GST' => 1], 'Wholesale Silvia Union' => ['PST' => 1, 'GST' => 1], 'wholesale_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_silvia_silver_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_silvia_gold_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_silvia_premium_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_silvia_union_pst_exempt' => ['PST' => 0, 'GST' => 1], 'wholesale_tax_exempt' => ['PST' => 0, 'GST' => 0], 'wholesale_silvia_silver_tax_exempt' => ['PST' => 0, 'GST' => 0], 'wholesale_silvia_gold_tax_exempt' => ['PST' => 0, 'GST' => 0], 'wholesale_silvia_premium_tax_exempt' => ['PST' => 0, 'GST' => 0], 'wholesale_silvia_union_tax_exempt' => ['PST' => 0, 'GST' => 0] ]; Then $PST_payable = $taxes[$customer_role]['PST']; $GST_payable = $taxes[$customer_role]['GST'];
  9. Barand
    Ids must be unique In the document.ready(), the options are added only to those selects that already exist This works <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type="text/javascript"> var max_fields = 10; //Maximum allowed input fields var x = 1; //Initlal input field is set to 1 function loadOptions(x) { var select = $('#jobqueue'+x); //request the JSON data and parse into the select element var data = JSON.parse($("#jobdata").val()) //clear the current content of the select select.html(''); //iterate over the data and append a select option $.each(data, function(key, val){ select.append('<option id="' + val + '">' + val + '</option>'); }) } $(document).ready(function() { loadOptions(1); //When user click on add input button $("#add_fields").click(function(e){ e.preventDefault(); //Check maximum allowed input fields if(x < max_fields){ x++; //input field increment //add input field $("#wrapper").append('<div><input type="hidden" name="input_array_kuemid[]" value=""/><input type="text" name="input_array_email[]" placeholder="Email Address" /> <input type="text" name="input_array_name[]" placeholder="Email Address Owner" /> <select id="jobqueue'+x+'" name="input_array_queue[]"></select><a href="javascript:void(0);" class="remove_field">Remove</a></div>'); loadOptions(x); // load options into new select object } }); //when user click on remove button $("#wrapper").on("click",".remove_field", function(e){ e.preventDefault(); $(this).parent('div').remove(); //remove inout field x--; //inout field decrement }) });</script> </head> <body> <button id="add_fields">Add</button> <br><br> <input type='hidden' id='jobdata' value='["Tech Support","AR","PNI","Shared","Mobile"]'> <div id="wrapper"> <div> <input type="hidden" name="input_array_kuemid[]" value=""/> <input type="text" name="input_array_email[]" placeholder="Email Address" /> <input type="text" name="input_array_name[]" placeholder="Email Address Owner" /> <select id="jobqueue1" name="input_array_queue[]"></select> <a href="javascript:void(0);" class="remove_field">Remove</a> </div> </div> </body> </html>
  10. Barand
    If that code is in the same page as your previous code then remember those $_POST items are now arrays. Once again you can can throw away the form and update stocks with a single query... UPDATE stocks s JOIN temp_shopping t USING (product_name, item_type, item_size, category) SET s.item_qty = s.item_qty - t.item_qty WHERE t.trans_ref = ? (This assumes you have checks into your application to ensure you have sufficient stock when making a sale.) And you have another table that requires normalizing +------------------+ | stocks | +------------------+ +--------<| product_id | | | item_qty | | +------------------+ | +-----------------+ | | product | | +-----------------+ | | product_id |-------+ | product_name | | | category | | +------------------+ | size | | | shopping | | type | | +------------------+ | price | | | shopping_id | +-----------------+ | | trans_ref | +--------<| product_id | | qty | | date | +------------------+
  11. Barand
    Option 1 - change the way you create the original data Option 2 - $orig = '[{"kuqudept":"Tech Support"},{"kuqudept":"AR"},{"kuqudept":"PNI"},{"kuqudept":"Shared"},{"kuqudept":"Mobile"}]'; $simple = json_encode(array_map( function($v) { return $v['kuqudept'];}, json_decode($orig, 1))); echo $simple; //--> ["Tech Support","AR","PNI","Shared","Mobile"] $simple will now contain the encoded siple verion
  12. Barand
    Just my 0.02 worth... You seem to be over-complicating the json array that you are passing. A simpler structure would do the same job <?php $a = ['Tech Support', 'AR', 'PNI', 'Shared', 'Mobile']; $j = json_encode($a); ?> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type="text/javascript"> $().ready( function() { var select = $('#jobqueue'); //request the JSON data and parse into the select element var data = JSON.parse($("#jobdata").val()) //clear the current content of the select select.html(''); //iterate over the data and append a select option $.each(data, function(key, val){ select.append('<option id="' + val + '">' + val + '</option>'); }) }) </script> </head> <body> <input type="hidden" id="jobdata" value='<?=$j?>'> <select id="jobqueue"></select> </body> </html>
  13. Barand
    This works (JSON data stored in hidden input field) <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type="text/javascript"> $().ready( function() { var select = $('#jobqueue'); //request the JSON data and parse into the select element var data = JSON.parse($("#jobdata").val()) //clear the current content of the select select.html(''); //iterate over the data and append a select option $.each(data, function(key, val){ select.append('<option id="' + val.kuqudept + '">' + val.kuqudept + '</option>'); }) }) </script> </head> <body> <input type="hidden" id="jobdata" value='[{"kuqudept":"Tech Support"},{"kuqudept":"AR"},{"kuqudept":"PNI"},{"kuqudept":"Shared"},{"kuqudept":"Mobile"}]'> <select id="jobqueue"></select> </body> </html>
  14. Barand
    https://www.google.com/search?client=firefox-b-d&amp;q=top+php+ide+2019
  15. Barand
    Post your shopping table structure. (It should look something like this...) mysql> SHOW CREATE TABLE shopping\G; *************************** 1. row *************************** Table: shopping Create Table: CREATE TABLE `shopping` ( `shopping_id` int(11) NOT NULL AUTO_INCREMENT, `trans_ref` int(11) DEFAULT NULL, `category` int(11) DEFAULT NULL, `product_name` varchar(30) DEFAULT NULL, `item_type` int(11) DEFAULT NULL, `item_size` varchar(10) DEFAULT NULL, `item_qty` int(11) DEFAULT NULL, `item_price` decimal(10,2) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `date` date DEFAULT NULL, PRIMARY KEY (`shopping_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  16. Barand
    Then you have a primary key or unique key constraint on "trans_id" in your shopping table, which seems an odd thing to do as it appears that you are extracting groups of product items with the same trans_id ( $_SESSION['trans_id'] ) from your temporary table to insert into the shopping table, all with that same trans_id..
  17. Barand
    You might have read it but you totally ignored it. Good bye.
  18. Barand
    Read the replies.
  19. Barand
    Can you post the output you are now getting from echo '<pre>', print_r($_POST, 1), '</pre>';
  20. Barand
    I would use virtually tthe same query for each of the above. When checking for existence, get the count. The advantage is there will always be a row returned, even if the count is zero. This avoids checking for reults prior to checking the value. $color = 'red'; // posted input $res = $db->prepare("SELECT COUNT(*) FROM cars WHERE color = ? "); $res->execute([$color]); then if you just want a yes/no answer echo $res->fetchColumn() > 0 ? 'Yes' : 'No'; but if you want to know how many echo $res->fetchColumn() . " cars found"; The plate search will be exactly the same. The difference is that a key search will be faster as the index can be used instead of the query processor having to read every record. All those scenarios are single value queries. The difference would come when you you need more info, such as "What make and color is Peter's car?" or "What make and color is Mary's car?" (doesn't exist). Assuming each driver has only one car... $name = 'Mary'; // posted input $res = $db->prepare("SELECT color , make FROM cars WHERE driver = ? "); $res->execute([$name]); $row = $res->fetch(); if (!$row) { echo "$name has no car"; } else { echo "$name has a {$row['color']} {$row['make']}"; }
  21. Barand
    Just a comment on your tables. Data items like product_name, category, price are properties of the product and should, therefore, only be in the product table. The shopping table should hold just the product_id. (Read up on "data normalization") +-----------------+ | product | +-----------------+ | product_id |-------+ | product_name | | | category | | +------------------+ | size | | | shopping | | price | | +------------------+ +-----------------+ | | shopping_id | | | trans_ref | +--------<| product_id | | qty | | date | +------------------+
  22. Barand
    Your problem is the input names. You have multiple items with the same name so, when posted, the last value with that name has overwritten all the previous ones. Only the final row is posted. You need to append "[ ]" to your input names so they are posted in arrays (EG name="category[]" ) . To process ... if ($_SERVER['REQUEST_METHOD']=='POST') { $stmt = $pdo->prepare("INSERT INTO shopping( trans_ref, category, product_name, item_type, item_size, item_qty, item_price, price, date ) VALUES( :trans_ref, :category, :product_name, :item_type, :item_size, :item_qty, :item_price, :price, CURDATE() ) "); foreach ($_POST['category'] as $k => $category) { $record = [ 'trans_ref' => $_SESSION['trans_ref'], 'category' => $category, 'product_name' => $_POST['product_name'][$k], 'item_type' => $_POST['item_type'][$k], 'item_size' => $_POST['item_size'][$k], 'item_qty' => $_POST['item_qty'][$k], 'item_price' => $_POST['item_price'][$k], 'price' => $_POST['item_price'][$k] * $_POST['item_qty'][$k], ]; $stmt->execute($record); } } That should fix your code. Now to fix your method. Firstly, don't store derived values. You can calculate the total price when required (SELECT qty * price as totalprice, ...) Secondly, a single INSERT..SELECT SQL query will replace all the above code (two if you count the check that data exists to be transferred). Here's how I would do it (no form required) $res = $pdo->prepare("SELECT SUM(item_qty * item_price) FROM temp_shopping WHERE trans_ref = ? "); $res->execute([$_SESSION['trans_ref']]); $total_price = $res->fetchColumn(); if ($total_price == 0) { echo "NO RECORDS TO TRANSFER<br>"; } else { $res = $pdo->prepare("INSERT INTO shopping( trans_ref, category, product_name, item_type, item_size, item_qty, item_price, date ) SELECT trans_ref, category, product_name, item_type, item_size, item_qty, item_price, CURDATE() FROM temp_shopping WHERE trans_ref = ? "); $res->execute([$_SESSION['trans_ref']]); echo "Total value transferred : " . number_format($total_price,2) . '<br>;'; }
  23. Barand
    I agree with @ginerjm. If your first code is "working" it's because you have more than one record with the found email value. The best use I have found for fetchColumn() is when getting results from single-value queries EG $result = $db->query("SELECT COUNT(*) FROM user"); $user_count = $result->fetchColumn();
  24. Barand
    Why are you using an intermediate form to move data from one table to another? Is this a guessing game where we have to guess how the form sending the above POST data is getting its data from your "temporal" table? - Sorry, I don't play games.
  25. Barand
    What does the data being posted to the form look like. IE What does this output... echo '<pre>', print_r($_POST, 1), '</pre>'; ?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.