Insert/Update Helper Function Using Pdo

Insert/update helper function using PDO

I usually have a class extending PDO, but my class is pretty custom. If I get it cleaned up and tested I will post it at a later time. Here is a solution to your system, however.

function dbSet($fields, &$values) {
    $set = '';
    $values = array();

    foreach ($fields as $field) {
        if (isset($_POST[$field])) {
            $set .= "`$field` = ?,";
            $values[] = $_POST[$field];
        }
    }

    return rtrim($set, ',');
}

$fields = explode(" ","name surname lastname address zip fax phone date");
$_POST['date'] = $_POST['y']."-".$_POST['m']."-"$_POST['d'];

$query  = "UPDATE $table SET ".dbSet($fields, $values).", stamp=NOW() WHERE id=?";
$values[] = $id;

$dbh->prepare($query);
$dbh->execute($values);  

This may not be perfect and could use tweaking. It takes into account that $dbh is setup with a PDO Connection. Pending any minor syntax issues I made, that should work.

EDIT

Really though, I think I would go for Doctrine ORM (or another ORM). As you setup the model and add all the validation there, then it is as simple as:

$table = new Table();
$table->fromArray($_POST);
$table->save();

That should populate the contents easily. This is of course with an ORM, like Doctrine.

UPDATED

Did some minor tweaks to the first code, such as putting isset back and using rtrim over substr. Going to work on providing a mock up of a PDO Extension class just gotta layout the way to do it and do some unit tests to make sure it works.

PHP PDO simple insert or update function

You are assigning $pro to the prepare, not the execute statement.

Having said that, if you are using mysql you can use the insert... on duplicate key update syntax.

insert into $table (field, value) values (:name, :value) on duplicate key update value=:value2

You can't use the same bound param twice, but you can set two bound params to the same value.

Edit: This mysql syntax will only work where a key (primary or another unique) is present and would cause an insert to fail.

How to make a PDO class method for inserting/updating/deleting with an unknown number of parameters in the arg

You need to add a second parameter to your function. Simply an array where all those variables would go. An array by definition can have an arbitrary number of elements, which solves your problem exactly:

public function runQuery($sql, $parameters = []) {
    $stmt = $this->dbc->prepare($sql);
    $stmt->execute($parameters);
    return $stmt;
}

this simple function will run ANY query. You can see the usage example in my article dedicated to PDO helper functions:

// getting the number of rows in the table
$count = $db->runQuery("SELECT count(*) FROM users")->fetchColumn();

// the user data based on email
$user = $db->runQuery("SELECT * FROM users WHERE email=?", [$email])->fetch();

// getting many rows from the table
$data = $db->runQuery("SELECT * FROM users WHERE salary > ?", [$salary])->fetchAll();

// getting the number of affected rows from DELETE/UPDATE/INSERT
$deleted = $db->runQuery("DELETE FROM users WHERE id=?", [$id])->rowCount();

// insert
$db->runQuery("INSERT INTO users VALUES (null, ?,?,?)", [$name, $email, $password]);

// named placeholders are also welcome though I find them a bit too verbose
$db->runQuery("UPDATE users SET name=:name WHERE id=:id", ['id'=>$id, 'name'=>$name]);

// using a sophisticated fetch mode, indexing the returned array by id
$indexed = $db->runQuery("SELECT id, name FROM users")->fetchAll(PDO::FETCH_KEY_PAIR);

As you can see, now your function can be used with any query with any number of parameters

How To Create Function Insert In PHP PDO

You had a typo, that's all. You should have:

':tgl' => $tgl

In your query $params array.

creating a flexible update query with Php and pdo - problems with bindparam

You are lifting this log from the wrong end.

Your approach is potentially insecure yet inflexible at the same time.
What if you need a JOIN based update? What if you need OR in the WHERE (or IN)?

What you really need is a conventional query where only SET statement values have to be generated.
So, you need a helper function to produce such a statement out of data array, returning both correctly formatted SET statement and array with variables to be bound:

$fields = array("name","email");
$sql = "UPDATE users SET ".pdoSet($fields,$values,$data)." WHERE id = :id"
// now we have $values array to be passed into query
$stmt = $dbh->prepare();
$values["id"] = $_POST['id'];
$stmt->execute($values);

With this code you'll be able to make updates for the arbitrary query. And make it safe.

As a further step you will need to start using type-hinted placeholders, to make whole code like this:

$db->query("UPDATE ?n SET ?u WHERE id IN(?a)",$table,$data,$ids);

Getting back to your problem, ONe is right - you need to use bindValue instead of bindParam (as it mentioned in the tag wiki)

INSERT array - PDO

You cannot do that:

• You need to add each variable / field-name and value individually;
• You can only bind values and not table- or field-names.

Table- and field-names you will have to inject directly into your sql so to prevent sql injection problems, you need to check them against a white-list before doing that.

So in your case that would be something like (rough draft):

// assuming all fields have been checked against a whitelist
// also assuming that the array keys of `$habbo_data` do not contain funny stuff like spaces, etc.
$fields = '`' . implode('`, `', array_keys($habbo_data)) . '`';
$fields_data   = ':' . implode(', :', array_keys($habbo_data));

var_dump($fields);
var_dump($fields_data);

global $con;

$query = "INSERT INTO `personnel` ({$fields}) VALUES ({$fields_data})";
$result = $con->prepare($query);
$result->execute($habbo_data);

Note that I am not manually binding the variables any more but sending the associative $habbo_data array directly as a parameter to the execute method, see example #2.

PHP PDO Update prepared statement problem

There are no parentheses in the SET clause of an UPDATE query.

http://dev.mysql.com/doc/refman/5.0/en/update.html

Hence the syntax error when the ( is hit. As long as you're trying to do things the right way with bound parameters, do it in the WHERE clause too!

---

Related Topics