MYSQL Insert: Whatever's inside JSON array?

[ChenXiu]

An affiliate marketer refers some products which are stored in a simple array.
My website creates a JSON variable of all products sold.

$all_items_sold = '{"7777":{"item":"hammer","price":"4.99"},"8888":{"item":"nail","price":"1.99"},"9999":{"item":"apple","price":"2.00"}}';
$referred_by_Affiliate = array('1234','8888','7777');

So, out of all the 3 items that sold, only 2 of them were referred by the affiliate marketer.
DESIRED EFFECT: insert this product 8888: $1.99 and insert this product 7777: $4.99 into mysql.

Currently I do this:
1.) foreach loop,
2.) use strpos to see if it's in the raw JSON variable.
3.) If there, I use preg_match to find the price.
4.) Do a mySQL insert while still in the foreach loop.

Is there a "best practices" way to accomplish this?
I'm guessing there is a "one liner" so I don't have to do a foreach loop and using strpos.
And I'm guessing there is a way to do multiple mySQL inserts all at once with just one line of code, instead of from inside a foreach loop.
This probably can all be accomplished with just one line of code total, error trapping included 😁

Thank you.
 

Edited July 28, 2021 by ChenXiu

[NotionCommotion]

First of all, definitely do not use strpos and preg_match but convert your JSON string to an array using json_decode($all_items_sold, true).  Alternatively, you can convert to an object by I prefer an array.  Recommend standard json_decode error checking as well.

Regarding multiple inserts, sure you can do it;

INSERT INTO 
    your_table(product , item, price)
VALUES
    (7777,'hammer',4.99),
    (8888,'nail',1.99);

But don't do direct inserts like this and instead use a prepared statement.  Your place holders can either be something like :product or a question mark (?).  Go with the question mark format and use functions such as implode(', ', $array) and  rtrim(str_repeat('?,',count($arr), ',') to build your prepared statement.

[Barand]

Something like this, maybe (sorry it's more than 2 lines)

$all = json_decode($all_items_sold, 1);
$matched = array_filter($all, function($k) use ($referred_by_Affiliate) {
                                    return in_array($k, $referred_by_Affiliate);
                                },
                                ARRAY_FILTER_USE_KEY
                                );
$placeholders = $values = [];

foreach ($matched as $pid => $pdata) {
    $placeholders[] = "(?,?,?)";
    array_push($values, $pid, $pdata['item'], $pdata['price']);
}

$stmt = $db->prepare("INSERT INTO mytable (prod_id, item, price) VALUES " . join(',', $placeholders));
$stmt->execute($values);

 

[ChenXiu]

47 minutes ago, NotionCommotion said:

Regarding multiple inserts,

Perfect! I appreciate the implode and rtrim. And I'll use the "prepared statement" style -- but sometimes I wonder how necessary that is when the values are already sanitized (for example, the SKU numbers I'd be inserting have already been preg_replaced to just digits only, and will only be inserted if exactly 4 digits long, etc.)

Interestingly, I am suddenly at a loss how to access a JSON value using json_decode without the "true" (and impossible to search for on the internet because all the search results tell people to use the "true" part). Whenever I try to access the array values of just json_decode($var) I keep getting that error message about std class.

 

[ChenXiu]

12 minutes ago, Barand said:

sorry it's more than 2 lines

I know you really do know how to do this in just 1 line.... but, "we earthlings are not yet ready for such knowledge" haha 😃

I like the code -- Thank you, it looks perfect, I'm going to try it now.
...uh-oh.... what's that!...darnit -- that word "join" again... and not even using 2 tables. (Just when I thought I had understood what "join" meant.) Back to my mySQL studybooks I guess.

I was going to search the internet again for a function on how to use "array_diff" or "array_intersect" using just keys (If I use json_decode, I have an array keyed with the sku numbers) and then I can intersect it with that simple array.

But I see your code already does that!

Thank you again!

Edited July 28, 2021 by ChenXiu

[Barand]

1 hour ago, ChenXiu said:

I am suddenly at a loss how to access a JSON value using json_decode without the "true"

Here's a shortened version. It also uses json_decode without the "true" to give an object (containing objects)

$all = json_decode($all_items_sold);

$placeholders = $values = [];

foreach ($all as $pid => $prod){
    if (in_array($pid, $referred_by_Affiliate)) {
        $placeholders[] = "(?,?,?)";
        array_push($values, $pid, $prod->item, $prod->price);
    }
}

$stmt = $db->prepare("INSERT INTO mytable (prod_id, item, price) VALUES " . join(',', $placeholders));
$stmt->execute($values);

 

1 hour ago, ChenXiu said:

(Just when I thought I had understood what "join" meant.) Back to my mySQL studybooks I guess.

A PHP join has nothing to do with MySQL joins.

Edited July 28, 2021 by Barand
typo

[gw1500se]

46 minutes ago, ChenXiu said:

Interestingly, I am suddenly at a loss how to access a JSON value using json_decode without the "true" (and impossible to search for on the internet because all the search results tell people to use the "true" part). Whenever I try to access the array values of just json_decode($var) I keep getting that error message about std class.

After you json_decode without the true do the following:

echo "<pre>";
print_r($result);
echo "</pre>";

That will show you the resulting associative array so you can see how to reference the elements by key.

[ChenXiu]

32 minutes ago, Barand said:

A PHP join has nothing to do with MySQL joins.

[from php.net]:
   (PHP 4, PHP 5, PHP 7, PHP)
   join — Alias of implode()

So it's just an alias of implode....  (You did that on purpose!  :-)

[Barand]

29 minutes ago, ChenXiu said:

You did that on purpose! 

No - I always use join. It's less typing.

[NotionCommotion]

1 hour ago, ChenXiu said:

but sometimes I wonder how necessary that is when the values are already sanitized (for example, the SKU numbers I'd be inserting have already been preg_replaced to just digits only, and will only be inserted if exactly 4 digits long, etc.)

Necessary, maybe not, but you will live a happier life if you use json_decode() and deal with arrays or objects of your choosing.

1 hour ago, ChenXiu said:

Interestingly, I am suddenly at a loss how to access a JSON value using json_decode without the "true" (and impossible to search for on the internet because all the search results tell people to use the "true" part). Whenever I try to access the array values of just json_decode($var) I keep getting that error message about std class.

If you use the default json_decode($var), as you discovered, it is returned as a standard class and not an array, thus you cannot access it as an array.

JavaScript is obj.property as PHP is $obj->property. 

[mac_gyver]

4 hours ago, Barand said:

$matched = array_filter($all, function($k) use ($referred_by_Affiliate) {
                                    return in_array($k, $referred_by_Affiliate);
                                },
                                ARRAY_FILTER_USE_KEY
                                );

less...

$matched = array_intersect_key($all, array_flip($referred_by_Affiliate));