Jump to content

thara

Members
  • Posts

    589
  • Joined

About thara

  • Birthday 02/05/1983

Profile Information

  • Gender
    Male
  • Location
    Panda

Contact Methods

  • Skype
    tharangagamage

Recent Profile Visitors

11,428 profile views

thara's Achievements

Advanced Member

Advanced Member (4/5)

5

Reputation

  1. Let me explain this little bit more.. Through my question I have pointed out a "Buy and pay a fixed price" type of offer. (Eg: Buy 3 SAVE 25 / 😃 Suppose the selling price of item X is 70.00. The owner decides to make an offer for that X item. Think that offer named as "Buy 3 SAVE 30 / =". That offer means that 3 of the X items will sell for 180 / -. Through the system, the shop owner has the ability to create, delete or edit this kind of offers for a product. He has the ability to change an offer created in this way at any time. Each time that is changed, the data related to the changed offer should be entered as a separate row in the database.This change can even happen to a single day also. In such a case, There is a possibility that the offer made to the customer in the morning may be changed or not received in the evening. Each of these offers has a relevant date range, so when adding a new offer to the table, you should be careful about the date range of the previous offer related to that particular item. For that reason the previous offers' date range should be updated accordingly, then the conditions should be applicable as I mentioned in the question above. For better understanding, here I have attached an image of my offer input form:
  2. Hi All, I have some issues when I trying to insert/update data into mysql through php. Hoping someone may help me out. This is how I store data in my table: select * from offer; +----------+---------------+---------------------+---------------------+-----------------------------+------+ | offer_id | offer_type_id | valid_from | valid_until | terms | name | +----------+---------------+---------------------+---------------------+-----------------------------+------+ | 1 | 1 | 2022-04-29 21:26:38 | 2022-04-30 21:28:09 | {"qty": 4, "total": 260.00} | aaa | | 2 | 1 | 2022-04-30 21:26:38 | 2022-05-25 23:59:59 | {"qty": 2, "total": 150.00} | bbb | | 3 | 1 | 2022-05-10 21:26:38 | 2022-05-20 23:59:59 | {"qty": 3, "total": 200.00} | ccc | +----------+---------------+---------------------+---------------------+-----------------------------+------+ This is how it look my POST array: (not for the data in the table shown above) Array ( [offerName] => Array ( [34] => aaa [35] => bbb [36] => ccc ) [quantity] => Array ( [34] => 3 [35] => 2 [36] => 4 ) [offerPrice] => Array ( [34] => 200 [35] => 150 [36] => 250 ) [validFrom] => Array ( [34] => 2022-04-29 [35] => 2022-04-30 [36] => 2022-05-01 ) [validUntil] => Array ( [34] => 2022-04-30 [35] => 2022-05-05 [36] => 2022-05-20 ) [offer_type] => multisellTotal ) Insert/Update conditions: If the "validFrom" date in POST array overlaps the previous offer in the table, then previous offer record need to be updated as following. valid_until = (validFrom - 1 second) and "validFrom" date of new offer = (validFrom H:i:s) (H:i:s is the time when the script is executed.) If the "validFrom" date in POST array equal to "curdate()", then previous offer record need to be updated as following. valid_until = (now() - 1 second) and "validFrom" date of new offer = now(); If the "validFrom" date in POST array is not overlaps the previous offer and new offer have future dates, then previous offer record updation should be ignore, and new offer records need to be inserted with relavant "validFrom" and "validUntil" dates. when inserting "validFrom" time should be "00:00:00" and "validUntil" time shold be "23:59:59" This is how I tried it. But it doesn't work as I needed. $d1 = new DateTime(); $d2 = clone $d1; $d2->modify("-1 second"); $today = $d1->format('Y-m-d H:i:s'); $yesterday = $d2->format('Y-m-d H:i:s'); $offerType = isset($_POST['offer_type']) ? $_POST['offer_type'] : ''; $prvOferUpdt = $newOferUpdt = []; if ($offerType != '' && $offerType == 'multisellTotal') { foreach ($_POST['validUntil'] as $key => $endDate) { if ($endDate < $_POST['validFrom'][$key]) { $messages = array('success'=>false, 'message'=>'Start date should be less than to End date.'); echo json_encode($messages); return; } $start_date[$key] = $_POST['validFrom'][$key]; $end_date[$key] = $_POST['validUntil'][$key]; } $sql = "SELECT offer_id FROM offer o JOIN offer_item i USING(offer_id) WHERE i.item_id = ? AND ? BETWEEN valid_from AND valid_until AND o.offer_id = i.offer_id"; $stmt = $pdo->prepare($sql); $i = 1; foreach ($start_date as $id => $sdate) { $stmt->execute([$id, $sdate]); $offerID = $stmt->fetch(); // If offer already available and new offer's start date is still // in between previous offer's date_from and date_valid: // then, first closed the old offer and set its date_valid for NOW()-1 second // and set to new offer's date from to NOW(): // Create array to store offer_id and valid_until date which // is need to be updated in previous offer: $oferStart1 = DateTime::createFromFormat( "Y-m-d", $sdate); $oferStart2 = DateTime::createFromFormat( "Y-m-d", $sdate); $oferStart2->modify("-1 second"); if (isset($offerID['offer_id'])) { $diff = $d1->diff($oferStart1); $diffDays = (integer)$diff->format("%R%a"); if ($diffDays == 0) { $prvOferUpdt[$offerID['offer_id']] = $yesterday; $newOferUpdt[$id] = $today; } else if ($diffDays > 0) { $prvOferUpdt[$offerID['offer_id']] = $oferStart2->format("Y-m-d H:i:s"); $newOferUpdt[$offerID['offer_id']] = $oferStart1->format("Y-m-d H:i:s"); } } else { // IF offer start date is a future Date: // $prvOferUpdt[$i] = $oferStart1->format("Y-m-d H:i:s"); $newOferUpdt[$i] = $oferStart1->format("Y-m-d H:i:s"); } $i++; } // Insert new offers into the DB: $query = "INSERT INTO offer ( offer_type_id , valid_from , valid_until , terms , name ) VALUES (1,?,?,?,?)"; $stmt = $pdo->prepare($query); $offerIDs = []; ksort($_POST['offerPrice']); foreach ($_POST['offerPrice'] as $id => $offerPrice) { //$offerPrice = floatval($offerPrice); if(!empty($offerPrice) && $_POST['validFrom'][$id] != '') { $quantity = intval($_POST['quantity'][$id]); $offerPrice = floatval($offerPrice); $offerPrice = number_format($offerPrice,2, '.', ''); $validFrom = date('Y-m-d H:i:s', strtotime($_POST['validFrom'][$id])); $validUntil = $_POST['validUntil'][$id]; $validUntil = ($validUntil != '' AND $validUntil != NULL) ? date('Y-m-d 23:59:59', strtotime($validUntil)) : '9999-12-31 23:59:59'; $offerName = (!empty($_POST['offerName'][$id])) ? filter_var($_POST['offerName'][$id], FILTER_SANITIZE_STRING) : 'Buy and pay a fixed price'; $terms = "{\"qty\": $quantity, \"total\": $offerPrice}"; $stmt->execute([$validFrom,$validUntil,$terms,$offerName]); $lastInsertID = $pdo->lastInsertId(); array_push($offerIDs, $lastInsertID); } else { $messages = array('success'=>false, 'message'=>'Offer Price and Start Date fields can NOT be empty.'); echo json_encode($messages); return; } } $op = array_filter($_POST['offerPrice'],'is_numeric'); $offerItems = array_combine(array_keys($op), array_values($offerIDs)); $sql = "INSERT INTO offer_item (offer_id, item_id) VALUES (?,?)"; $stmt = $pdo->prepare($sql); foreach ($offerItems as $k => $v) { $offerID = $v; $itemID = $k; $stmt->execute([$offerID,$itemID]); } // Then, update previous offers if exist: if (!empty($prvOferUpdt)) { $sql = "UPDATE offer SET valid_until = ? WHERE offer_id = ?"; $stmt = $pdo->prepare($sql); foreach ($prvOferUpdt as $oid => $date) { $stmt->execute([$date,$oid]); } } // If, newOferUpdt array is not empty, // then reset valid_from date for newly added offers: if (!empty($newOferUpdt)) { $newStartDates = array_combine(array_values($offerIDs), array_values($newOferUpdt)); $sql = "UPDATE offer SET valid_from = ? WHERE offer_id = ?"; $stmt = $pdo->prepare($sql); foreach ($newStartDates as $k => $dt) { if (isset($newStartDates[$k])) { $stmt->execute([$dt, $k]); } } } } Also attached sample SQL. Thank you. sql-dump.txt
  3. Thank you, Sir! This is what I was trying to achieve as alternative. I modified your code a bit to output the desired results and all works perfectly now. Yes, the main reason for updating this script was to reduce its execution time. The method you suggested works ten times faster than my version made using arrays and loops. For thousands of records, that process was simply very slow. Again, Thank you very much, Sir!
  4. Sir, Yes in that case, your gess is correct. But in my case I can't use "ON DUPLICATE KEY UPDATE" query for price table. Since every price have a date range. +---------------+-------------+-----------+---------------------+-------------+---------+ | item_price_id | sales_price | our_price | valid_from | valid_until | item_id | +---------------+-------------+-----------+---------------------+-------------+---------+ | 1 | 100.00 | 50.00 | 2022-04-26 15:50:36 | NULL | 42 | | 2 | 120.00 | 50.00 | 2022-04-26 15:50:36 | NULL | 41 | | 3 | 130.00 | 0.00 | 2022-04-26 15:50:36 | NULL | 43 | | 4 | 40.00 | 0.00 | 2022-04-26 15:50:36 | NULL | 40 | | 5 | 55.00 | 0.00 | 2022-04-26 15:50:36 | NULL | 38 | | 6 | 30.00 | 0.00 | 2022-04-26 15:50:36 | NULL | 39 | +---------------+-------------+-----------+---------------------+-------------+---------+ How I create my two array? One create from POST array as follows: // Sales Prices from POST array: $sp = $_POST['salesPrice']; // Remove 0.00 prices from array: foreach ($sp as $k => $v) { if ($sp[$k] == 0) { unset($sp[$k]); } } // Rearrange the POST array to access both prices in one place: foreach ($sp as $key => $value) { $newPrices[$key] = [$value, $_POST['ourPrice'][$key]]; } Other one is creating from SELECT query as follows: // Create new array from item_id: foreach ($sp as $iid => $sPrice) { $itemIDs[] = $iid; } // Select Prices from DB: $in = join(',', array_fill(0, count($itemIDs), '?')); $sql = "SELECT item_id,sales_price,our_price FROM item_price WHERE NOW() BETWEEN valid_from AND COALESCE(valid_until, '9999-12-31 00:00:00') AND item_id IN ($in)"; $stmt = $pdo->prepare($sql); $stmt->execute($itemIDs); $oldPrices = $stmt->fetchAll(PDO::FETCH_UNIQUE); Now I want execute insert / update queries comparing these two arrays..
  5. I have two multidimensional arrays as follows: Array 01 for new prices of items: Array ( [42] => Array ( [0] => 110 [1] => 0.00 ) [41] => Array ( [0] => 80 [1] => 70.00 ) [43] => Array ( [0] => 70 [1] => 60 ) [40] => Array ( [0] => 90 [1] => 80 ) ) 1 Array 02 for old prices of items: Array ( [42] => Array ( [sales_price] => 100.00 [our_price] => 0.00 ) [41] => Array ( [sales_price] => 80.00 [our_price] => 0.00 ) ) 1 Array key of both array are item ids (42,41,43,40). Now I need to compare $new and $old arrays to create update and insert queries. If the values in the new array are different from those in the old one, the table should be updated. If there are more elements in the new array than in the old array, they should be identified for the insert query. So basically I want to divide new array into two parts with comparing old array and considering the terms above. Expecting two arrays should be as follows: Array ( [42] => Array ( [0] => 110 [1] => 0.00 ) [41] => Array ( [0] => 80 [1] => 70.00 ) ) 1 Array ( [43] => Array ( [0] => 70 [1] => 60 ) [40] => Array ( [0] => 90 [1] => 80 ) ) 1 The Code I have so far: foreach ($priceNew as $newIds => $newPrices) { foreach($priceOld as $oldIds => $oldPrices) { } }
  6. Sir, this is my actual size of the paper Total width: 110mm Total Height: 31mm
  7. Sir, My label size is 34x25mm
  8. Sir I have been doing some experiments for a considerable time. Now it is at a satisfactory level. The printer settings also have the ability to adjust to a certain level.
  9. The maximum width of the sticker is used for the barcode.
  10. Yes sir, I have customized your code to suit my needs. Sir one thing, I have tested these codes with few actual barcode scanners. Sometimes, barcode scanners can't recognize a barcode or sometimes its take few seconds to recognize. And aslo, in some cases, I have to manage some distance between label and scaner to read the code. I have tested this for code 128 barcode and code 39 barcode scripts at FPDF. Both scripts work the same way.
  11. First, Thank you very much sir, for the big help. I tried with your code and its working fine for me. Sir, one thing, here on my printer i am using a sticker paper roll as shown in the image below: Using this code, labels can only be printed on the right side of the paper. As shown in the image below, If I print labels for other two columns, I can do it by adjusting printer setting on my printer. But its not easy for all who use the printer. Sir, can we modify above script to match this paper roll?
  12. Yes that code is working. But just I want to create custom label as attached image with dynamic data. Thats why I try to to create custom code as above. Sir, my I know, what would be the issue on my code?
  13. Using FPDF, I try to create barcode labels as the image attached below. For barcode printing in the label, here I am using Code 128 barcodes script at FPDF. This is how I tried it: //require('./fpdf/barcode-codabar.php'); $data = ['item_name' => 'Fuel Vapour Hose' ,'code_purchase' => 'ABC-2342' ,'code_sale' => 'DFS-4312' ,'item_code' => '47900001' ]; require('./pdf_code128.php'); $pdf=new PDF_Code128(); $barcode = $pdf->Code128( 4,3,$data['item_code'],30,10); class Barcode_Label extends FPDF { protected $data; protected $barcode; //constructor public function __construct(\PDF_Code128 $barcode) { parent::__construct('P','mm',[31,110]); $this->setFillColor(0xDD); $this->barcode = $barcode; } public function printLabel($labelData) { $this->data = $labelData; $this->AddPage(); $this->addLabel(); } private function addLabel() { $k = count($catdata['data']); $this->setFont('Times', 'B', 10); $this->Cell(30, 6, 'Label 1', 'B', 0, 'C'); $this->Cell(30, 6, 'Label 2', 'B', 0, 'C'); $this->Cell(30, 6, 'Label 3', 'B', 0, 'C'); $this->ln(2); } } $label= new Barcode_Label(); $label->printLabel($data); $label->Output(); But this code is not working for me and it gives me
  14. In my DB schema, there are 4 tables and its relationship as shown in the below attached image. My question is how many schools are there according to the data in the school table and how many of those schools belong to provinces, districts and zones, is it possible in one mysql query? Currently I am using 4 separate queries for this and the relevant code is as follows. $sql = "SELECT count(school_id) as schtot FROM sn_school"; $stmt = $pdo->query($sql); $schtot = $stmt->fetchColumn(); $schtot = str_pad($schtot , 3, 0, STR_PAD_LEFT); $sql = "SELECT count(zone_id) as zonetot FROM sn_school GROUP BY zone_id"; $stmt = $pdo->query($sql); $zonetot = $stmt->rowCount(); $zonetot = str_pad($zonetot , 3, 0, STR_PAD_LEFT); $sql = "SELECT COUNT(d.district_id) as districtTot FROM sn_school s JOIN zone z USING(zone_id) JOIN district d ON d.district_id = z.district_id GROUP BY d.district_id"; $stmt = $pdo->query($sql); $districtTot = $stmt->rowCount(); $districtTot = str_pad($districtTot , 3, 0, STR_PAD_LEFT); $sql = "SELECT COUNT(p.province_id) as ptot FROM sn_school s JOIN zone z USING(zone_id) JOIN district d ON d.district_id = z.district_id JOIN district p ON p.province_id = d.province_id GROUP BY p.province_id"; $stmt = $pdo->query($sql); $ptot = $stmt->rowCount(); $ptot = str_pad($ptot , 3, 0, STR_PAD_LEFT);
  15. Side Note: You also can't just insert PHP variables directory into URLs or HTML markup. They have to be escaped and encoded: <?php function html_escape($value, $encoding) { return htmlspecialchars($value, ENT_QUOTES | ENT_HTML5 | ENT_SUBSTITUTE, $encoding); } $url = 'icerik.php?'.http_build_query(['icerik' => $goster['icerik_id']]); echo '<a href="'.html_escape($url , 'UTF-8').'">'.html_escape($goster['baslik'], 'UTF-8').'</a>'; ?>
×
×
  • 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.