bungeebones — 2013-10-28T14:33:02-04:00 — #1
I just updated my visitor login to a script that uses PDO (which I am just learning). I have to send some additional parameters to the database when the user registers which I finally was able to do but only after an exhausting wrestling match with a MYSQL table's column name that has some capital letters in it (I.e. wdgts_ID). After struggling to get the data to input into the db I finally realized it was because of the caps in the name.
I have a tentative work-around but it will involve a lot of reworking of code because the only way I can get the data I need into the db was to change the name of the table to all lower case.
Here's the code if anyone has a better way around this...
$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, 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_INT);
michael_morris1 — 2013-10-28T16:50:12-04:00 — #2
MySQL identifiers aren't case sensitive. PHP array elements are (which is further muddled by the fact that most labels in PHP aren't case sensitive).
And, for more fun, fun, fun, MySQL will remember the case you used for the label even if it doesn't itself care.
So example time - you name a table in a database "widgets_ID". When it finds its way to a php array it will be $row['widgets_ID']. Which is a separate entry from $row['widgets_id'] as far as PHP is concerned.
Does PDO respect this or have a hand in it? I dunno. I've always used lower case table identifiers for this very reason - I don't like being confused. It wouldn't surprise me if this were the case - the SQL standard is case insensitive, but it wouldn't surprise me if some rogue implementation was not - and since PDO is tasks with being as widely compatible as possible..
bungeebones — 2013-10-28T17:30:54-04:00 — #3
I attempted to get this into smaller pieces. I got very unexpected results right off the bat when I changed the first part of the query.
$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,
I changed wdgts_id to wdgts_ID. Since there is a column in the db with each name but the data wasn't entered into the upper case column as defined in the INSERT but rather the data was saved to the lower case column? That shouldn't happen should it? I mean I told it (mysql) to put the data in the column named with the capital ID and it's not?
One good benefit of that little experiment is that it actually inserted data whereas when I run that code with all the "ID" instances as caps it won't even post.
bungeebones — 2013-10-28T18:25:40-04:00 — #4
Another experiment - I renamed the mysql table column wdgts_id to something totally different thinking maybe mysql was confusing wdgt_id from wdgt_ID. Now, running the exact same insert code as in my previous post that did successfully save the data but to the wrong (i.e. lower case) column, it won't save the data at all even though the INSERT code is naming it exactly as it is.
Ok, now I really feel like a dweeb - the column name had a blank space in front of it. Thanks for letting me vent
cpricelandscapes — 2013-10-29T03:26:39-04:00 — #5
Actually I don't know, how to write php codes and all.