[FIXED] how do I correctly use mysqli_stmt::bindParam()

Issue

I am having difficulty understanding how to properly use bindParam();

I have been following the details at the following URL: http://php.net/manual/en/pdo.prepared-statements.php which show the following as one of their examples:

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

This example is obviously incomplete, however, it shows enough of the details that I should be able reproduce the same results.

I have lifted the code below out of my main program and put it into it's own file to see if I can isolate the issue, so far, it has. I have replicated a snipped of my input data to recreate the same conditions I am getting with the real data, and well, I got that right because I am getting the same error.

I have been getting the error message: Call to undefined method mysqli_stmt::bindparam() which leads me to believe that something is wrong stmt. So, I have put some debugging code in to see what is inside stmt. It appears to be an object, so, it's defined, there are no SQL errors thrown up to that point to suggest an SQL syntax error, everything connects, and seems to function properly, and the object appears to me defined, however, the minute it goes to use it, it's undefined.

I am at a loss, as I followed the example best I could. It seems bindParam() uses references when it sets things up, so I created the variables ahead of time so it has something to work with.

There is something about this that I do not understand and the documentation is not explaining enough for me to get it.

The following is the test script I am using, it is the complete file, minus some obvious stuff.

<?php
$servername = "localhost";
$username   = "-----";
$password   = "--------";
$dbname     = "-----";
$EN["Data"]["Episode"][1]["id"] = "1";
$EN["Data"]["Episode"][1]["seasonid"] = "14";
$EN["Data"]["Episode"][1]["seriesid"] = "143";
$EN["Data"]["Episode"][1]["SeasonNumber"] = "1";
$EN["Data"]["Episode"][1]["EpisodeName"] = "1";
$EN["Data"]["Episode"][2]["id"] = "2";
$EN["Data"]["Episode"][2]["seasonid"] = "14";
$EN["Data"]["Episode"][2]["seriesid"] = "143";
$EN["Data"]["Episode"][2]["SeasonNumber"] = "1";
$EN["Data"]["Episode"][2]["EpisodeName"] = "2";

echo "<pre>";
print_r($EN);
echo "</pre>";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
if (! $conn->set_charset("utf8mb4")) {
    die("Error loading character set utf8: " . $conn->error);
}
$stmt = $conn->prepare( "INSERT INTO TVEpisodes 
    (id,  seasonid,  seriesid,  SeasonNumber,  EpisodeName)
VALUES (?, ?, ?, ?, ?)" );

if ($stmt == FALSE) {    /*<--- didn't fail here. */
    echo "Error: " . $sql . "<br>" . $conn->error;
    exit();  // should not happen at this point, mabye...
}
echo "<pre>";
var_dump($stmt);         /*<--- both show I have data.*/
print_r($stmt); 
echo "</pre>";

$episode_id = " ";
$episode_seasonid = " ";
$episode_seriesid = " ";
$episode_SeasonNumber = " ";
$episode_EpisodeName = " ";

$stmt->bindParam(1, $episode_id);  /*<------ fails here.*/
echo "<pre>";
var_dump($stmt);
print_r($stmt); 
echo "</pre>";
$stmt->bindParam(2, $episode_seasonid);
$stmt->bindParam(3, $episode_seriesid);
$stmt->bindParam(4, $episode_SeasonNumber);
$stmt->bindParam(5, $episode_EpisodeName);
foreach ($EN["Data"]["Episode"] as &$episode) {
    echo "<pre>->>>";
    print_r($episode);
    echo "<<<<- </pre>";
    $episode_id = $episode["id"];
    $episode_seasonid = $episode["seasonid"];
    $episode_seriesid = $episode["seriesid"];
    $episode_SeasonNumber = $episode["SeasonNumber"];
    $episode_EpisodeName = $episode["EpisodeName"];
    if ($stmt->execute() != TRUE) {
        echo "Error: " . $sql . "<br>" . $conn->error;
        exit();  // should not happen at this point, mabye...
    }
}
?>

This is the output page showing the values being spit out.

Array
(
    [Data] => Array
        (
            [Episode] => Array
                (
                    [1] => Array
                        (
                            [id] => 1
                            [seasonid] => 14
                            [seriesid] => 143
                            [SeasonNumber] => 1
                            [EpisodeName] => 1
                        )

                    [2] => Array
                        (
                            [id] => 2
                            [seasonid] => 14
                            [seriesid] => 143
                            [SeasonNumber] => 1
                            [EpisodeName] => 2
                        )

                )

        )

)
object(mysqli_stmt)[2]
  public 'affected_rows' => null
  public 'insert_id' => null
  public 'num_rows' => null
  public 'param_count' => null
  public 'field_count' => null
  public 'errno' => null
  public 'error' => null
  public 'error_list' => null
  public 'sqlstate' => null
  public 'id' => null
mysqli_stmt Object
(
    [affected_rows] => 0
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 5
    [field_count] => 0
    [errno] => 0
    [error] => 
    [error_list] => Array
        (
        )

    [sqlstate] => 00000
    [id] => 1
)

( ! ) Fatal error: Call to undefined method
 mysqli_stmt::bindparam() in /home/-long-path-/test.php on line 45

Solution

First of all the link you gave at the first paragraph belongs to PDO. But in the title you said mysqli and in the code you create mysqli.

So you need to read the documentation here:

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

$stmt->bind_param('ssss', $episode_seasonid,$episode_seriesid,$episode_SeasonNumber,$episode_EpisodeName); 

Answered By - Alex

Answer Checked By - Timothy Miller (PHPFixing Admin)