CODEWITHSUNDEEP
phppdo
rits
rits

Reputation: 1544

Binding values from arrays?

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
));

Upvotes: 2

Views: 950

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157895

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.

Upvotes: 1

Ali
Ali

Reputation: 3081

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 . ")";

Upvotes: 3

Jeremy Harris
Jeremy Harris

Reputation: 24549

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.

Upvotes: 4

Related Questions