Something is wrong with my INSERT INTO statement

[Technetium]

First, huge thanks for the help on my previous question. You guys solved my problem.

Here is a different one. I have a prepared statement with INSERT INTO. Only, it’s not creating the new row. I’ve tried using try/catch to isolate the error, but the catch isn’t giving me anything. I’ve also been using fwrite to track the value of things as the code progresses, and I know roughly where the code just stops running, I just can’t figure out why. The third fwrite line runs smoothly, but as far as I can tell, nothing happens after that. The new row does not get written, but the fwrite line within the catch does not execute either.

Here is the function. I had trouble getting code to indent properly last question I posted, so I took a screenshot of the function this time instead:

One thing that’s not an error above: The column Number is actually a string, that’s why it has bindValue instead of BindParam and no PDO::PARAM_INT. It’s a string because it may be a number or it may be of the form “3d6+3” or something like that.

Here is the result of all the fwrite, which is exactly the data I expect it to produce:
0, 0, 60, 2d6
New Index = 1.
PDOStatement Object
(
[queryString] => INSERT INTO treasure (Index, MonsterID, ItemID, Frequency, Number) VALUES (:index, :monsterid, :itemid, :frequency, :number)
)

Finally, here’s a screen grab of the phpMyAdmin panel showing the table with column names and data, just to eliminate any chance that I’ve mis-typed a column name somewhere. The one row of data in this table I entered manually through phpMyAdmin, so that I could have data to use when testing a different function to display the table contents.

[phdr]

Did you set the PDO error mode to exceptions when you made the connection? If not, there’s nothing to catch, because nothing will get thrown. The try/catch logic will just get skipped over. Also, do you have php’s error_reporting set to E_ALL and display_errors set to on (in the php.ini on your system) so that php would help you by reporting and displaying all the errors it detects. One of the points in the last thread was to let php catch and handle the exceptions, in most cases, so that you don’t have to clutter up your code with error handling logic or debugging statements that you have to remove later.

Next, do NOT select existing data in order produce an incremental index in a database table. This is not ‘atomic’/concurrent safe. Instead define the column as an auto-increment integer primary index and let the database manage the generation of the value.

Posting a picture of your code is next to worthless, since we cannot copy/paste it for any testing or for quoting parts of in a reply.

[Technetium]

Thank you. I looked into everything you mentioned. I googled about setting the PDO error mode and was able to add the line to set that properly. The two lines in php.ini were already at the values you suggested. After adding the PDO error mode, I was able to fwrite the error caught. It said:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Index, MonsterID, ItemID, Frequency, Number) VALUES (1, ‘0’, ‘0’, ‘60’, ‘2d6’)’ at line 1

What I noticed here is that the value for Index is an integer and the other values are all strings. In the table structure, the first four columns are supposed to be integers and the last is supposed to be a string. I’m not sure if that is the problem. I can try it with converting the middle 3 using intval so that the types match.

I had to look up how to set auto-increment in phpmyadmin. When I check the box for “A I” for the Index Column, it gives me an error message saying “#1075 - incorrect table definition; there can only be one auto column and it must be defined as a key.” I didn’t have any other column set to auto-increment. And when I tried to set it as Primary key, it gave another error saying it couldn’t do that either. So I deleted the table and rebuilt it with the Index set to Primary and Auto-increment. I re-added the data for the one line that was in there before, leaving Index blank, and it seemed to work, although it set the Index at 1 rather than 0 which seems odd to me. I have to change the code a bit now that the index is auto-incrementing, I’ll see if the problem is still there.

And sorry about the picture. I thought the code I posted in the other thread looked unreadable with the way it was not formatting the indents.

EDIT: It works now. Thanks again for your help!

[chorn]

although it set the Index at 1 rather than 0 which seems odd to me.

Database indexes start at 1. (For whatever reason)

[phdr]

A zero value is treated as a boolean false, so it causes problems when used in logic statements.