PHP Prepared Statement not executing correctly

Question

I have been staring at a problem for far to long now.

I have a PHP file that is preparing an insert query, binding the params, and executing the query (Simple).

QUERY

$stmt = $db->prepare("SELECT insert_user(?, ?, ?, ?, ?, ?)");

For some reason the $stmt object returned by execute returns -1 for the affected rows. If I alter the code to do just an insert query with the values I wanted to bind, hard coded instead, the query works just fine.

HARD CODED QUERY

$db->query("SELECT insert_user('Test', 'Account', '[email protected]', 'testAccount9', '1980-01-01', 1)");

Something is going wrong in the bind_param section. I have errors turned on and am checking for mysqli errors too, but both are returning no errors.

PHP FILE

... $postdata = file_get_contents("php://input"); if (isset($postdata) && !empty($postdata)) { $request = json_decode($postdata); } if ( validate_string($request->fname) && validate_string($request->lname) && validate_integer(intval($request->gender))) { $stmt = $db->prepare("SELECT insert_user(?, ?, ?, ?, ?, ?)"); if ($stmt) { $stmt->bind_param("ssssis", $first_name, $last_name, $email, $password, $gender, $dob); $first_name = $request->fname; $last_name = request->lname; $email = $request->email; $password = password_hash($request->password, PASSWORD_BCRYPT); $gender = intval($request->gender); $dob = $request->dob; $stmt->execute(); if ($stmt->affected_rows > 0) { echo toJson('success'); } else { echo toJson('fail'); } } else { echo toJson("Prepare failed: (" . $db->errno . ") " . $db->error); } } else { echo toJson('fail - passed data not valid'); } ...

I feel the error must be simple at this point, but I have tried at least 23,432 different things to no success.

SELECT != INSERT. See dev.mysql.com/doc/refman/8.0/en/insert.html — aynber
– aynber, Commented Sep 5, 2019 at 18:40
Check the syntax of an insert query - stackoverflow.com/questions/60174/… - INSERT INTO table (column) VALUES (:column) — Nigel Ren
– Nigel Ren, Commented Sep 5, 2019 at 18:42
Do you have mysqli error reporting turned on? phpdelusions.net/mysqli/error_reporting — waterloomatt
– waterloomatt, Commented Sep 5, 2019 at 18:46
@aynber are you referring to the SELECT insert_user() portion? — Chris
– Chris, Commented Sep 5, 2019 at 18:47

Shir Gans · Accepted Answer · 2019-09-05 18:43:26Z

2

My guess the problem is here "ssssis" . The fifth should be string, the last integer. Maybe this should work?

answered Sep 5, 2019 at 18:43

Shir Gans

2,0374 gold badges24 silver badges40 bronze badges

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

5 Comments

Nigel Ren Over a year ago

If you look at the value put into the 5th bind (gender) the value is set using $gender = intval($request->gender);

Chris Over a year ago

@Shir Gans I am passing a integer value for gender in my DB. Date is a string converted to a DATE by the insert_user function.

waterloomatt Over a year ago

Right, but OP's HARD CODED QUERY, they use gender 1 as the final argument.

Chris Over a year ago

OMG, that is it! What a waste of time for you guys sorry!

Chris Over a year ago

The insert_function in my DB takes DOB as the 5th param and gender as the last.

Collectives™ on Stack Overflow

PHP Prepared Statement not executing correctly

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related