php mysql Insert, delete, or update

Question

I have two tables:

people:
peopleID (PK)
name

peopleaddress:
addressID (PK)
peopleID (fK)
address
addresstype
...other fields...

A person can have more addresses. I have a form to add a new person (and addresses) and another form to edit info. When I load the edit form, it takes the info from the DB about that person and put them in the fields value="".

Right now, when I submit the edit form, I use 2 sql commands:

$stmt = $conn->prepare("DELETE from peopleaddress WHERE peopleID=?");

and

$stmt = $conn->prepare("INSERT INTO peopleaddress (peopleID, addresstype, active, street.....) VALUES (?, ?, ?, ....)");

It works well, only downside is that addressID changes every update and it tends to grow fast. Am I doing it the right way or there is a way in php or better sql to say:

if new address exists » update

if doensn't exist » insert

if all fields of existing address empty » delete

Thanks for your help!

You are doing it worng. It is pretty straightforward - user adds you should INSERT, on edit you should UPDATE, remove DELETE, view SELECT — kwarunek
– kwarunek, Commented Jan 19, 2016 at 22:56

marklark · Accepted Answer · 2016-01-19 23:08:58Z

2

Here's what you're looking for: "INSERT ... ON DUPLICATE KEY UPDATE"

INSERT INTO peopleaddress (peopleID, addresstype, active, ...) VALUES (1,2,3, ...)
  ON DUPLICATE KEY UPDATE addresstype=2, active=3, ...;

It will find the address if it is already in the database and update it, if it can. Otherwise, it does the insert.

You may have to re-work your '?' substitutions.

answered Jan 19, 2016 at 23:08

marklark

8971 gold badge8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

codeispoetry Over a year ago

Thanks, I only have some trouble with the syntax: let's say I have an entry: addressID=5 peopleID=506 address=555 my street. AddressID is an autoincrement, generated by the sql server. In the edit form I load all these values. If the user chages street, I would like addressID remain = 5. Is the following code ok?

$stmt = $conn->prepare("INSERT INTO peopleaddress (addressID, peopleID, street) VALUES (?,?,?); ON DUPLICATE KEY UPDATE addressID=values(addressID), peopleID=values(peopleID), street=values(street); $stmt -> bind_param("iis",$_POST["addressID"], $_POST["peopleID"]....

marklark Over a year ago

First, you'll need to remove the semi-colon just before 'ON DUPLICATE' in your new code snippet.

marklark Over a year ago

$stmt = $conn->prepare("INSERT INTO peopleaddress (addressID, peopleID, street) VALUES (?,?,?) ON DUPLICATE KEY UPDATE peopleID=?, street=?"); $stmt -> bind_param("iisis",$_POST["addressID"], $_POST["peopleID"], $_POST["street"], $_POST["peopleID"], $_POST["street"]);

codeispoetry Over a year ago

thanks! It's ok for insert or update, but I still need to delete: what is the best way to delete the address for the person? sql (I don't know how) or I have to do an if...else in php?

marklark Over a year ago

if ( inputs_valid() ) { insert(); } else { delete(); }

|

John · Accepted Answer · 2016-01-19 22:59:25Z

If someone adds a new address to an existing account it's okay to have a new row (e.g. id), keeping old addresses isn't necessarily a good or bad thing and having that as a separate table is good (some people may have a home and a business address in example).

If all the fields are missing then your code's logic (PHP?) shouldn't even be getting to the point of any SQL queries.

function admin_members_address_update()
{
 // 1. Create $required array.
 // 2. Check foreach $_POST[array_here].
 // 3. If any of the required $_POST variables aren't set HTTP 403 and end the function.

 //if something wrong, HTTP 403
 else
 {
  ///Normal code here.
 }
}

You should always check for variables you expect to be set just like you always escape (e.g. not trust) client data and have your error reporting set to maximum. A hacker will omit a form field easily with any dev tool and try to find vulnerabilities based on error messages generated by the server.

Always check for failure before you presume success.

//if () {}
//else if () {}
//else if () {}
//else {}

SQL should be handled in a similar fashion:

$query1 = 'SELECT * from table;';
$result1 = mysqli_query($db,$query1);

if ($result1) {}
else {/*SQL error reporting, send __magic_function__ as param with well named functions*/}

That is the very rough of it (don't have access to my actual code) though it should give you something to go on. Comment if you'd like me to update my answer when I have access to my code later.

mzulch · Accepted Answer · 2016-01-19 23:04:20Z

A MySQL unsigned INT (max 4294967295) will support an average of 13 inserts per second, 24x7, for 10 years. That's already probably as many addresses as there are on earth. If you're pushing the limit somehow, a BIGINT will be massively larger and probably never run out in a million years.

Basically, don't worry about using up IDs with auto increment, the decision of whether to DELETE -> INSERT or UPDATE/DELETE/INSERT should be based on whether you need to maintain persistent IDs for individual addresses. Deleting then inserting assigns a new ID, even if it's really the same address, which is undesirable if you want to create a foreign key that references address IDs. If you don't need that you don't need to worry about it, though as a personal preference I would probably incorporate UPDATE.

Collectives™ on Stack Overflow

php mysql Insert, delete, or update

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related