Method for comparing array keys and creating MySQL Statements

Question

In a user system we are building, a client is able to sell a number of products that we offer. The number of these products is not too extensive but we may (and plan to add new products often). When a user logs into their administration portal, they have the ability to enable or disable the products they would like to sell from a list of all available products offered.

We have the form built to accept values for 'enabled' products using checkboxes and a checkboxed name="products[]" array. This part all works fine. The issue comes into play when we are trying to build out the best INSERT / UPDATE methods for the data entered. Values would be INSERTED in the event a customer is offered a new product (by us) at a later time, and choose to enable that product. An update would occur by simply checking or unchecking the corresponding product checkbox, and thus enabled or disabling that product.

The INSERT and UPDATE if a product is currently disabled but I cannot come up with an array compare to properly UPDATE the product to 'disabled' if it is currently set to enabled in the DB.

 foreach($_REQUEST['products'] as $productID) {
    if ( array_key_exists($productID, $_SESSION['productsOffered']) && $_SESSION['productsOffered'][$productID]['status'] == 0) {
        echo 'this product would be turned active.';
        echo "<br>";
    }
    elseif ( array_key_exists($productID, $_SESSION['productsOffered']) && $_SESSION['MYADMIN']['products'][$productID]['status'] == 1) {
        echo 'this product would be turned inactive.';
        echo "<br>";
        <b>THIS IS THE LOGIC WHICH DOES NOT WORK</b>
    }
    elseif ( !array_key_exists($productID, $_SESSION['MYADMIN']['products']) ) {
        echo 'insert';
        echo "<br>";
    }
}

As mentioned, the IF and final elseif here work fine but the middle statement, in english: "If the user unchecked a product which was originally set to active, disable that specific productID", is not right.

Any advice given is appreciated.

Update 1: Initial Thought

I believe a method which may work would be to build a new array of all current $_SESSION['productsOffered'] and find out which current products (at time of update) are currently set to active. Then I would be able to compare the new array with the $_REQUEST['products'] array and determine which products had been left out (or essentially unchecked) and then could update the appropriate product.

While I believe this method may work, it seems very sloppy. Update2: This also would have to be done outside of the current foreach request because obviously the deactivated product would not exist in the REQUEST.

Update 3

By request, some sample data from the arrays is as follows (it is very simple data and near 'real'):

$_SESSION['productsOffered'] = array(
    [1] => Array
        (
          [ID] => 1,
          [status] => 1,
          [name] => Product 1,
        )

    [2] => Array
        (
          [ID] => 2,
          [status] => 1,
          [name] => Product 2,
        )

);

$_REQUEST['products'] = array('1','2','3','4','5','6','7');

are you also storing the inactive products in the $_SESSION['productsOffered'] array at the time of initialization ? — Maximus2012, Jul 16 '13 at 21:48
Based on this information, I think the initial answer that I gave makes sense. Since, there is no way for you to check the "non-selected" products against the database or session directly, it makes sense to disable the previously enabled products first and then only enable the products that were selected in this session. — Maximus2012, Jul 16 '13 at 22:08
@Maximus2012 - I could check against session. I am currently writing out all the code to test which makes most sense. In short, I create an array of all "active ID's" from the initialization SESSION. I then compare that to the REQUEST. If I notice that a value does not exist in the request but did in the activeArray-from-SESSION, I know it is an ID which needs to be marked 'inactive' on the UPDATE. — JM4, Jul 16 '13 at 22:18
You could do that but then you'll have to carefully update the session values too when the user updates the product choice. Otherwise, if the user makes updates multiple times in a session, then you will always be checking against stale values (the ones that you initialized at the time of user log in which may or may not be correct). This approach could also cause some issues if the user is using this application in say multiple tabs and the session values get overwritten with wrong values. — Maximus2012, Jul 16 '13 at 22:25
good point - I'll use your method for now and continue to test additional possibilities as time goes on. Thank you for your help. — JM4, Jul 16 '13 at 23:30

score 1 · Accepted Answer · answered Jul 16 '13 at 20:03

1

$_REQUEST['products'] will only contain products that have been marked as checked by the user. So it will not contain products that have not been checked. Which means in the code above, the control will never go to the 2nd part of the code which is what you are trying to do. One approach that you can take here would be to initially disable all the products when the user submits the form. A sample SQL would be:

$sql = "update product_table set disabled='true' where user_id = 'this_user_id'";

then you can iterate through your logic to mark the products that the user did select:

foreach($_REQUEST['products'] as $productID) {
    if ( array_key_exists($productID, $_SESSION['productsOffered']) && $_SESSION['productsOffered'][$productID]['status'] == 0) {
        echo 'this product would be turned active.';
        echo "<br>";
    }
    elseif ( !array_key_exists($productID, $_SESSION['MYADMIN']['products']) ) {
        echo 'insert';
        echo "<br>";
    }
}

This is somewhat similar to the logic that you posted in Update 1: Initial Thought. Some more information like what are your $_SESSION['productsOffered'] array and sample $_REQUEST['products'] array would be helpful.

answered Jul 16 '13 at 20:03

Maximus2012

1,799
2
12
15

it would require an extra execute query to do so but I had not thought of simply disabling every product then running through the logic as you suggest. I am considering the implications if the update runs but something else happens after, causing products to be permanently disabled but seems like a viable option. – JM4 Jul 16 '13 at 20:08
There are ways to fix i believe of note, disabling all products ahead of time will not get the desired effect. Say all products were enabled and the user only wanted to disable a single product, this logic would disable all products without the ability to re-enable them. – JM4 Jul 16 '13 at 20:45
I am assuming that in order to enable a product, a user has to select it using a checkbox. If that is the case then the logic would still work even if the user wants to disable a single product. The limitation that you are facing for this application is because of the way information is passed from one page to another through checkboxes. Checboxes pass only the values for the fields that have been selected and give no information about the fields left unselected. – Maximus2012 Jul 16 '13 at 20:55
@Maximum2012 - understood. Just updated with some sample data that is similar to "real" information. – JM4 Jul 16 '13 at 21:16

score 0 · Answer 2 · answered Jul 16 '13 at 19:44

0

Do you know about the REPLACE verb in mysql?

Add new if does not exists, update if it does exist.

answered Jul 16 '13 at 19:44

RiggsFolly

93,638
21
103
149

the issue is not decifering how to determine INSERT OR UPDATE, it is how to correctly recognize and create the update statement based on an existing products current status and the status input by the user. – JM4 Jul 16 '13 at 19:46
additionally - REPLACE requires "INSERT" and "DELETE" privileges. Let's assume for this question, the DB user does not have delete privileges for a number of reasons. – JM4 Jul 16 '13 at 19:48

Method for comparing array keys and creating MySQL Statements

Update 1: Initial Thought

Update 3

2 Answers2