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
– AlexHeuman, Commented Mar 18, 2013 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
– AlexHeuman, Commented Mar 18, 2013 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
– Petr, Commented Mar 18, 2013 at 22:09

sh03 · Accepted Answer · 2013-03-18 22:43:58Z

1

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

    ...

}

edited Mar 18, 2013 at 22:43

answered Mar 18, 2013 at 22:38

sh03

76.5k39 gold badges177 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AlexHeuman Over a year ago

thanks so much for rewriting my function. Unfortunately it's still not working. I'm wracking my brains on this one.

AlexHeuman Over a year ago

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 Over a year ago

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 Over a year ago

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

Petr · Accepted Answer · 2013-03-19 07:08:33Z

0

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

edited Mar 19, 2013 at 7:08

answered Mar 19, 2013 at 6:56

Petr

1,19911 silver badges21 bronze badges

1 Comment

AlexHeuman Over a year ago

Thanks, I thought I could use a string in bind_param, but I was mistaken. Thanks for your help.

Collectives™ on Stack Overflow

PHP mysqli insert command

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related