CODEWITHSUNDEEP
phpmysqlsql
codeispoetry
codeispoetry

Reputation: 373

php mysql Insert, delete, or update

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!

Upvotes: 0

Views: 1122

Answers (3)

marklark
marklark

Reputation: 897

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.

Upvotes: 2

mzulch
mzulch

Reputation: 1539

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.

Upvotes: 1

John
John

Reputation: 13720

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.

Upvotes: 1

Related Questions