MySQL Query sent through PHP does not work, but works in Workbench

Question

I have a basic insert statement I would like to execute through PHP:

INSERT INTO Ability_to_PermissionLevel (Ability_to_PermissionLevel_Ability_FK, Ability_to_PermissionLevel_PermissionLevel_FK)
VALUES (0, 2)

The PHP code I have to send the query is this:

$query = 'INSERT INTO Ability_to_PermissionLevel (Ability_to_PermissionLevel_Ability_FK, Ability_to_PermissionLevel_PermissionLevel_FK)
          VALUES (:id, :p);';
$statement = $pdo->prepare($query);
$statement->bindValue(":id", $ability);
$statement->bindValue(":p", $p);
$statement->execute();

For some reason, nothing is ever inserted to the database, but it returns no errors. The first thing I thought was that I probably messed up capitalization in some way because I have done that before, but I checked over and over again and have not found any sort of case discrepancies. It properly executes a few queries right before this one in the code, so it should not be anything with improperly setting up a connection. I figured maybe it was because the primary key was not set up even though it is auto-incremented, so I tried this:

INSERT INTO Ability_to_PermissionLevel (Ability_to_PermissionLevel_OID, Ability_to_PermissionLevel_Ability_FK, Ability_to_PermissionLevel_PermissionLevel_FK)
          VALUES (DEFAULT, 0, 2);

Still no luck. I set up a general log for the sake of debugging, and it confirms that it is being sent to the database correctly. This is what is in the general log:

458 Query   INSERT INTO Ability_to_PermissionLevel (Ability_to_PermissionLevel_OID, Ability_to_PermissionLevel_Ability_FK, Ability_to_PermissionLevel_PermissionLevel_FK)
          VALUES (DEFAULT, '0', '2')

When copying and pasting what is in the log into the workbench, it works correctly.

I am stuck as to what to try next. I am assuming it is something simple and obvious that I am missing, but I am just not sure what it is.

EDIT:

Thanks for all the help with error checking. The execute method returned false but error_get_last returned no errors, so I tried the error checking steps in the link provided and found that the error had to do with database constraints that were set up. This did not make sense, because I originally pulled the foreign key value from the tables they are connecting themselves, but I tried a fix anyway, and it completely passes all of the error checking and execute even returns true. Unfortunately I am still having the problem before, because it is not actually inserting anything, even though copying and pasting from the MySQL general log into workbench actually inserts it. Is it something wrong with my fix? Here is the updated query:

$query = "SET @p = (SELECT PermissionLevel_OID
                  FROM PermissionLevels
                  WHERE PermissionLevel_OID = $p);
                  INSERT INTO Ability_to_PermissionLevel (Ability_to_PermissionLevel_Ability_FK, Ability_to_PermissionLevel_PermissionLevel_FK)
                  VALUES ($ability, @p);";

And the log returns:

518 Query   SET @p = (SELECT PermissionLevel_OID
                  FROM PermissionLevels
                  WHERE PermissionLevel_OID = 2);
      518 Query INSERT INTO Ability_to_PermissionLevel (Ability_to_PermissionLevel_Ability_FK, Ability_to_PermissionLevel_PermissionLevel_FK)
                  VALUES (0, @p)

The only constraint problem that the error returned was with the Ability_to_PermissionLevel_PermissionLevel_FK, so that is why only that is pulled again.

I have the pdo preparation and execution in a try/catch now, as well as a test to see if execute returns true or false. It is not catching anything anymore, and execute returns true. Unfortunately, nothing is added to the database. Like I mentioned earlier, I copied and pasted what is in the log into workbench and it worked there. Is there anything else I can do to further error check this?

did you check maybe you are connected to the wrong datatabase — Jurij Jazdanov, May 31 '17 at 13:31
Yeah, It is the correct database. I retrieve data from the same table I attempt to insert into — Michael VanScoyoc, May 31 '17 at 13:34
Tried removing the last semicolon, and still does not work for some reason. Also getting no php errors, and the log does not have any errors. When copying and pasting into Workbench, Workbench gives no errors. Is there any other place that could send an error, maybe I am missing it? — Michael VanScoyoc, May 31 '17 at 13:36
Which is the result of the `execute` method? According to the documentation it should give `TRUE` on success and `FAIL` otherwise. Consider using the `error_get_last` function to check that no error occurs. — Ilario Pierbattista, May 31 '17 at 13:40
see [How to get mysqli error in different environments?](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments) — Masivuye Cokile, May 31 '17 at 13:44
[Error Checking for PDO Prepared Statements](https://stackoverflow.com/questions/15275689/error-checking-for-pdo-prepared-statements) — Masivuye Cokile, May 31 '17 at 13:45
Do you maybe need to explicitly state the data type in `bindValue()`? I see you are trying to insert numbers/integers, but your log shows strings (indicated by `'`)? — chrki, May 31 '17 at 14:00
My reply to all of your comments were too long so I added an edit. Thanks for your help with error checking! — Michael VanScoyoc, May 31 '17 at 14:44

MySQL Query sent through PHP does not work, but works in Workbench

0 Answers0