PHP mysqli insert command

Question

I am trying to insert multiple values into MySQL via an array, but It's not working or delivering an error message so I'm not sure where I'm going wrong. Any help would be appreciated.

Here is where I call the function

$testArrayList = array();
          $testArrayList[] = 'Account_idAccount';
          $testArrayList[] = 'firstName';
          $testArrayList[] = 'lastName';
          $testArrayValues = array();
          $testArrayValues[] = $idAccount;
          $testArrayValues[] = $firstName;
          $testArrayValues[] = $lastName;
          $dbManager->insertValues("User", $testArrayList, $testArrayValues);

Now, here is the insertValues funciton being called.

        public function insertValues($table, $cols, $values) {
    foreach ($cols as $col)
        $colString .= $col.',';
    foreach ($values as $value)
    {
        $valueAmount .= '?,';
        $valueType .= 's';
        $valueParam .= $value.",";
    }
    $colString = substr($colString, 0, -1);  
    $valueAmount = substr($valueAmount, 0, -1); 
    $valueParam = substr($valueParam, 0, -1); 

    $mysqli = new mysqli(DBHOST, DBUSER, DBPASSWORD, DBDATABASE);
    $sql = "INSERT INTO $table ($colString) VALUES($valueAmount)";
    /* Prepared statement, stage 1: prepare */
    if (!($stmt = $mysqli->prepare($sql))) {
         echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
    }
    print_r($valueParam);
    /* Prepared statement, stage 2: bind and execute */
    if (!$stmt->bind_param("$valueType", $valueParam)) {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
    }

    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }
    /* explicit close recommended */
    $stmt->close();
    $mysqli->close();
}

I'm getting no errors at all. I just know it's not working, because I don't see my values in the database. I noticed, that before when I would play around with the quotation marks around the ->bindParam function that I would get errors, and the way I have it now I don't get errors, but I also don't have any values in the database. — AlexHeuman, Mar 18 '13 at 22:02
I know that I'm calling the function properly. I know that the proper values are being passed in the arrays. I know my error lies in my insertValues function, but everything looks right to me. I don't know where to start debugging, especially since I don't get an error. — AlexHeuman, Mar 18 '13 at 22:06
bind_param binds variables to a prepared statement as parameters. I do not think you understand what it means. Read the documentation. — Petr, Mar 18 '13 at 22:09
Also, when I bypass the arrays, and just enter straight values it still won't work. This function is a copy of a function that works for single values, but I can't figure out how to make it work for multiple values. — AlexHeuman, Mar 18 '13 at 22:16
@Petr, I know what bind_param means. I'm pretty sure that I'm using it properly as well, am I not? — AlexHeuman, Mar 18 '13 at 22:29

Shoe · Accepted Answer · 2013-03-18T22:43:58.960

There were a bunch of errors there, here's a rewritten version of you function that should work:

public function insertValues($table, array $cols, array $values) {

    $mysqli = new mysqli(DBHOST, DBUSER, DBPASSWORD, DBDATABASE);

    $colString = implode(', ', $cols); // x, x, x
    $valString = implode(', ', array_fill(0, count($values), '?')); // ?, ?, ?

    $sql = "INSERT INTO $table ($colString) VALUES($valString)";
    if (!$stmt = $mysqli->prepare($sql))
         echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;

    foreach ($values as $v)
        if (!$stmt->bind_param('s', $v))
            echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;

    if (!$stmt->execute())
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;

    $stmt->close();
    $mysqli->close();

}

You should as well initialize the mysqli connection once in the constructor instead of for each method:

public function __construct() { 
    $this->mysqli = new mysqli(DBHOST, DBUSER, DBPASSWORD, DBDATABASE);
}

public function __destruct() {
    $this->mysqli->close();
}

Also it's good that you create a proper function to handle those errors, such as:

public function showError($message, object $obj) {
    echo "$message: (" . $obj->errno . ") " . $obj->error;
}

leading to this cleaner version of you function:

public function insertValues($table, $cols, $values) {

    ...

    if (!$stmt = $mysqli->prepare($sql))
         $this->showError("Prepare failed", $mysqli);

    foreach ($values as $v)
        if (!$stmt->bind_param('s', $v))
            $this->showError("Binding parameters failed", $stmt);

    if (!$stmt->execute())
        $this->showError("Execute failed", $stmt);

    ...

}

thanks so much for rewriting my function. Unfortunately it's still not working. I'm wracking my brains on this one. — AlexHeuman, Mar 18 '13 at 22:47
I'm glad I know that now, and thanks for your detailed answer. It also seems that the $values array is getting printed to the screen, but I'm not printing it anywhere. Anyway, thanks for your help. — AlexHeuman, Mar 18 '13 at 22:57
Also, I do initialize the mysqli in the constructor, but for some reason when I call it in a method it doesn't work, so I just call it in each method now. — AlexHeuman, Mar 18 '13 at 22:58
I've just realized that the file containing my insertValue function hasn't been saving this whole time, but every other file has. Now I'm getting this error Binding parameters failed: (0) Binding parameters failed: (0) Binding parameters failed: (0) Execute failed: (2031) No data supplied for parameters in prepared statement — AlexHeuman, Mar 18 '13 at 23:02

Petr · Answer 2 · 2013-03-19T07:08:33.447

I have rewritten the function so you can clearly se why you use bind_param() wrong.

This version is just an example, which works with 2 cols only!

    function insertValues($table, array $cols, array $values) {

        $mysqli = new mysqli('localhost', 'petr', null,'test');

        $colString = implode(', ', $cols); // x, x, x
        $valString = implode(', ', array_fill(0, count($values), '?')); // ?, ?, ?

        $sql = "INSERT INTO $table ($colString) VALUES($valString)";
        if (!$stmt = $mysqli->prepare($sql))
             echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;

        list($a,$b) = $values;
        // params $a and $b must exists during $stmt execution, therefore you can't use foreach with temproray variable
        if (!$stmt->bind_param('ss', $a, $b))
                echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;

        if (!$stmt->execute())
            echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;

        $stmt->close();
        $mysqli->close();

    }

This works:

    insertValues('test',array('firstName','lastName'),array('Jan Amos','Komensky'));

Thanks, I thought I could use a string in bind_param, but I was mistaken. Thanks for your help. — AlexHeuman, Mar 19 '13 at 23:48

PHP mysqli insert command

2 Answers2

Linked