Home Web development How to reduce the quantity of items in stock? – PHP – SitePoint Forums

How to reduce the quantity of items in stock? – PHP – SitePoint Forums

0

For example, there are 20 products in the stock table, when you reduce the number by 5, indicate that there are 15 in the stock table

stoch table

sell table
2022-08-29_191253


You would use a UNION ALL query to calculate the total if needed, getting the SUM() from the stock table, grouped by the item id, minus the SUM() from the sales table, grouped by the item ID. Since you haven’t posted full column information and 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 that you have an element table in which the elements are defined. This produces an item identifier that you would use in any stock or sell records to link the item to its definition.

If the Implant_s form field is the ID of the selected implant system, wouldn’t you use that value to determine which row of the section table to operate on?

Here is a long list of issues and things that can be simplified in the code posted –

  1. A header() instruction does not stop the execution of php code. You need an exit/die statement after the ‘admin’ redirect to prevent all other code from running.
  2. The post method of the processing code should be above the start of the html document, so you can decide what to do on the page based on the result of the form processing code.
  3. Never use the @ error suppressor. Once the form is submitted, except for unchecked checkboxes/radio fields, all form fields will be set and 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.
  4. Do not attempt to test if the submit button is enabled. There are cases where this will not be the case. Instead, you should detect if a post method form has been submitted. If you can have multiple form processing codes on a page, use a hidden field containing a single value to control which form processing code to execute.
  5. Whatever the code for the output() function does, it’s probably incorrect for the input data. Except for the slicing data, so that you can detect if all the space characters have been entered, you should NOT modify the input data before using it.
  6. You need to keep the form data as a set in a php array variable and then operate on the elements of that array variable in the rest of the code.
  7. Once you have made the article #6 on this list you can cut all input data at once using a single line of php code.
  8. After cropping the data, you must validate each input separately, storing user/validation errors in an array using the field name as the index of the array. For example, if Implant_s and Impalnt_n are “mandatory”, you must validate that they contain authorized values. If not, it’s an error and you need to set up messages for the user telling them what’s wrong with the data they submitted.
  9. After all the validation logic completes, if the array containing user/validation errors is empty, use the data from the submitted form.
  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 contains a definition of what form fields are expected, what validation to perform on each, and what treatment to perform. You would then loop over this definition and the user’s general purpose code to validate and process the data.
  11. Often the conditional logic “failure” code is much shorter than the “success” code. If you reverse the tested condition and put the “failed” code first, it’s easier to track what your code is doing.
  12. You need to create sql queries in php variables. This makes debugging easier. You can echo the sql query statement to see what it is. It also separates the sql query syntax from the php code, thus reducing the number of typing errors.
  13. You must list the columns you select in a query. This helps avoid errors and makes your query self-documenting.
  14. Don’t copy variables into other variables for nothing.
  15. If you set the default recovery mode to assoc when you connect to the database, you do not need to specify it in each recovery statement.
  16. If you are using implicit binding, providing an array of data to ->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 don’t provide values ​​for, for example, the id column.
  19. If this INSERT query might result in duplicate data, you need to have error handling for the query to detect if a duplicate error has occurred and set up a message for the user telling them what went wrong with the data he submitted.
  20. After all form processing logic completes, if there are no errors, redirect to the exact same URL of the current page to cause a request to get 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 make the form submit on the same page, just omit the entire action attribute.
  23. If you put the tags around the field they belong to, you can omit the for=’…’ attribute and the corresponding id=’…’ attribute (most of which are missing/don’t match anyway.) Don’t use attributes in your markup only when necessary.
  24. The first one in a list should be a prompt to select one of the choices. The value of this first option must be an empty string. This will force the user to make a choice, it will allow you to validate the input (an empty string will not pass validation) and it will let the ‘required’ attribute work.
  25. It seems that the code to set the ‘selected’ attribute for the ‘doctor’ field is a remnant of something else and needs 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 (crown type) select/option must 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 other select fields /option .

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



1 like

Why does your form for a patient praise the user for adding a Kurdish student?

Here are some more points to add to the list –

  1. Store only the user id in a session variable to indicate who the logged in user is.
  2. Query on each page request for current user permissions or any other user information.
  3. Apply html entities to any dynamic value when outputting it to an html context to avoid cross-site scripting.

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

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

For all Implant_s and Implant_n values ​​submitted, what will you do if the Implant_n value is greater than the section column in the row corresponding to the Implant_s identifier value? What will you do if the Implant_n value is less than or equal to the section column in the row corresponding to the Implant_s identifier value?

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



1 like

I put the complete source code

You must do this update in a single (atomic) operation or you must lock the table to prevent multiple instances of the code from changing values ​​between the SELECT query and the UPDATE query (which was in the code of the now deleted answer #3.)

To do all this in a single UPDATE request and check if the value has been 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

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



1 like

What I meant was what happened that shouldn’t have happened, or what didn’t happen that should have happened? How did the code “didn’t work”?



2 likes

No results appeared in the table

when the data appended to the array said this > echo ‘there was not enough quantity to update’;


                              

This will not generate HTML that will provide a value to the post object. It should be


                              



1 like

even shing, it doesn’t work

something is missing

Have you echoed the values ​​of $Impaltn_n and $Implant_s to make sure the values ​​match what you expect?

Have you taken your SQL statement with these values ​​and run it to make sure it works and there are no issues like a misnamed field name?

And that seems wrong… doesn’t rowcount() return a count of affected rows?

if($stmt->rowCount())

So it shouldn’t be

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



1 like

In the now deleted image (answer #3) of your database table values, you already had a -300 for one of the section row values. Is this the one you choose to use? If so, there is NOT enough in that row.



1 like

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.



1 like

work now, thanks for helping my brother and other friends

Now if you just did the things on the given lists, so that your code is secure in all contexts, provides a good user experience (UX), will be simple without a lot of unnecessary typing, and will work or it will tell you why this it’s not the case.



1 like

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