Need Prepared Statements Help

[punk_runner]

I have a general dbInsert class method that accepts three arguments: table, fields, and values - the last two each as an array. I am using a PDO connection and prepared statements to sanitize everything. I am having a problem with the bindParam() function accepting the parameter ID's though... here's my code and the result I am getting, any advice?

 

My arguments:

 

$this->_table   =  "testdata";
$this->_fields  =  array('product_id', 'store_id', 'description', 'price', 'colors', 'sizes');
$this->_values  =  array("20002157", "2005", "Kids polo shirt", "12.59", "White", "Large");

 

My object:

 

$_crud = new Crud();
$_makeCrud = $_crud->dbInsert($this->_table, $this->_fields, $this->_values);

 

My class:

 

public function dbInsert($table, $fields, $values) {

      $_table = $table;
      $_fields = implode(", ", $fields);

      // Create and format the list of insert values
      $_values = "";  

      // replaces values with "?" placeholders
      foreach ($values as $value) {
            $_values .= "?" . ", ";
      }

      // trims off last comma and space
      $_values = substr($_values, 0, -2);

      // checks database connection
      if (isset($this->_dbh)) {

            // Create the SQL Query  
    $this->_sql = 'INSERT INTO ' . $_table . ' (' . $_fields . ') VALUES (' . $_values . ')';

            // Build the query transaction
            $this->_dbh->beginTransaction();

            // Build the prepared statement
            $this->_stmt = $this->_dbh->prepare($this->_sql);

            // Bind the parameters to their properties
            foreach ($values as $key => $val) {
                  
                  // starts $key at 1
                  $key = (int) $key + 1;
                  $this->_stmt->bindParam($key, $val);
               }

            // Execute the query
            $this->_stmt->execute();

            $this->_affectedRows = $this->_stmt->rowCount();

      }		
}

 

What my new table row should look like:

 

product_id     store_id      description                        price       colors      sizes
----------------------------------------------------------------------------------------------
20002157       2005          kids polo shirt                    12.59        white      large

 

 

But this is what my new row DOES look like:

 

product_id     store_id      description                        price       colors      sizes
----------------------------------------------------------------------------------------------
Large              0         Large                               0.00        Large      Large

 

 

So it's taking the value of that last $value and inserting it in all the fields, the store_id and price are 0 and 0.00 because of their numeric type...

 

I assume it is a syntax error, missing quotes somewhere, but where?

[mikosiko]

did you "echo" $_fields and $_values  what echo shows?

[punk_runner]

Echo:

 

Table: testdata

Fields: product_id, store_id, description, price, colors, sizes

Values: ?, ?, ?, ?, ?, ?

[punk_runner]

If I echo the $key $value pairs in the foreach loop I get this:

 

foreach ($values as $key => $val) {
      $key = (int) $key + 1;
      echo $key . " " . $val . "<br />";
}

 

 

1 20002157

2 2005

3 Kids polo shirt

4 12.59

5 White

6 Large

 

 

So I am at a loss LOL...

[punk_runner]

Here is the echo for $this->_sql:

 

INSERT INTO testdata (product_id, store_id, description, price, colors, sizes) VALUES (?, ?, ?, ?, ?, ?)

[mikosiko]

no tested in my side... but try

foreach ($values as $key => $val) {
      $key = (int) $key + 1;
      $val = "'" .  $val . "'";
....
}

 

or using " instead of '

[punk_runner]

no tested in my side... but try

foreach ($values as $key => $val) {
      $key = (int) $key + 1;
      $val = "'" .  $val . "'";
....
}

 

or using " instead of '

 

 

Nope, but it was worth a try. All that does is add quotes to the value in the database like this.

 

product_id     store_id      description                        price       colors      sizes
----------------------------------------------------------------------------------------------
'Large'              0        'Large'                            0.00       'Large'     'Large'

[punk_runner]

What I am trying to avoid is doing more than one insert. I could loop over the who process for each value but that wastes resources.

[mikosiko]

DELETED ... just realize that you are using PDO...

 

here is something:

http://www.php.net/manual/en/pdostatement.bindparam.php

 

one of the comments there:

This works ($val by reference):
<?php
foreach ($params as $key => &$val) {
    $sth->bindParam($key, $val);
}
?>

This will fail ($val by value, because bindParam needs &$variable):
<?php
foreach ($params as $key => $val) {
    $sth->bindParam($key, $val);
}
?>

[punk_runner]

wait a second.... why are you passing the $key in the bind_param?  you are not supposed to pass the type of the param?

 

like

$stmt->bind_param('sssd', $code, $language, $official, $percent);

 

http://php.net/manual/en/mysqli-stmt.bind-param.php

 

Discard this... just realize that you are using PDO

 

 

because I used "?" placeholders and they need to be numbered 1, 2, 3, 4, 5, 6 and the array will naturally start at 0, so I did $key + 1 so it starts at 1...

 

If I were to manually write the bindParams they would look like this:

 

$this->_stmt->bindParam(1, $val);

$this->_stmt->bindParam(2, $val);

$this->_stmt->bindParam(3, $val);

$this->_stmt->bindParam(4, $val);

$this->_stmt->bindParam(5, $val);

$this->_stmt->bindParam(6, $val);

 

but for some reason it is overwriting the $value to the last value for all of them.

 

 

[mikosiko]

yup... I saw the PDO part later... see my previous post ^^^ modified 

[punk_runner]

SOLVED!!!

 

http://docs.php.net/manual/en/pdostatement.bindvalue.php

 

I switched to bindValue instead of bindParam...

 

That's because bindParam works by binding to a variable, and I'm re-using the variable ($value) for multiple values. bindValue seems to bind to individual values. Not sure how though LOL...