Updating mysql with php

In this statement, other columns are also updated with respective new values.

updating mysql with php-69updating mysql with php-42updating mysql with php-68

Data storing in to database enables us to keep the information safe and secure, but what if we wish to update it. For this you must have a database in My SQL with the information stored in it.

In this blog post we will show you how to update previously stored information in database using PHP.

My SQL UPDATE using NULL My SQL UPDATE command can be used to update a column value to NULL by setting column_name = NULL, where column_name is the name of the column to be updated.

The following My SQL statement will update pub_lang column with NULL if purch_price is more than 50.

Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.

The following My SQL statement will update receive_qty, pub_lang, and receive_dt columns with new values 20, Hindi and 2008-07-10 if purch_price is more than 50.Code: FROM book_mast; -------- ------------------------------------ --------- ---------- | book_id| book_name | pub_lang|book_price| -------- ------------------------------------ --------- ---------- | BK001 | Introduction to Electrodynamics | English | 85.00| | BK002 | Understanding of Steel Construction| English | 105.50| | BK003 | Guide to Networking | Hindi | 200.00| | BK004 | Transfer of Heat and Mass | English | 250.00| | BK005 | Conceptual Physics | NULL | 145.00| | BK006 | Fundamentals of Heat | German | 112.00| | BK007 | Advanced 3d Graphics | Hindi | 56.00| | BK008 | Human Anatomy | German | 50.50| | BK009 | Mental Health Nursing | English | 145.00| | BK010 | Fundamentals of Thermodynamics | English | 225.00| | BK011 | The Experimental Analysis of Cat | French | 95.00| | BK012 | The Nature of World | English | 88.00| | BK013 | Environment a Sustainable Future | German | 100.00| | BK014 | Concepts in Health | NULL | 180.00| | BK015 | Anatomy & Physiology | Hindi | 135.00| | BK016 | Networks and Telecommunications | French | 45.00| -------- ------------------------------------ --------- ---------- 16 rows in set (0.00 sec) and My SQL FROM purchase; --------- ---------- ------------- ------------ | book_id | pub_lang | purch_price | total_cost | --------- ---------- ------------- ------------ | BK001 | English | 75.00 | 1125.00 | | BK004 | English | 55.00 | 440.00 | | BK005 | NULL | 20.00 | 400.00 | | BK004 | English | 35.00 | 525.00 | | BK001 | English | 25.00 | 200.00 | | BK003 | Hindi | 45.00 | 900.00 | --------- ---------- ------------- ------------ 6 rows in set (0.02 sec) If we want to update the book_price of the table book_mast by an increment of 5% and also update the purch_price and total_cost of puchase table by an increment of 5%, and this increment will affect only those rows in both book_mast and purchase table, which publishing language is English and book_id matching in both the tables, we can write the following code - UPDATE book_mast,purchase SET book_mast.book_price=book_mast.book_price (book_mast.book_price*.05), purchase.purch_price=purchase.purch_price (purchase.purch_price*.05), purchase.total_cost=receive_qty*(purchase.purch_price (purchase.purch_price*.05)) WHERE book_mast.book_id=purchase.book_id AND purchase.pub_lang="English"; FROM book_mast; -------- ------------------------------------ --------- ---------- | book_id| book_name | pub_lang|book_price| -------- ------------------------------------ --------- ---------- | BK001 | Introduction to Electrodynamics | English | 89.25| | BK002 | Understanding of Steel Construction| English | 105.50| | BK003 | Guide to Networking | Hindi | 200.00| | BK004 | Transfer of Heat and Mass | English | 262.50| | BK005 | Conceptual Physics | NULL | 145.00| | BK006 | Fundamentals of Heat | German | 112.00| | BK007 | Advanced 3d Graphics | Hindi | 56.00| | BK008 | Human Anatomy | German | 50.50| | BK009 | Mental Health Nursing | English | 145.00| | BK010 | Fundamentals of Thermodynamics | English | 225.00| | BK011 | The Experimental Analysis of Cat | French | 95.00| | BK012 | The Nature of World | English | 88.00| | BK013 | Environment a Sustainable Future | German | 100.00| | BK014 | Concepts in Health | NULL | 180.00| | BK015 | Anatomy & Physiology | Hindi | 135.00| | BK016 | Networks and Telecommunications | French | 45.00| -------- ------------------------------------ --------- ---------- 16 rows in set (0.01 sec) and My SQL FROM purchase; --------- ---------- ------------- ------------ | book_id | pub_lang | purch_price | total_cost | --------- ---------- ------------- ------------ | BK001 | English | 78.75 | 1181.25 | | BK004 | English | 57.75 | 462.00 | | BK005 | NULL | 20.00 | 400.00 | | BK004 | English | 36.75 | 551.25 | | BK001 | English | 26.25 | 210.00 | | BK003 | Hindi | 45.00 | 900.00 | --------- ---------- ------------- ------------ 6 rows in set (0.08 sec) My SQL: Update with Join Statement Sample tables Problem If we want to update the aval1of table11 with the bval1 of table12 against the following condition - 1).the id of table11 and table13 must be matched, and 2).You can run UPDATE statements in My SQL command-line, in a GUI tool or with mysqli_query() in a PHP script.For this article, we are going to use `employee` table mentioned below.Result sets listed in this article (except for amending values section) have been generated in that way and by executing SELECT statements (SELECT * FROM `employee`) in command-line.

Tags: , ,