Jump to content

MYSQL Insert: Whatever's inside JSON array?


ChenXiu
 Share

Go to solution Solved by Barand,

Recommended Posts

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 by ChenXiu
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

Posted (edited)
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 by ChenXiu
Link to comment
Share on other sites

  • Solution
Posted (edited)
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 by Barand
typo
  • Great Answer 1
Link to comment
Share on other sites

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.

  • Thanks 1
Link to comment
Share on other sites

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!  :-)

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

 

  • Like 2
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

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