Jump to content


  • Posts

  • Joined

  • Last visited

  • Days Won


thenorman138 last won the day on July 19 2018

thenorman138 had the most liked content!

Recent Profile Visitors

1,257 profile views

thenorman138's Achievements

Advanced Member

Advanced Member (4/5)



  1. I have a successful select query, where I'm looping and building a row for getting parameters which works correctly while ($row = $orderDetailCheck->fetch(PDO::FETCH_ASSOC)) { $params = [ ":INVOICE_NUMBER" => $row["INVOICE_NUMBER"], ":CUSTNM" => $row["CUSTNM"], ":SELLINGN" => $row["SELLINGN"], ":GROUP" => $row["GROUP"], ":DTL12" => $row["DTL12"], ":DTL13" => $row["DTL13"], ":QUANTITY" => $row["QUANTITY"], ":COUNT_PLMN_1" => $row["COUNT_PLMN_1"], ":LAST_DATE" => $row["LAST_DATE"], ]; } My issue now is that, for each row, I need to perform two merges because the data from that select is going to be split into two tables in db2. Some values are truly split between the tables but some values are shared between the two. I'm not sure the best way to perform these two merges because if the first one (products table) inserts, then it creates an ID that I need as a foreign key basically, to insert into the orders table. So on insert I need to grab that newly created ID and use it for ```product_id``` in the second merge. If the first merge performs the update when matched, then I need to grab the existing ID for that record so that I can update the proper record in the orders table. My two merge statements: /*products table*/ MERGE INTO products AS P USING(VALUES( :GROUP, :DTL12, :DTL13, :CUSTNM, :SELLINGN, :COUNT_PLMN_1, :LAST_DATE ) ) AS S(GROUP,DTL12,DTL13,CUSTNM,SELLINGN,COUNT_PLMN_1,LAST_DATE) ON s.GROUP = p.GROUP and s.DTL12 = p.DTL12 and s.DTL13 = p.DTL13 and s.CUSTNM = p.CUSTNM WHEN MATCHED THEN UPDATE SET LAST_DATE = s.LAST_DATE WHEN NOT MATCHED THEN INSERT VALUES (s.GROUP,s.DTL12,s.DTL13,s.CUSTNM,s.SELLINGN,s.COUNT_PLMN_1,s.LAST_DATE) /*ORDERS Table*/ MERGE INTO ORDERS AS PO USING(VALUES( /*need foreign key, which is id from products table*/ :QUANTITY, :LAST_DATE, :INVOICE_NUMBER )) AS S(PRODUCT_ID,quantity_ordered,LAST_DATE,invoice_number) ON s.PRODUCT_ID = po.id WHEN MATCHED THEN UPDATE SET LAST_DATE = s.LAST_DATE, quantity_ordered = s.quantity_ordered, invoice_number = s.invoice_number WHEN NOT MATCHED THEN INSERT VALUES (s.PRODUCT_ID, s.quantity_ordered, s.LAST_DATE, s.invoice_number) Examples: If my ROW Returns INVOICE | CUSTNM | SELLINGNUM | GROUP | DTL12 | DTL13 | QUANTITY | COUNT_PLMN_1 | LAST_DATE ================================================================================================================== 1122 123 321 995 1452 12 13 5 '2018-12-14' Then my insert into products would be products ID | GROUP | DTL12 | DTL13 | CUSTNM | SELLINGNUM | COUNT_PLMN_1 | LAST_DATE ========================================================================================================== 1 995 1452 12 123 321 5 '2018-12-14' and my insert to orders: ORDERS PRODUCT_ID | QUANTITY_ORDERED | LAST_DATE | INVOICE ============================================================ 1 13 '2018-12-14' 1122 But if my next record matched on my unique qualities with a new invoice, quantity and date ROW Returns INVOICE | CUSTNM | SELLINGNUM | GROUP | DTL12 | DTL13 | QUANTITY | COUNT_PLMN_1 | LAST_DATE ================================================================================================================== 1133 123 321 995 1452 12 4 5 '2018-12-18' Then I would update products like so: products ID | GROUP | DTL12 | DTL13 | CUSTNM | SELLINGNUM | COUNT_PLMN_1 | LAST_DATE ========================================================================================================== 1 995 1452 12 123 321 5 '2018-12-18' and update orders like so: ORDERS PRODUCT_ID | QUANTITY_ORDERED | LAST_DATE | INVOICE ============================================================ 1 4 '2018-12-18' 1133 I guess the main question is: How can I get the ID of a record from the products table (whether it's an existing match OR newly created in the merge) and once I get it, how can I use it for the 2nd merge?
  2. Ah I see what you mean. So there's no way to create sub headers for laravel excel?
  3. I currently have an array that I've built that dumps like this: 0 => array:11 [▼ "category_code" => "123" "category_name" => "Testing" "category_description" => "This is a test category" 19738 => array:5 [▼ "identifier" => "720368842943" "description" => Test Description One "count" => 4 "details" => array:2 [▼ 0 => array:3 [▼ "detail_code" => "2751" "detail_code2" => "43" "detail_specifier" => "Detail One" ] 1 => array:3 [▼ "detail_code" => "2681" "detail_code2" => "9" "detail_specifier" => "Detail Two" ] ] "prices" => array:1 [▼ "01" => "1129.00" ] ] 19739 => array:5 [▼ "identifier" => "720368844121" "description" => "Test Description Two" "count" => 4 "details" => array:2 [▼ 0 => array:3 [▼ "detail_code" => "2751" "detail_code2" => "43" "detail_specifier" => "Detail One" ] 1 => array:3 [▼ "detail_code" => "2681" "detail_code2" => "9" "detail_specifier" => "Detail Two" ] ] "prices" => array:1 [▼ "01" => "1490.00" ] ] I'm using laravel excel in order to export that as an excel file, but it's not quite working the way I intend When it exports to excel I only get the top level info: 123 | Testing | This is a test category But I want to get that info as a header and then each subsequent product for that category as a row, so with the example above it would look like: 123 | Testing | This is a test category ==================================================================================================================== 19738 | 720368842943 | Test Description One | 4 | 2751 | 43 | Detail One | 2681 | 9 | Detail Two | 1129.00 19739 | 720368844121 | Test Description Two | 4 | 2751 | 43 | Detail One | 2681 | 9 | Detail Two | 1490.00 Here's the excel code with the array I'm using, which is dumped above: $allCategoryResult= array(); foreach($prices->categories as $category){ $categoryItem = array(); $categoryItem["category_code"] = $category->category_code; $categoryItem["category_name"] = $category->category_name; $categoryItem["category_desc"] = $category->category_desc; foreach($category->skus as $sku){ $skuItem = array(); $skuItem["identifier"] = $sku->sku_info->identifier; $skuItem["description"] = $sku->sku_info->item->description; $skuItem["count"] = $sku->sku_info->item->item_type->count; $skuItem["details"] = array(); foreach ($sku->sku_info->details as $details) { $detailsItem = array(); $detailsItem["detail_code"] = $details->detail_code; $detailsItem["detail_code2"] = $details->detail_code2; $detailsItem["detail_specifier"] = $details->detail_specifier; $skuItem["details"][] = $detailsItem; } $skuItem["prices"] = get_object_vars($sku->prices); $itemCode = $sku->sku_info->item->item_code; $categoryItem[$itemCode] = $skuItem; } $allCategoryResult[] = $categoryItem; } $name = 'Test Export'; $build = Excel::create($name, function ($excel) use ($allCategoryResult) { $excel->setTitle('Test Export'); $excel->sheet('Test Export', function ($sheet) use ($allCategoryResult) { $sheet->fromArray($allCategoryResult);
  4. I'm currently looping on an array with this structure: Categories{ CategoryName CategoryCode CategoryDescription Products{ product_info{ product_code product_type{ CountNumber ProductDescription } } prices{ "wholesale":"250", "retail":"400" } } } I'm looping on the above array at each level, and I've declared an array so that I can put all my needed values into it $priceResult = array(); foreach($prices->categories as $category){ $categoryName = $category->category_name; $categoryCode = $category->category_code; $categoryDescription = $category->category_desc; foreach($category->products as $product){ foreach($product->product_info as $info){ $product_code = $info->product_code; foreach($info->product_type as $type){ $CountNumber = $type->CountNumber; $ProductDescription = $type->ProductDescription; } } foreach ($product->prices as $price => $amount) { $price_amount = $amount; } } } The problem I'm having is I don't know how to properly push into that new ```$priceResult``` array so that I can use PHPExcel to put it into a format with a subheader. The format I would want from the above example would be something like this Test Category 1 | 123 | Category for Testing ================================================== PRD123 | 12 | Product for Testing | 150.00 PRD112 | 17 | Product for Testing | 250.00 Test Category 2 | 321 | New Category for Testing ===================================================== PRD189 | 16 | Product for Testing | 450.00 PRD139 | 34 | Product for Testing | 350.00 So basically I want to call PHPExcel on my $priceResult array in order to get that format where I can list my category info, and for each product within that category, I'd have a product row. Everything would be grouped by the category main header $build = Excel::create($name, function ($excel) use ($priceResult) { $excel->setTitle('Test Products'); UPDATE: SO I'm thinking my desired resulting array would be something like: CategoryCode : 123 CategoryName : TestCategory CategoryDescription: For Testing Products{ 0{ Product_code : 123, CountNumber : 12, ProductDescription: Test Product, price_amount : 150.00 }, 1{ Product_code : 112, CountNumber : 32, ProductDescription: Test Product 2, price_amount : 250.00 } } That way each category can be a header and all of its products can be rows for each category
  5. I have a php script that I've been running that seems to have been working but now I'm wondering if some of my logic is potentially off. I select records from a db table within a date range which I put into an array called ```$validCount``` If that array is not empty, that means I have valid records to update with my values, and if it's empty I just insert. The trick with the insert is that if the ```STORES``` is less than the ```Quantity``` then it only inserts as many as the ```STORES``` otherwise it inserts as many as ```Quantity```. So if a record being inserted with had Stores: 14 Quantity:12 Then it would only insert 12 records but if it had Stores:1 Quantity:20 It would only insert 1 record. In short, for each customer I should only ever have as many valid records (within a valid date range) as they have stores. If they have 20 stores, I can have 1 or 2 records but should never have 30. It seems like updating works fine but I'm not sure if it's updating the proper records, though it seems like in some instances it's just inserting too many and not accounting for past updated records. This is the logic I have been working with: if(!empty($validCount)){ for($i=0; $i<$row2['QUANTITY']; $i++){ try{ $updateRslt = $update->execute($updateParams); }catch(PDOException $ex){ $out[] = $failedUpdate; } } }else{ if($row2["QUANTITY"] >= $row2["STORES"]){ for($i=0; $i<$row2["STORES"]; $i++){ try{ $insertRslt = $insert->execute($insertParams); }catch(PDOException $ex){ $out[] = $failedInsertStore; } } }elseif($row2["QUANTITY"] < $row2["STORES"]){ for($i=0; $i<$row2["QUANTITY"]; $i++){ try{ $insertRslt = $insert->execute($insertParams); }catch(PDOException $ex){ $out[] = $failedInsertQuantity; } } } } Let's say customer 123 bought 4 of product A and they have 10 locations customerNumber | product | category | startDate | expireDate | stores ---------------------------------------------------------------------------------- 123 1 A 2018-08-01 2019-03-01 10 123 1 A 2018-08-01 2019-03-01 10 123 1 A 2018-08-01 2019-03-01 10 123 1 A 2018-08-01 2019-03-01 10 Because they purchased less than their store count, I insert 4 records. Now if my ```$validCheck``` query selects all 4 of those records (since they fall in a valid date range) and my loop sees that the array isn't empty, it knows it needs to update those or insert. Let's say they bought 15 this time. Then I would need to insert 6 records, and then update the expiration date of the other 9 records. customerNumber | product | category | startDate | expireDate | stores ---------------------------------------------------------------------------------- 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 123 1 A 2018-08-01 2019-03-11 10 There can only ever be a maximum of 10 (store count) records for that customer and product within the valid date range. As soon as the row count for that customer/product reaches the equivalent of stores, it needs to now go through and update equal to the quantity so now I'm running this but it's not running and no errors, but it just returns back to the command line $total = $row2['QUANTITY'] + $validCheck; if ($total < $row2['STORES']) { $insert_count = $row2['QUANTITY']; $update_count = 0; } else { $insert_count = $row2['STORES'] - $validCheck; // insert enough to fill all stores $update_count = ($total - $insert_count); // update remainder } for($i=0; $i<$row2['QUANTITY']; $i++){ try{ $updateRslt = $update->execute($updateParams); }catch(PDOException $ex){ $failedUpdate = "UPDATE_FAILED"; print_r($failedUpdate); $out[] = $failedUpdate; } } for($i=0; $i<$insert_count; $i++){ try{ $insertRslt = $insert->execute($insertParams); }catch(PDOException $ex){ $failedInsertStore = "INSERT_STORE_FAILED!!!: " . $ex->getMessage(); print_r($failedInsertStore); $out[] = $failedInsertStore; } }```
  6. @ginerjm thank you, you're right. I'm dumping each variable and they return NULL
  7. @requinix thank you for your answer. I've tried this using this for my php: $pageID = $_POST['currentPageID']; $newTime = $_POST['datePicker']; $newEndTime = DateTime::createFromFormat('m/d/Y h:i A', $newTime); $convertedDateTime = $newEndTime->format('Y-m-d H:i:s'); $extendExpiration = " UPDATE pages SET end_time = '$convertedDateTime' WHERE id = '$pageID'; "; But I'm still getting an error in my developer network console: "Undefined index: currentPageID Undefined index: datePicker Call to a memeber function format() on boolean"
  8. I only put that in for another testing function, but upon success I'm removing it
  9. I'm having an issue related to creating forms within a foreach loop. As of now, I have 3 forms in a div, each with their own datepicker instance and submit button ( also using a hidden input for the pageID) The datepickers are unique and working independently, but when it comes to posting the data via ajax and handling it in a php script, It's not inserting data into my db because the names of my two inputs are not unique. I don't really know how to make them unique on the form and then handle those unique names back in the script. Only one form will be submitted at a time so I'm trying to make it to where the user can click on any of these datepickers, select a date and submit, and the ajax will handle only the page ID and the datepicker value for that form submitted and the PHP script will then insert. The sql in my php script works but my POST variables are where I'm having the issue here. Any help is much appreciated. <?php foreach($expiredPages as $expiredPage): ?> <form id="updateTime_<?php echo $expiredPage['id']?>" class="updateTime" method="POST"> <input type="hidden" name="currentPageID<?php echo $expiredPage['id']?>" value="<?php echo $expiredPage['id']?>"> <div class="datepick input-group date" id="datetimepicker_<?php echo $expiredPage['id']?>" data-target-input="nearest"> <input type="text" class="form-control datetimepicker-input" data-target="#datetimepicker_<?php echo $expiredPage['id']?>" name="datePicker<?php echo $expiredPage['id']?>" /> <span class="input-group-addon" data-target="#datetimepicker_<?php echo $expiredPage['id']?>" data-toggle="datetimepicker"> <span class="fa fa-calendar"></span> </span> </div> <input type="submit" name="Extend Date" class="extendDate"> </form> <?php endforeach; ?> <script type="text/javascript"> $(".extendDate").click(function(){ event.preventDefault(); var string = $('.updateTime').serialize(); console.log(string); // AJAX Code To Submit Form. $.ajax({ type: "POST", url: "extendTime.php", data: string, dataType: 'json', cache: false, success: function(response){ location.reload(); } }); }); </script> extendTime.php $pageID = $_POST['currentPageID']; $newTime = $_POST[$dtPick]; $newEndTime = DateTime::createFromFormat('m/d/Y h:i A', $newTime); $convertedDateTime = $newEndTime->format('Y-m-d H:i:s'); $extendExpiration = " UPDATE pages SET end_time = '$convertedDateTime' WHERE id = '$pageID'; "; if($mysqlConn->query($extendExpiration)=== TRUE){ echo "SUCCESS"; }else{ echo "Could not extend Time"; }
  10. @requinix I feel like I'm on the right track logically maybe? My problem is, if at any time I have 3 or 4 different sized divs with their own textarea/tinymce editor, how can I fairly simply arrange this to save the content of each with a corresponding value from each (1,2,3,etc.) to identify the panel type, all while still only inserting one page record?
  11. Can you give an example of the 2nd option? Again, this works for me as far as inserting 1, but I really just need help to use multiple instances of tinymce editors and potentially looping my insert for content and panels. I'm so beyond stuck here
  12. I have a CMS that I've built allowing users to create a 'Page' made up of different panels each with their own content. I've made this work so that a user can create a page with one panel and one textarea of content but I still can't figure out how to do this for multiple panels/content. Currently, if the page load gets the value of '1' from the URL value, it loads html templates with a fullwidth div and halfwidth div. I'm trying to set the panel type of each with hidden input types and they each have their own tinymce text area. <?php if($value == 1){?> <form method="post"> <div class="col-lg-12 fullWidth" id="full"> <input type="hidden" name="fullWidth" value=""> <div class="fullContent" style="background-color: white; height: 100%;"> <form id="form-data3" method="post"> <textarea class="original" id="mytextarea3" name="fullText">Some Text Here</textarea> <input type="submit" value="Save Content"> </form> </div> </div> <div class="col-lg-12 halfWidth" id="half"> <input type="hidden" name="halfWidth" value=""> <div class="halfContent" style="background-color: white; height: 100%;"> <form id="form-data4" method="post"> <textarea class="original" id="mytextarea4" name="halfText">Some Text There</textarea> <input type="submit" value="Save Content"> </form> </div> </div> </form> <?php } ?> Once this is done and they go to save page, there is another form that lets them set the title of the page and it also gets the page type ($value from above) <form action="addPage.php" method="post"> <input type="hidden" name="pageType" value="<?php echo $value;?>">//This comes from the url value <input class="form-control" id="addTitle" name="addTitle"> <input type="submit" name="Save Page"> The problem is, when I now call my addPage.php script to insert the records, I don't know how to pass the values correctly so that I add one page record (the $value for page_type_id and the Title) but then insert 2 content and panel records for the text areas. Here's my expected insert in the case of the above code: pages ID | Title | page_type_id 1 | TitleNew | 1 /*this comes from $value*/ content ID | Content 1 | Some Text Here 2 | Some Text There panels ID | panel_type_ID | page_id | content_id 1 | 1 | 1 | 1 2 | 2 | 1 | 2 This works for one insert in all 3 tables but if I can set multiple panel types to each div, how can I modify this to still insert the one page record but successfully account for multiple panel and content? Here's the add page script //Insert Page $title = $_POST['addTitle']; $page_type = $_POST['pageType']; $addpage = " INSERT INTO pages (title, page_type_id) VALUES ('$title','$page_type'); "; $mysqlConn->query($addpage) $page_id = $mysqlConn->insert_id; //Insert Content $content = $_POST['page_content']; $addContent = " INSERT INTO content(content) VALUES('$content'); "; $mysqlConn->query($addContent); $cont_id = $mysqlConn->insert_id; //Insert panel(s) $panelID = $_POST['panelType']; $addPanel = " INSERT INTO panels(panel_type_id, page_id, cont_id) VALUES ('$panelID', '$page_id', '$cont_id'); "; $mysqlConn->query($addPanel);
  13. @requinix this is a further developed example of a question you helped me with the other day. Could you help me understand how I would serialize here? Basically submitting multiple records for each panel type but only one record for page type
  14. @dalecosp this is the question I was referencing, it uses a lot of the principals from the last question but I'm trying to set input types for the different divs and then loop if possible
  • 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.