-1

I have created a function to insert data into the MySQL database.

// Inserting in the database
public function addData($table, array $columns, array $values) {
    // Defining columns
    $each_column = null;
    foreach ($columns as $column) {
        $each_column .= '`' . $column . '`, ';
    }
    $column_sentence = substr($each_column, 0, -2);

    // Defining values
    $each_value = null;
    foreach ($values as $value) {
        $each_value .= ':' . $value . ', ';
    }
    $value_sentence = substr($each_value, 0, -2);

    // Go ahead with prepared SQL
    $sql = "INSERT INTO `$table` ($column_sentence) VALUES ($value_sentence)";
    $statement = $this->pdo->prepare($sql);

    foreach (array_combine($columns, $values) as $column => $value) {
        $statement->bindValue(':' . $column, $value);
    }

    // Execute that baby
    $statement->execute();

    $statement->errorCode();
    $statement->errorInfo();
}

This is how I run this function:

$db->addData('test', array('id', 'ip', 'name'), array(24, 123456789, 'Reza'));

My problem is that I get this warning:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

What exactly is wrong with that function?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Reza Saadati
  • 5,018
  • 4
  • 27
  • 64
  • Please check https://stackoverflow.com/questions/14814592/warning-pdostatementexecute-sqlstatehy093-invalid-parameter-number-par?rq=1 – Agam Banga Dec 25 '17 at 00:14
  • 1
    I think you are putting the actual value contents with a colon before them in the VALUES section, but then trying to call them by the names of the columns. If I am reading it correctly. – C Miller Dec 25 '17 at 00:19

1 Answers1

2

you are using the values as instead of the column names in the values area.

    public function addData($table, array $columns, array $values) {
        // Defining columns
        $each_column = null;
        foreach ($columns as $column) {
            $each_column .= '`' . $column . '`, ';
        }
        $column_sentence = substr($each_column, 0, -2);

        // Defining values
        // FIX : put the column names in the VALUES()
        $each_column = null;
        foreach ($columns as $column) {
            $each_column .= ':' . $column . ', ';
        }
        $value_sentence = substr($each_column, 0, -2);

        // rest of code snipped
    }

Before

INSERT INTO `test` (`id`, `ip`, `name`) VALUES (:24, :123456789, :Reza)

After

INSERT INTO `test` (`id`, `ip`, `name`) VALUES (:id, :ip, :name)
ryantxr
  • 4,119
  • 1
  • 11
  • 25