-1

EDIT - Disclaimer: Noob at PHP and SQL

I need to subtract a different value from each amount row based on the vegetable that is selected.

For example: If the user selects tomatoes and capsicum, I would need to subtract 200 and 400 from each of those amounts respectively.

Is there some way to attach these values to the vegetable? I have an array that looks like this:

**Array ( [0] => tomatoes [1] => onions [2] => spinach **

ingredientsTable +----+-------------+---------+--------+-------+ | id | name | type | amount | unit | +----+-------------+---------+--------+-------+ | 1 | tomatoes | veggies | 1000 | grams | | 2 | onions | veggies | 1000 | grams | | 3 | spinach | veggies | 1000 | grams | | 4 | capsicum | veggies | 1000 | grams | | 5 | basil | veggies | 1000 | grams | +----+-------------+---------+--------+-------+

I have an array of vegetables in PHP. I am thinking of iterating over that array and updating the column based on that vegetable. So possibly: 

foreach($vegetable as $subject) 
     ($query = $conn->prepare("UPDATE ingredients set amount = amount - ? where `name` = '$item';"))
     $query->bind_param("i", $amount);
     $query->execute();
     $insertresult = $query->get_result();
`

The issue is, I don't know how to change the $amount and I know this code does not subtract values at all.

At the moment, I've hardcoded $amount as 200, but I don't want to just update the amount for each row to 200. I need it to somehow subtract 200 from whatever the current value is - in this case it is 1000, so 1000 - 200, and update the value to 800. And for capsicum, I would need to subtract 400.

I've looked at Update multiple rows in 1 column in MySQL but that does appears not to be dynamic. It assumes we know in advance which rows to change and what the final value will be.

EDIT: I just checked Mysql query: decrease value by 1 which I think solves the problem of subtracting values, but it doesn't explain how to link those values to the vegetable in PHP. Example: How do I get and link the 200 amount to tomatoes, and 400 to capsicum?

EDIT 2: Updated code to subtract value and corrected syntax, clarified question.

EnterPassword
  • 580
  • 1
  • 6
  • 22
  • Possible duplicate of [Mysql query: decrease value by 1](https://stackoverflow.com/questions/21813741/mysql-query-decrease-value-by-1) – Shadow Nov 01 '19 at 22:30
  • Thanks, I think that helps the subtracting values part. Do you know how I could bind the amount to the vegetable value each loop? Is there some way to create a key value pair of vegetable and amount? I'm currently getting vegetables as an associative array. – EnterPassword Nov 01 '19 at 22:36
  • That isn't how UPDATE works. Please read the documentation on this function https://dev.mysql.com/doc/refman/8.0/en/update.html - What you have now resembles an INSERT. – Funk Forty Niner Nov 01 '19 at 23:47
  • You will also be updating your entire table by not using a WHERE clause. – Funk Forty Niner Nov 01 '19 at 23:48
  • Yeah I just realised that the syntax was completely off. I'll update this post. – EnterPassword Nov 01 '19 at 23:49
  • Also, please add a reason if you downvote. Helps me ask better questions. – EnterPassword Nov 01 '19 at 23:54

1 Answers1

0

The answer was to modify the way I was getting the data. So from this:

<input type="checkbox" name="topping[]" id="toppingCheckbox" value="tomatoes">

I needed to instead change the name and value attributes:

<input type="checkbox" name="topping[tomatoes]" id="toppingCheckbox" value="200">

The subtraction was a simple sql query, answered here. Mysql query: decrease value by 1

EnterPassword
  • 580
  • 1
  • 6
  • 22