Argthtjtr

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?

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.

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.