0

On form post the certain rows need to be deleted or new rows need to be inserted into database (mysql).

What I have:

$toInsert = array() // array of ID's where new rows need to be created because these ID's dont exist in any row.
$toDelete = array() // array of ID's where rows containing this ID need to be deleted.

Delete sql query:

$sql = DELETE FROM table WHERE id IN ' . implode(',',$toDelete);

Query deletes all rows containing this ID. How can I do the same thing but with insert? Certain default values need to be inserted alongside with the ID itself.

Your suggestions or criticism are very much appreciated.

Matas Skaržauskas
  • 11
  • 6
  • Yes, you can do single insert query https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql just prepare it beforehand. And yes, you can issue a single delete query with WHERE id IN (id1, id2, ...) – blahy Aug 17 '20 at 20:31
  • Thank you very much, I was looking for such a post but couldn't find it. – Matas Skaržauskas Aug 17 '20 at 20:39
  • Friendly reminder to watch out when creating SQL queries for user input, if $toDelete is in any way influenced by input in your application, as it is right now it could be used for malicious SQL injection. https://www.w3schools.com/sql/sql_injection.asp – Marc Sances Aug 18 '20 at 06:22

1 Answers1

0

Please remember that you must escape the ids (or make sure you cast to integer). The correct syntax for this would be:

$q_delete_ids = implode(', ', array_map('intval', $toDelete));
$sql = " DELETE FROM table WHERE id IN ($q_delete_ids) ";

A generic function should be developed where you quote any value and to be used with array_map.

e.g. code with an imaginary db object that has an escape() method (like addslashes() but safer for the db engine and avoiding issues with random collations...)

public function quoteSurround($val) {
    return "'" . $this-db->escape($val) . "'";
}

Now, for insert, a common method should be developed e.g. where you pass an array with all columns of db (if you miss some columns then make sure you have default values in mysql table DDL).

In essence you should develop a function that will create and execute an sql string like

INSERT INTO table (col1, col2, col3) VALUES (1, 'test1', '2000-01-01'), (2, 'test2', '2000-02-02'), (3, 'test3', '2000-03-03'), (4, 'test4', '2000-04-04'), (5, 'test5', '2000-05-05');

I hope I can post a gist to github in the following days...

Antonis Adamakos
  • 68
  • 1
  • 3