Binding values from arrays?

Question

So I have this incomplete method to save object in database:

public function save() {
    $variables = get_object_vars($this);
    $attributes = [];

    foreach ($variables as $variable => $value) {
        $attributes[] = $value;
    }

    $variableString = implode(", ", $attributes);

    foreach ($variables as $variable => $value) {
        $attributes[] = ":" . $value;
    }

    $valueString = implode(", ", $attributes);

    $sql = "INSERT INTO products (" . $variableString . ") VALUES (" . $valueString . ")";
    $query = $this->pdo->prepare($sql);
    // ...
}

How do I bind values like this using arrays I already created?

$query->execute(array(
  ':username' => $username,
  ':email' => $email,
  ':password' => $password
));

score 4 · Accepted Answer · answered Dec 27 '16 at 21:16

This is how I would do it:

// This gets an associative array
$variables = get_object_vars($this);

// Init
$columns = [];
$placeholders = [];
$bindings = [];

// Loop through variables and build arrays
foreach ($variables as $column => $value) 
{
   $columns[] = $column;
   $placeholder = ':' . $column;
   $placeholders[] = $placeholder;
   $bindings[$placeholder] = $value;
}

// Create strings
$columnString = implode(',', $columns);
$placeholderString = implode(',', $placeholders);

// Prepare query
$sql = "INSERT INTO products (" . $columnString . ") VALUES (" . $placeholderString . ")";
$query = $this->pdo->prepare($sql);

// Execute query
$query->execute($bindings);

You basically prepare the pieces you need upfront and then pass them through.

I should mention this may be a brittle way of doing this due to the fact that it assumes the attributes on your class are always in your database table. It basically takes one $myModel->non_column = 123; statement somewhere where to push through and break your query.

Seems like you are trying to build your own Active Record implementation perhaps? May want to look at how some of the big players do this, or just use theirs.

I am creating an instance of a Product class and wanted to create a method that saves it to database. — rits, Dec 27 '16 at 21:34
There are lots of good frameworks out there that do all this work for you, unless you are doing it as a learning exercise. My recommendation is Laravel: https://laravel.com — Jeremy Harris, Dec 27 '16 at 21:35
I know. This is a specific task that was given to me so that I understand PHP OOP. Thanks for the answer. — rits, Dec 27 '16 at 21:38
Ah, cool. Then yeah, this will work. Question is, do you understand it? ;-) — Jeremy Harris, Dec 27 '16 at 21:38

Ali · Answer 2 · 2016-12-27T21:40:04.887

Try the following to have a $placeholders and $columnNames to construct your query as well as the $attributes itself to be passed to your $query->execute() method

public function save()
    {
        $variables = get_object_vars($this);
        $attributes = [];

        foreach ($variables as $key => $value) {
            $attributes[':' . $key] = $value;
        }

        $keys = array_keys($attributes);

        $placeholders  = implode(", ", $keys);
        $columnNames = str_replace(':', '', $placeholders );

        $sql = "INSERT INTO products (" . $columnNames . ") VALUES (" . $placeholders . ")";

        $query = $this->pdo->prepare($sql);
        // ...
    }

the other approach would be to construct two arrays, one for placeholders and one for values and then use the array_combine method to to achieve the same $attributes. however this method would lack the column names and you had to make sure your object provides all the tables column in the correct order, i.e. $sql = "INSERT INTO products VALUES (" . $placeholders . ")";

the provided example will do the trick, but i can help it thinking the whole idea of saving your objects in the DB with this method will cause you trouble down the line, interesting idea though! — Ali, Dec 27 '16 at 21:42
Thanks for the answer. This isn't anything that I will develop further. It's kind of a test task. — rits, Dec 27 '16 at 21:44

Your Common Sense · Answer 3 · 2016-12-28T15:43:10.693

1

You have it complete actually. In fact, $variables array is what you're looking for: just send it into execute().

Besides, all the solutions above are too verbose to my taste. To create a couple of simple strings is not that a big deal:

$variables = get_object_vars($this);
$columns   = array_keys($variables);

$columnString      = '`'.implode('`,`', $columns).'`';
$placeholderString = ':'.implode(',:', $columns);

$sql = "INSERT INTO products ($columnString) VALUES ($placeholderString)";
$this->pdo->prepare($sql)->execute($variables);

Note that for the dynamically built query, to use delimiters around identifiers is obligatory, as you never can tell which property turns out to be a mysql keyword.

edited Dec 28 '16 at 15:43

answered Dec 27 '16 at 23:24

Your Common Sense

156,878
40
214
345

Thanks for an answer, I will test it out. – rits Dec 27 '16 at 23:29
So you're still not entirely satisfied. What is it then? – Your Common Sense Dec 27 '16 at 23:32
This just seems shorter, that's all. – rits Dec 27 '16 at 23:36

Binding values from arrays?

3 Answers3

Linked

Related