Having problems inserting and updating tables in a database with prepared statements

Question

Very new to PHP5 and have some problems still. I figured out how to Select with prepared statements now trying to insert/update my code is as follows

function input_lab_results($name, $image, $descrip) {
 $query = "INSERT INTO pat_table (pat_name, pat_image, pat_descrip, pat_doctor, pat_resident, pat_create, pat_modify) VALUES (?, ?, ?, ?, ?, ?, ?)";

 if($stmt = $this->conn->prepare($query)){
  $stmt->bind_param('sssiidd', $name, $image, $descrip, 0, 0, date("Ymd"), date("Ymd"));
  $stmt->execute();
  die();
 } else{
  die($this->conn->error);
 }
}

The error I am getting is

Fatal error: Cannot pass parameter 4 by reference html/classes/mySQL.php on line 43

Any help/references would be appreciated. Thank you.

Answer 1

What is the value of your '$descrip' variable?

Answer 2

I changed it to

function input_lab_results($name, $image, $descrip) {
 $query = "INSERT INTO pat_table (pat_name, pat_image, pat_descrip, pat_doctor, pat_resident, pat_create, pat_modify) VALUES (?, ?, ?, 0, 0, ?, ?)";

 if($stmt = $this->conn->prepare($query)){
  $stmt->bind_param('sssdd', $name, $image, $descrip, date("Ymd"), date("Ymd"));
  $stmt->execute();
  die();
 } else{
  die($this->conn->error);
 }
}

Basically I chanted where it was reading the ?? for the integer to 0 in the query I didn't bind it.

Answer 3

it's not $descrip that's the problem; it's the 0s (parameters 4 & 5). the solution is to pass variables rather than integers:

 `$query = "INSERT INTO pat_table (pat_name, pat_image, pat_descrip, pat_doctor, pat_resident, pat_create, pat_modify) VALUES (?, ?, ?, ?, ?, ?, ?)";`

    $pat_doctor = 0;
    $pat_resident = 0;

    if($stmt = $this->conn->prepare($query)){
            $stmt->bind_param('sssiidd', $name, $image, $descrip, $pat_doctor, $pat_resident, date("Ymd"), date("Ymd"));`

evidently mysqli_bind_param wants its arguments as references so it looks for where they're stored in memory rather than copying their values. this makes sense as some of the things you'd like to bind to the sql statement, such as that image, are probably big enough that you would rather not have excess copies running around. literals, string or otherwise, are not accessible by reference. see: http://us.php.net/references

i do not suggest hard coding the 0s into the sql statement, as it unnecessarily confuses your code.

let me suggest PDO, by the way. its syntax is much saner.

Answer 4

Don't use bind_param - It's a very unintuitive and error-prone interface, which is mostly a legacy from the underlying C-api. Use bind_value or better yet - pass an array of values as an argument to PDOStatement->execute. Eg.:

$query = "INSERT INTO pat_table (pat_name, pat_image, pat_descrip, pat_doctor, pat_resident, pat_create, pat_modify) VALUES (?, ?, ?, 0, 0, ?, ?)";
if ($stmt = $this->conn->prepare($query)) {
  $stmt->execute(array($name, $image, $descrip, date("Ymd"), date("Ymd")));
  die();
} else {
  die($this->conn->error);
}

Answer 5

Everyone is mentioning using PDO, do you guys have any good tutorials or examples of this please?