Insert into Table associative array

[Joak]

Hi guys

 

A mobile application send data in Json format, so I want to insert the data into Mysql table, when I try to insert I have gotten the error "PHP Warning: mysqli::prepare() expects exactly 1 parameter, 3 given", my table only has 2 columns, "id" and "name" column.

 

The array has this data

(
[0] => ('id', '2')
[1] => ('name', 'Jhon')
)

The function to parse and insert data ...

function redeem() {
    // Check for required parameters
        $json = file_get_contents('php://input');
        $obj = json_decode($json,true);
        // Assumes $obj == array(0 => $assocArray, 1 => $assocArray)
        foreach($obj as $index => $assocArray) {
            // Assumes $assocArray == array(0 => array('id' => '2'), 1 => array('name' => 'John'))
            $stmt = $this->db->prepare('INSERT INTO prueba (id,nombre) VALUES (%d,%s)',$assocArray[0]['id'],$assocArray[1]['name']) or die(mysqli_error($this->db));
            $stmt->execute();
            
        }    
        
    }

Edited June 6, 2014 by Maq

[ginerjm]

True. You have 3 args in your call to prepare. So why is that? Check the manual and you'll see what the proper format is.

 

http://us2.php.net/manual/en/pdo.prepare.php

 

PS - What is $this in your Redeem function? You don't have an object in there to make your call to db->prepare with.

Edited June 6, 2014 by ginerjm

[Ch0cu3r]

You're using mysqli_prepare incorrectly,

 

With mysqli_prepare you only define the query, the values being replaced by placeholders. It them returns a statement object ($stmt). With the $stmt object you'd call the bind_param() method to bind the values to the placeholders. Finally you'd call mysqli_execute to execute the query.

 

Example code

// prepare the query
$stmt = $this->db->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');

$assocArray = array('id' => '', 'name' => ''); // define $assocArrat var first

// bind the values to the placeholders, describing their data types
$stmt->bind_param('is', intval($assocArray['id']), $assocArray['name']);

// loop througght he json objects to insert into mysql
foreach($obj as $assocArray) {
    $stmt->execute(); // execute the query
}

[Joak]

 

You're using mysqli_prepare incorrectly,

 

With mysqli_prepare you only define the query, the values being replaced by placeholders. It them returns a statement object ($stmt). With the $stmt object you'd call the bind_param() method to bind the values to the placeholders. Finally you'd call mysqli_execute to execute the query.

 

Example code

// prepare the query
$stmt = $this->db->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');

$assocArray = array('id' => '', 'name' => ''); // define $assocArrat var first

// bind the values to the placeholders, describing their data types
$stmt->bind_param('is', intval($assocArray['id']), $assocArray['name']);

// loop througght he json objects to insert into mysql
foreach($obj as $assocArray) {
    $stmt->execute(); // execute the query
}

 

Ch0cu3r, thanks for you comment. I fix it, I have not any error message, but how can I do the commit?

$stmt = $this->db->commit();

[Joak]

Ch0cu3r, thanks for you comment. I fix it, I have not any error message, but how can I do the commit?

$stmt = $this->db->commit();

 

I can do the commit ..... but the table has the number 0 has id, and null in name column

[mac_gyver]

because bind_parm() uses references to variables, i was only able to get this to work as follows -

$stmt = $mysqli->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');
$stmt->bind_param('is',$id,$name);

foreach($obj as $assocArray) {
    $id = $assocArray[0]['id'];
    $name = $assocArray[1]['name'];
    $stmt->execute(); // execute the query
}

[Joak]

 

because bind_parm() uses references to variables, i was only able to get this to work as follows -

$stmt = $mysqli->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');
$stmt->bind_param('is',$id,$name);

foreach($obj as $assocArray) {
    $id = $assocArray[0]['id'];
    $name = $assocArray[1]['name'];
    $stmt->execute(); // execute the query
}

Thanks Mac_gyver, but I have gotten a new message error just the first line inside foreach ..... PHP Fatal error:  Cannot use string offset as an array

[mac_gyver]

that error would mean that the input data you have shown in this thread isn't what the actual data is.

 

what does the following show for the contents of $obj -

echo '<pre>',print_r($obj,true),'</pre>';

[Joak]

 

that error would mean that the input data you have shown in this thread isn't what the actual data is.

 

what does the following show for the contents of $obj -

echo '<pre>',print_r($obj,true),'</pre>';

This contents of $obj

 

[09-Jun-2014 10:48:55] Array

(

    [id] => 2

    [name] => Jhon

)

[Psycho]

This contents of $obj

 

[09-Jun-2014 10:48:55] Array

(

    [id] => 2

    [name] => Jhon

)

 

Which is very different from what you stated in the first post

 

 

(

    [0] => ('id', '2')

    [1] => ('name', 'Jhon')

)

 

Try this:

 

$stmt = $mysqli->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');
$stmt->bind_param('is',$id,$name);
 
foreach($obj as $assocArray) {
    $id = $assocArray['id'];
    $name = $assocArray['name'];
    $stmt->execute(); // execute the query
}

[Joak]

 

Which is very different from what you stated in the first post

 

 

 

Try this:

$stmt = $mysqli->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');
$stmt->bind_param('is',$id,$name);
 
foreach($obj as $assocArray) {
    $id = $assocArray['id'];
    $name = $assocArray['name'];
    $stmt->execute(); // execute the query
}

Psycho ..... I don't have any error ...... but I can't insert the record

[ginerjm]

Don't you have to bind after you have assigned the values?

[Joak]

Don't you have to bind after you have assigned the values?

After assigned the values .... I have this...The id it's null and the name only has the first letter

[09-Jun-2014 11:57:32]

[09-Jun-2014 11:57:32]

[09-Jun-2014 11:57:32] J

[09-Jun-2014 11:57:32] J

[Psycho]

Don't you have to bind after you have assigned the values?

 

No. I'm no expert in mysqli_ (I use PDO), but, per the manual, once you bind the "variable" to the query you can set/change the variable before executing the query. Here is an excerpt form the manual. Example 2

 

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

/* execute prepared statement */
mysqli_stmt_execute($stmt);

 

@Joak,

 

Are you checking for DB errors?

[ginerjm]

can we see how you changed your code to accomplish this? Kind of silly to just post some output and ask us to debug that...

[Joak]

Psycho ... I'm only checking the errors in error_log file.

 

My code

<?php



class RedeemAPI {
    private $db;
    // Constructor - open DB connection
    function __construct() {
        $this->db = new mysqli('localhost', 'futchoco', 'Futcho1907', 'futchoco_futsoft');
       /* verificar la conexión */
       if (mysqli_connect_errno()) {
           printf("Conexión fallida: %s\n", mysqli_connect_error());
           exit();
      }
        $this->db->autocommit(FALSE);
    }
        
    // Destructor - close DB connection
    function __destruct() {
        $this->db->close();
    }
    // Main method to redeem a code
    function redeem() {
    // Check for required parameters
        $json = file_get_contents('php://input');
        $obj = json_decode($json,true);
        // prepare the query
        $stmt = $this->db->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');
        
        $stmt->bind_param('is',$id,$name);
        
        // loop througght he json objects to insert into mysql
        foreach($obj as $assocArray) {
            //error_log(print_r($obj,true));
            $id = $assocArray['id'];
            $name = $assocArray['name'];
            error_log(print_r($id,true));
            error_log(print_r($name,true));
            $stmt->execute(); // execute the query
            $stmt = $this->db->prepare('COMMIT');
            $stmt->execute();
        }
    }
}


// This is the first thing that gets called when this page is loaded
// Creates a new instance of the RedeemAPI class and calls the redeem method
$api = new RedeemAPI;
$api->redeem();

?>

Edited June 9, 2014 by Joak

[ginerjm]

As Psycho said - you are doing the bind correctly. So:

 

How about doing this to your code:

foreach($obj as $assocArray) 
{
    $id = $assocArray['id'];
    $name = $assocArray['name'];
    echo "id is $id name is $name<br>";
    if (!$stmt->execute()) // execute the query
        echo "Execute failed to run";
}

And see what happens as you loop thru your data.

Note I threw in a check for a failure on the execute. One should do this always,perhaps adding an output of the error message.

[Joak]

I'm getting this values ...

 

 

[09-Jun-2014 12:44:17] Array
(
    [id] => 2
    [name] => Jhon
)

[09-Jun-2014 12:44:17]
[09-Jun-2014 12:44:17]
[09-Jun-2014 12:44:17] Execute failed to run
[09-Jun-2014 12:44:17] Array
(
    [value1] => 2
    [value2] => This was sent from ios to server
)

[09-Jun-2014 12:44:17] J
[09-Jun-2014 12:44:17] J

 

The code of function

 

   function redeem() {
     // Check for required parameters
        $json = file_get_contents('php://input');
        $obj = json_decode($json,true);
        // prepare the query
        $stmt = $this->db->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');
        
        $stmt->bind_param('is',$id,$name);
        
        // loop througght he json objects to insert into mysql
        foreach($obj as $assocArray) {
            error_log(print_r($obj,true));
            $id = $assocArray['id'];
            $name = $assocArray['name'];
            error_log(print_r($id,true));
            error_log(print_r($name,true));
            if (!$stmt->execute()) // execute the query´
            {
               error_log(print_r('Execute failed to run',true));
            }else{
               $stmt = $this->db->prepare('COMMIT');
               $stmt->execute();
            }
        }
    }

 

I'm confused .... why in the first loop, the variables are null? when excute the Insert .,.... I don't have any value in the  variables

[ginerjm]

It appears that you have one set of values which work and the second set is empty which causes the query to fail. Don't know what value1 and value2 are.

 

What are you referring to by "first loop"?

 

Can't you simply echo this stuff instead of logging it to make it a bit easier to read?

[mac_gyver]

$obj isn't an array of arrays. it's an array, i.e. written as code it would be $obj = Array('id' => 2,'name' => 'Jhon');

 

the code in question in this thread would need to be -

$stmt = $mysqli->prepare('INSERT INTO prueba (id,nombre) VALUES (?, ?)');
$stmt->bind_param('is',$obj['id'],$obj['name']);

$stmt->execute(); // execute the query

[Joak]

Yes ..... $obj it's an array, and only has 'id' => 2,'name' => 'Jhon'; the value1 and value2 are the names "original" that I recive bye the mobile application ... I forgot change when paste the result of error_log file  ..... I can't do "echo" ... I have a hostting server.