#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2017
    Posts
    2
    Rep Power
    0

    Use values in one table to update another table


    Hi all the DevShed community.

    I am a bit new in PHP and MySQL and I am stacked to write my programs. Any suggestion is more than welcome !

    Here is my problem and my question. I am looking to run a calculation in PHP using a variable that I retrieve from a API / data-feed in JSON and other variables stores in a table within my database.

    Here is the code to retrieve my variable from a JSON file. Here no problems :

    PHP Code:
     $json file_get_contents('https://gold-feed.com/paid/API....php');
        
    $XAUGBP json_decode($json);
        
    $XAUGBP $XAUGBP->gold_bid_gbp_toz
    Then I have the following tables in my database :

    PHP Code:
    TABLE 1                                                       TABLE 2

        id 
    product weight(gr) | margin fee               id bafa
         
         1    Kilo       1000        1.03    20                1    3
         2    500G       500         1.04    20
         3    250G       250         1.05    20
        
    ...   ...        ...         ...     ... 
    The calculation is really simple. I have to do :

    1) adda the **bafa** from TABLE 2 to $XAUGBP and transform it into grams :

    *($XAUGBP + bafa)/31.1035*
    example : (960+3)=963/31.1035 = 30.96

    2) then I have to use this result to run the calculation as a loop for all the rows, using the data from TABLE 1 :

    PHP Code:
    $result 30.96  weight margin fee 
    I don't know if I should convert the entire table into a two-dimensional array or use *mysqli_query* to store each parameters into a variable.

    How should I do it ? What would you recommend to write that ?
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,274
    Rep Power
    9645
    What are you going to do with each $result?

    Part 1 is just coming up with a number. Use a query to get the bafa value, then do the math
    PHP Code:
    $pergram = ($XAUGBP $bafa) / 31.1035
    Part 2 is using that value. You can put it in a query, you can use it in more PHP code, it depends what you want to do next...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2017
    Posts
    2
    Rep Power
    0
    Originally Posted by requinix
    What are you going to do with each $result?
    I want to use each $result to update the real price in an other table that corresponds to the products that I create in my CMS (I use Prestashop).

    I am looking to do that through a while loop and matching the id for each products. Like in the picture below :

  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,274
    Rep Power
    9645

    moved from PHP "Convert SQL query into two dimensional array PHP"


    That's obviously not working so just use a link. https://www.dropbox.com/s/zv1qh5ta2u...2014.58.28.png

    If you want to use this to update data in another part of the database then you can do it all with one query.

    What is your table structure?
    Last edited by requinix; July 7th, 2017 at 09:14 AM.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,698
    Rep Power
    4288
    moderator note: your pictures aren't working (i deleted two other posts)

    use the "Go Advanced" button and upload your images there

    better yet, find a way to describe your data without resorting to screenshots
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo