Php mysql insert query without variables

Question

I've always wrote 2 functions... one for insert and one for edit, for example

ADD:

function add_bank($vars = array()) {
    $sql = "INSERT INTO BANK (Name, Var1, Var2, ...., Var7) VALUES (?, ?, ?, ..., ?)";

    $name = isset($vars[Name]) ? $vars[Name] : "";

    ...

    $var7= isset($vars[Var7]) ? $vars[Var7] : "";

    $rs = $db->prepare($sql);
    $rs->execute(array($name, ..., $var7));
    $id = $db->lastInsertId();
    return $id;
}

EDIT:

function edit_bank($idBank, $vars = array()) {

    $sql = "UPDATE BANK SET ";
    $v = array();   

    if ( isset($vars['Name']) ) {
        $sql .= "Name = ?, ";
        $v[] = $vars['Name'];
    }
    ...
        ..
    if ( isset($vars['Var7']) ) {
        $sql .= "Var7= ?, ";
        $v[] = $vars['Var7'];
    }

    $sql = rtrim($sql, ", ");
    $sql .= " WHERE ID = ?";
    $v[] = $idBank;

    $rs = $db->prepare($sql);
    $rs->execute($v);
}

Now, everytime I need to add new fields to my table, i've always to edit both function...

I'd like to reduce the problem, editing only the edit_bank function and mantain the add_bank function.

I was thinking about a solution like this:

function add_bank($vars = array()) {

    $sql = "INSERT INTO BANK";

    $rs = $db->prepare($sql);
    $rs->execute();
    $id = $db->lastInsertId();

    edit_bank($id, $vars);

    return $id;
}

but it seems the query: $sql = "INSERT INTO BANK"; is not valid. Any solution?

You have to give VALUES to the query. A correct query goes like you have above: INSERT INTO BANK (Name, Var1, Var2, ...., Var7) VALUES (?, ?, ?, ..., ?) — Svenmarim
– Svenmarim, Commented Nov 22, 2018 at 14:54
I wouldn't recommend your new option. Stick with the two methods or switch to an ORM and let that deal with the problems. — Nigel Ren
– Nigel Ren, Commented Nov 22, 2018 at 14:57

You Old Fool · Accepted Answer · 2018-11-22 16:41:14Z

If I'm understanding correctly you'd simply like to insert an empty row, get the new id from auto-increment column and then update it with your desired data. I've personally used this method in some situations and I don't see any problem with it.

You can insert a new row with all default values like this:

INSERT INTO BANK () VALUES();

Or alternately, if ID is an auto increment (which it appears to be) you can do:

INSERT INTO BANK SET ID = NULL;

Note for this to work all of your columns need to either have default values or you need to make sure that STRICT_TRANS_TABLES is off, otherwise you may get an error.

Now you should have a new id and an empty row to edit.

Dekel · Accepted Answer · 2018-11-22 14:58:26Z

0

You might want to look at replace into.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERT Syntax”.

This way you can create a query such as:

$sql = "INSERT INTO BANK (Id, Name, Var1, Var2, ...., Var7) VALUES (?, ?, ?, ..., ?)";
if (NEED_TO_EDIT) {
    $Id = $idBank;
} else {
    $Id = null;
}

$rs = $db->prepare($sql);
...

Note that this is not ansi sql standard.

answered Nov 22, 2018 at 14:58

Dekel

62.9k12 gold badges109 silver badges130 bronze badges

2 Comments

Giuseppe Lodi Rizzini Over a year ago

This is not good: "the old row is deleted before the new row is inserted". The ID need to be the same after the edit. I can't delete the row (maybe the ID is used as FK)

Dekel Over a year ago

For that you can use insert on duplicate update: download.nust.na/pub6/mysql/doc/refman/5.1/en/…

Collectives™ on Stack Overflow

Php mysql insert query without variables

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related