Barand

June 23, 2019

"color" attribute refers to content

"border-color" refers to border.

try

 'border: 1px solid red'

June 22, 2019

I think the problem is this line ...

$update->bindParam(':item_qty', $item_q - $_POST['item_qty'][$k]);

... where you are attempting to bind an expression and not a variable.

Try changing the query (and you also need to bind the category)

    $update = $pdo->prepare("
                        UPDATE stocks
                        SET item_qty = item_qty - :item_qty                 -- changed line
                        WHERE category = :category
                        AND  product_name = :product_name
                        AND item_size = :item_size
                        AND item_type = :item_type
                        ");
    foreach($_POST['category'] as $k => $category){
        $update->execute( [ 
                            ':item_qty'    => $_POST['item_qty'][$k],
                            ':category'    => $category,
                            ':product_name'=> $_POST['product_name'][$k],
                            ':item_type'   => $_POST['item_type'][$k],
                            ':item_size'   => $_POST['item_size'][$k]
                          ] );  
    }

June 22, 2019

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.

June 21, 2019

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;

June 19, 2019

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  |
+---------+-------------+

June 19, 2019

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.

June 19, 2019

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>

June 19, 2019

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.

June 19, 2019

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

June 19, 2019

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'];

June 19, 2019

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>

June 18, 2019

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            |
                                    +------------------+

June 18, 2019

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

June 17, 2019

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>

June 17, 2019

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>

June 17, 2019

https://www.google.com/search?client=firefox-b-d&q=top+php+ide+2019

June 17, 2019

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

June 16, 2019

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..

June 16, 2019

You might have read it but you totally ignored it.

8 hours ago, Barand said:

You need to append "[ ]" to your input names so they are posted in arrays (EG name="category[]" )

Good bye.

June 16, 2019

Read the replies.

7 hours ago, Barand said:

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[]" )

June 16, 2019

Can you post the output you are now getting from

echo '<pre>', print_r($_POST, 1), '</pre>';

June 16, 2019

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']}";
}

June 16, 2019

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            |
                                    +------------------+

June 16, 2019

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>;';
    
}

June 16, 2019

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();

Sign In

Barand

Posts

Joined

Last visited

Days Won

Content Type

Profiles

Forums

Posts posted by Barand

Text is red as well as border

Insert into database at all at once

Insert into database at all at once

phpspreadsheet create a new worksheet on salesperson name change

from MySQL to MySQLi - the script for errors in the db-connection

Apply different tax rate based on customer roles

Issue with PHP

Issue with PHP

Apply different tax rate based on customer roles

Apply different tax rate based on customer roles

Issue with PHP

Insert into database at all at once

Issue with PHP

Issue with PHP

Issue with PHP

What Top PHP 2019 IDE Editors voted for simplistic coding?

Insert into database at all at once

Insert into database at all at once

Insert into database at all at once

Insert into database at all at once

Insert into database at all at once

Strange outcomes with $stmt->fetchColumn()

Insert into database at all at once

Insert into database at all at once

Strange outcomes with $stmt->fetchColumn()

Browse

Activity

Important Information