Am new to bindValue and PDO - syntax issue

PHP
1

Hey,
I’ve installed a new PW authentication script and have it all working except I can’t figure out how to send additional new paramenters to the database using bindValues and PDO.

The code executes fine, without generating errors anyway, but nothing is inserted in the DB with the new code (inserts fine in the original code).

I did var_dump(get_object_vars($query_new_user_insert)); and get these results. The first is from the original and the second, again, is with my two new values.


array(1) { ["queryString"]=> string(239) "INSERT INTO users (user_name, user_password_hash, user_email, user_activation_hash, user_registration_ip, user_registration_datetime) VALUES(:user_name, :user_password_hash, :user_email, :user_activation_hash, :user_registration_ip, now())" }

array(1) { ["queryString"]=> string(290) "INSERT INTO users (user_name, user_password_hash, user_email, user_activation_hash, user_registration_ip, wdgts_lnk_num, wdgts_ID, user_registration_datetime) VALUES(:user_name, :user_password_hash, :user_email, :user_activation_hash, :wdgts_lnk_num, :wdgts_ID, :user_registration_ip,now())" }

The first block of code below is the original that works and is minus the two new parameters I’m trying to install (i.e. wdgts_lnk_num, wdgts_ID).
The second block executes without errors but won’t get any data at all into the db



/*               $query_new_user_insert = $this->db_connection->prepare('INSERT INTO users (user_name, user_password_hash, user_email, user_activation_hash, user_registration_ip, user_registration_datetime) VALUES(:user_name, :user_password_hash, :user_email, :user_activation_hash, :user_registration_ip, now())');
                $query_new_user_insert->bindValue(':user_name', $user_name, PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':user_password_hash', $user_password_hash, PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':user_email', $user_email, PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':user_activation_hash', $user_activation_hash, PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':user_registration_ip', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
                $query_new_user_insert->execute();
*/
        $query_new_user_insert = $this->db_connection->prepare('INSERT INTO users (user_name, user_password_hash, user_email, user_activation_hash, user_registration_ip, wdgts_lnk_num, wdgts_ID, user_registration_datetime) VALUES(:user_name, :user_password_hash, :user_email, :user_activation_hash, :wdgts_lnk_num, :wdgts_ID, :user_registration_ip,now())');
                $query_new_user_insert->bindValue(':user_name', $user_name, PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':user_password_hash', $user_password_hash, PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':user_email', $user_email, PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':user_activation_hash', $user_activation_hash, PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':user_registration_ip', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
                $query_new_user_insert->bindValue(':wdgts_lnk_num', $wdgts_lnk_num, PDO::PARAM_INT);
                $query_new_user_insert->bindValue(':wdgts_ID', $wdgts_ID, PDO::PARAM_STR);
                $query_new_user_insert->execute();

The PDO PARAM types are correct as $wdgts_ID is varchar in the DB

I’ve beating this (more like it’s beating me) for hours now so any help would be appreciated.

Thanks

2

It’s probably the PDO::PARAM_INT that’s messing things up. Try running your query without defining type on that line.

$query_new_user_insert->bindValue(':wdgts_lnk_num', $wdgts_lnk_num);
3

Thanks, Drummin but nothing changed. It still didn’t post to the database using
$query_new_user_insert->bindValue(‘:wdgts_lnk_num’, $wdgts_lnk_num);
instead of
$query_new_user_insert->bindValue(‘:wdgts_lnk_num’, $wdgts_lnk_num, PDO::PARAM_INT);
and
$query_new_user_insert->bindValue(‘:wdgts_ID’, $wdgts_ID);
instead of
$query_new_user_insert->bindValue(‘:wdgts_ID’, $wdgts_ID, PDO::PARAM_STR);

It atill doesn’t report any errors either. How can I get it to report the values or to report an error message for debugging?

4

Ahhhhh so focused on your NEW fields I didn’t see you were missing one of the original fields. :user_registration_ip,

$query_new_user_insert = $this->db_connection->prepare("INSERT INTO users
(user_name, user_password_hash, user_email, user_activation_hash, user_registration_ip, wdgts_lnk_num, wdgts_ID, user_registration_datetime)
VALUES (:user_name, :user_password_hash, :user_email, :user_activation_hash, :user_registration_ip, :wdgts_lnk_num, :wdgts_ID, :user_registration_ip,now())");
$query_new_user_insert->bindValue(":user_name", $user_name, PDO::PARAM_STR);
$query_new_user_insert->bindValue(":user_password_hash", $user_password_hash, PDO::PARAM_STR);
$query_new_user_insert->bindValue(":user_email", $user_email, PDO::PARAM_STR);
$query_new_user_insert->bindValue(":user_activation_hash", $user_activation_hash, PDO::PARAM_STR);
$query_new_user_insert->bindValue(":user_registration_ip", $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
$query_new_user_insert->bindValue(":wdgts_lnk_num", $wdgts_lnk_num, PDO::PARAM_INT);
$query_new_user_insert->bindValue(":wdgts_ID", $wdgts_ID, PDO::PARAM_STR);
$query_new_user_insert->execute();
5

I wasn’t seeing it. I copied the sql statement of both the old and the new, placed them one below the other to match them up and everything seemed the same except for the two new insertions (i.e. $wdgts_lnk_num and $wdgts_ID).

But that did inspire me to “divide and conquer” so I tried inserting just one new value at a time into the insert statement. I was able to get wdgts_lnk_num to insert but not wdgts_ID? Tried some changes to the columns config in mysql and I finally created a complete new column named wdgts_id (with “ID” now lower case). I changed name in the code and it worked, I then changed it back to the capitalized version. I seemed to remember getting another odd error but finally got it working too. HMMMM, really strange behavior … got me thinking mysql was the problem so I inserted the original code I posted here in the forum and it too now works?

What that means for anyone reading this later is that while dropping the PDO::PARAM_STR was worth a shot it wasn’t likely the problem.

Thanks for the help Drummin. It wasn’t the cure but the encouragement kept me diggin in a really weird problem.