php array to pdo variables

Question

Here's what i'm trying to achieve: i'm learning OOP for school and i have tp make a registration thingy.... it's working but a lot is hardcoded and i'd like to make an universal insert function which works like $class->insert "1, 2, 3", "foo, bar, thing" where it'll insert foo into 1 bar into 2 and thing into 3 here comes the problem: i don't know how to convert my PHP array into SQL variables because i do not know how many items there are in my PHP array. (due to an explode)

here's my code so far:

<?php

 class database
{
static protected $_connection;

public function __construct($host, $dbname, $username, $password)
{
    self::$_connection = new PDO("mysql:host=" . $host . ";dbname=" . $dbname, $username, $password);
    self::$_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return self::$_connection;

}

public function insert($tablename, $columns, $values)
{
    $exploded = explode(", ", $values);
    $valuenumber = NULL;
    $string = NULL;
    $teller = 0;


    $query = self::$_connection->prepare('INSERT INTO gebruikers (:naam) VALUES ');
    foreach ($exploded as $array) {
        $query->bindparam(":value" . $valuenumber, $array);
        $valuenumber++;
    }
    for ($i = $valuenumber; $i > 0; $i--) {
        $string .= ", :value" . $teller;
        $teller++;
        // alles wordt in een string gezet: ":value, :value1, :value2
    }
    $stringarray = explode($string, ", ");
    $query->execute(array(
        'naam' => $teller
    ));
}

}



$client = new database("localhost", "oop", "root", "");
$client->insert("gebruikers", "naam", "test");

I'm sorry if it's a bit messy and for the lack of comments i'm pretty new in this.

Could anyone please help me out a bit? thanks,

g3.

singleton = referring to the self::? according to my teacher when something's static you need to use self:: — JordyvD, Apr 03 '14 at 20:36
My point is that you do not need to use global scope to have a shared DB connection between multiple objects. This might give you some hints: http://stackoverflow.com/a/11369679/727208 — tereško, Apr 03 '14 at 20:37
thanks, i'll check it out, the reason i use a static is because i thought that was the appropriate way of making classes share a connection rather than every class creating it's own instance of the connection — JordyvD, Apr 03 '14 at 20:39
well ... you are partially right: each class should **not** make its own connection, but using global state is the wrong way to go about it. — tereško, Apr 04 '14 at 06:49

score 1 · Accepted Answer · answered Apr 03 '14 at 17:04

public function insert($table, $columns, $values)
{
    // make sure we have the same amount of keys and values
    if(count($columns) !== count($values)){
        return false;
    }

    $query = self::$_connection->prepare('INSERT INTO ' . $table . ' (' . implode(',', $columns) . ') VALUES (:' . implode(',:', $columns) . ')');

    $index = 0;
    foreach($columns as $column){
        $query->bindparam(":" . $column . $values[$index++]);
    }

}

If I were you, I'd get rid of passing $columns and $values separately and just pass a key => pair array like:

array('id' => 1, 'name' => 'new name');

I understand this is a learning process for you're sort of re-inventing the wheel here. Check out libraries like doctrine.

thanks for the answer, i'll try it out ^.^ + i'm not allowed to use any exentions yet but we're going to use Laravel next week — JordyvD, Apr 03 '14 at 20:32
That lowercase "p" in `bindparam` threw me right off. That almost looked like it was a missing underscore; alas it's PDO. I always use an uppercase "P" for clarity myself ;-) — Funk Forty Niner, Apr 04 '14 at 00:54

php array to pdo variables

1 Answers1