How to reduce item quantity in stock?

PHP
1

For example, there are 20 products in the stock table,when reduce 5 number tell there is 15 in table stock

stoch table
2022-08-29_191059

sell table
2022-08-29_191253

2

You would use a UNION ALL query to calculate the total when needed, getting the SUM() from the stock table, grouped by the item id, minus the SUM() from the sell table, grouped by the item id. Since you didn’t post complete column information, and assuming you want the total for item id = 62 (or any set of ids), the query would look like this -

SELECT i.name, SUM(x.qty) qty
 FROM items i
 LEFT JOIN (
 SELECT item_id, SUM(qty) qty FROM stock WHERE item_id IN(62) GROUP BY item_id
 UNION ALL
 SELECT item_id, -SUM(qty) qty FROM sell WHERE item_id IN(62) GROUP BY item_id
) x ON i.id = x.item_id
GROUP BY i.id
ORDER BY i.name

This assumes that your data is properly normalized and you have an item table, where the items are defined. This produces an item id that you would us in any stock or sell records to relate the item back to its definition.

4

If the Implant_s form field is the id of the selected Implant System, wouldn’t you use that value to determine which row in the section table to operate on?

Here’s a laundry list of issues and things that can be simplified in the posted code -

  1. A header() statement does not stop php code execution. You need an exit/die statement after the ‘admin’ redirect to stop all the rest of the code from being executed.
  2. The post method from processing code should be above the start of the html document, so that you can decide what to do on the page based on the result of the form processing code.
  3. Don’t use the @ error suppressor, ever. After the form has been submitted, except for unchecked checkbox/radio fields, all form fields will be set and will exist. If you were getting undefined index errors from this code, it was due to something you needed to find and fix, not hide the errors for.
  4. Don’t try to test if the submit button is set. There are cases where it won’t be. You should instead detect if a post method form was submitted. If you can have more than one form processing code on a page, use a hidden field with a unique value in it to control which form processing code to execute.
  5. Whatever the output() function code is doing is probably incorrect for input data. Except for trimming data, so that you can detect if all white space characters were entered, you should NOT modify input data before using it.
  6. You should keep the form data as a set in a php array variable, then operate on elements in this array variable throughout the rest of the code.
  7. Once you do item #6 on this list, you can trim all the input data at once using one single php line of code.
  8. After trimming the data, you need to validate each input separately, storing user/validation errors in an array using the field name as the array index. For example, if Implant_s and Impalnt_n are ‘required’, you need to validate that they contain permitted values. If they don’t, that’s an error and you must setup messages for the user telling them what is wrong with the data that they submitted.
  9. After the end of all the validation logic, if the array holding the user/validation errors is empty, use the submitted form data.
  10. If you have more than about 2-3 form fields you should use a data-driven design, where you have a data structure (array) that holds a definition of the expected form fields, what validation to perform on each one, and what processing to perform. You would then loop over this definition and user general-purpose code to validate and process the data.
  11. Often, conditional logic ‘failure’ code is much shorter than the ‘success’ code. If you invert the condition being tested and put the ‘failure’ code first, it is easier to follow what your code is doing.
  12. You should build sql queries in php variables. This makes debugging easier. You can echo the sql query statement to see what it is. This also separates the sql query syntax from the php code, reducing the number of typo mistakes.
  13. You should list out the columns you are selecting in a query. This helps prevent mistakes and makes your query self-documenting.
  14. Don’t copy variables to other variables for nothing.
  15. If you set the default fetch mode to assoc when you make the database connection, you don’t need to specify it in each fetch statement.
  16. If you use implicit binding, by supplying an array of data to the ->execute([
]) call, you can greatly simplify all your code.
  17. If you use positional ? place-holders, you can greatly simplify all your code.
  18. Leave columns out of an insert query if you are not supplying values for, e.g. the id column.
  19. If this INSERT query can result in duplicate data, you need to have error handling for the query to detect if a duplicate error occurred, and setup a message for the user letting them know what was wrong with the data that they submitted.
  20. After the end of all the form processing logic, if there are no errors, redirect to the exact same url of the current page to cause a get request for the page. This will prevent the browser from trying to resubmit the form data.
  21. If you want to display a one-time success message, store it in a session variable, then test, display, and clear the session variable at the appropriate location in the html document.
  22. An empty action=“” attribute is actually invalid html5. To get the form to submit to the same page, simply leave out the entire action attribute.
  23. If you put the <label>...</label> tags around the field they belong with, you can leave out the for=‘
’ attribute and the matching id=‘
’ attribute (which most are missing/don’t match anyways.) Only use attributes in your markup when they are needed.
  24. The first <option> in a list should be a prompt to select one of the choices. The value for this first option should be an empty string. This will require the user to actually make a choice, it will let you validate the input (an empty string won’t pass validation), and it will let the ‘required’ attribute work.
  25. It appears that the code to set the ‘selected’ attribute for the ‘doctor’ field is leftover from something else and need to be debugged.
  26. You need code for the ‘Implant_s’ field to set the ‘selected’ attribute.
  27. If you use a type=‘date’ field for dates, all modern browsers will present a date-picker.
  28. The type_of_c (type of crown) select/option needs to have its values defined in a database table, use the id as the field value and as the inserted value, and set the ‘selected’ attribute, similar to the other select/option fields.

Lastly, I recommend that you get your code to fully work with ONE form field of each general type, then you can worry about all the code needed for the rest of the fields.

2 Likes
5

Why does your form for a patient congratulate the user on adding a student in kurdish?

6

Here’s a few more points to add to the list -

  1. Only store the user’s id in a session variable to indicate who the logged in user is.
  2. Query on each page request to get the current user’s permissions or any other user information.
  3. Apply htmlentities to any dynamic value when you output it in a html context to help prevent cross site scripting.
7

I want the idea in two fields in two different tables,
I want to reduce the number of Impalnt_n culomun of the forms directly reduce of the column Implant_s of othere table

8

Programming involves defining what inputs you have, what processing you are going to do based on those inputs, and what result you are trying to produce or output.

For any submitted Implant_s and Implant_n values, what are you going to do if the Implant_n value is greater than the section column in the row corresponding to the Implant_s id value? What are you going to do if Implant_n value is less than or equal to the section column in the row corresponding to the Implant_s id value?

Answer these questions in your native language, put the two answers into your code as comments, then attempt to design, write, test, and debug the code and query(ies) needed to accomplish those two statements.

1 Like
9

i put complite source code

10

You must perform this update as one single (atomic) operation or you must lock the table to prevent multiple instances of the code changing values between the SELECT query and the UPDATE query (which was shown in the code in the now deleted reply #3.)

To do this all within one single UPDATE query and check if the value was changed, see the following -

$sql = "UPDATE classes SET section = IF(section>=?,section-?,section) WHERE id=?";
$stmt = $db->prepare($sql);
$stmt->execute([ $Impalnt_n, $Impalnt_n, $Implant_s ]);

if($stmt->rowCount())
{
	// the section value was updated, i.e. section was >= $Impalnt_n
	echo 'update was successful';
}
else
{
	// the section value was unchanged, i.e. section was < $Impalnt_n
	echo 'there was not sufficient quantity to update';
}
1 Like
12

Can you expand on “didn’t work” please?

1 Like
14

What I meant was, what happened that should not have happened, or what did not happen that should have? In what way did the code “not work”?

2 Likes
15

No results appeared in the table

when data added to table showed this > echo ‘there was not sufficient quantity to update’;

16 
<option <?php echo $row['id'] ?>><?php echo $row['level']; ?></option>
                              <?php } ?>

This won’t generate html which will provide a value to the post object. It should be

<option value="<?php echo $row['id'] ?>"><?php echo $row['level']; ?></option>
                              <?php } ?>
1 Like
18

same shing,it does’t working

19

Something’s missing

Have you echo’d out the values of $Impaltn_n and $Implant_s to ensure the values are what you’re expecting?

Have you taken your SQL statement with those values and executed it to ensure it’s working and there’s not a problem like a field name being named incorrectly?

And this looks wrong
doesn’t rowcount() return a number of rows affected?

if($stmt->rowCount())

So shouldn’t that be

if($stmt->rowCount() > 0) // maybe -1, been a while with PHP
1 Like
20

In the now deleted picture (reply #3) of your database table values, you already had a -300 for one of the section row values. Is this the one you are selecting to use? If so, there is NOT a sufficient quantity in that row.

1 Like
22

Is your id column happy to have NULL inserted into it? If it’s an auto-increment unique ID, just leave it out of the query altogether.

1 Like
23

workking now, thanks for helping my bro and other friends

24

Now, if you would just do the things on the given lists, so that your code will be secure in all contexts, provide a good User eXperience (UX), will be simple without a lot of unnecessary typing, and will either work or it will tell you why it doesn’t.

1 Like
25

It might be helpful to others in the future if you could post the solution - how did you fix the problem?