PDO problem

Does anyone see a reason why my fields don't update in my database?

$db2 = new PDO($dsn, $dbUserName, $dbPassword);
   $sqlStatement2 = $db2->prepare("UPDATE `users` SET `verified ` = ?, `regStep` = ? WHERE `ID` = ?");
   $sqlStatement2->execute([true, 1, $idCheck]);
   header('Location: ../registration/regfinal.php');
   exit;

PHP:

 

I just solved this one. This was the last error last night before I had to pull the plug and go to sleep, lol. For anyone wondering how to do the update statement my syntax is correct, but the last WHERE value has to be hard coded or have a value interpolated... Something like.

"UPDATE `users` SET `verified ` = ?, `regStep` = ? WHERE `ID` = $idCheck"

PHP:

the execute function wont put the variable in place from the provided array.

 

While I don't believe that hardcoding the value didn't fix the issue in your case, it's certainly not the correct way to do it.

My best guess is that the "true" in your execute() call shouldn't be there. Try replacing it with a 1 instead.

Also, remember to add error checks to your code. Don't always assume all queries execute as expected.

$db2 = new PDO($dsn, $dbUserName, $dbPassword, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

try {
    $sqlStatement2 = $db2->prepare("
        UPDATE `users`
        SET
            `verified ` = ?,
            `regStep` = ?
        WHERE `ID` = ?
    ");
    $sqlStatement2->execute([1, 1, $idCheck]);
}
catch (\PDOException $e) {
    echo 'Error: ', $e->getMessage();
    exit;
}

header('Location: ../registration/regfinal.php');
exit;

PHP:

This should tell you why the query failed (if it still does).

 

Thanks, I thought that was wrong, but since it went I figured it was right. I'll re-write this code above. That will likely show me what's wrong with another sql statement I'm having trouble with. This one is a lot harder for me to catch.

The weird thing is the MySQL statement is written right. When I run it in phpmyadmin it goes fine. I just swap the question marks out for true and put in the number if the ID. However from localhost the values will just not go. I even echoed them out to see true across the board, and the id echoes out with a value.

As a secondary note the sql query you just helped my with is directly above this one, but I don't think that's it. I already swapped them with each other while scratching my head for the solution, lol. I'm gonna set up that try catch on that solution. I'm supposed to incorporate those for all queries to suppress possible errors? That was another thing I was gonna ask later.

- I know this looks wrong but since the query runs in phpmyadmin it's gotta be a php error or something...

$db2 = new PDO($dsn, $dbUserName, $dbPassword);
     $sqlStatement2 = $db2->prepare("UPDATE `regtags` SET `gamesKids` = ?, `gamesMild` = ?, `gamesMature` = ?, `gamesPsychotic` = ?,  `moviesKids` = ?, `moviesMild` = ?, `moviesMature` = ?, `moviesPsychotic` = ?, `booksKids` = ?, `booksMild` = ?, `booksMature` = ?, `booksPsychotic` = ?, `comicsKids` = ?, `comicsMild` = ?, `comicsMature` = ?, `comicsPsychotic` = ?, `systemsNintendo` = ?, `systemsSony` = ?, `systemsXbox` = ?, `systemsOther` = ?, `computersHp` = ?, `computersAcer` = ?, `computersDell` = ?, `computersMac` = ?, `genreSciFi` = ?, `genreFantasy` = ?, `genreRealistic` = ?, `genreOriginal` = ? WHERE `ID` = ?");
     $sqlStatement2->execute([$gamesKids, $gamesMild, $gamesMature, $gamesPsychotic, $moviesKids, $moviesMild, $moviesMature, $moviesPsychotic, $booksKids, $booksMild, $booksMature, $booksPsychotic, $comicsKids, $comicsMild, $comicsMature, $comicsPsychotic, $systemsNintendo, $systemsSony, $systemsXbox, $systemsOther, $computersHp, $computersAcer, $computersDell, $computersMac, $genreSciFi, $genreFantasy, $genreRealistic, $genreOriginal, $idCheck]);
     

PHP:

 

I think you helped me solve my other problem. How come I have to send 1's instead of true. In phpmyadmin the value is true... Is there some kind of difference between php booleans and sql boolean/tinyint? Maybe I have to send 1's for my second statement I can't figure out? Now that I'm typing this I'm not sure... It doesn't sound right..ugh.. this is confusing, lol. Any way I'm off. I'll likely get any messages Monday.


EDIT - nope just tried 1's in some of the second statement. Didn't work. How come 1 in the place of verified in the first statement you helped me with worked and that's supposed to go in as a boolean value? This is very confusing..

 

execute() passes everything to SQL as strings. If you are adding numbers or comparing booleans, execute() won't work properly.

I recommend using $statement->bindValue(). You can specify if the SQL requires a boolean or a number instead of a string.

 

As a general rule, yes. Queries can fail for different reasons, and you should notify the user if something went wrong, rather than continuing as if it didn't. Although, avoid displaying direct MySQL messages. Log them somewhere for you to review later on.

Try to catch possible exceptions that PDO throws. These will help you find out what's wrong.

The thing is, there are no true boolean types in MySQL. tinyint(1) is as the name suggests, a small integer. Although, I'm sure PHP is smart enough to convert those correctly, I can't say I've ever tried it, and it was the only thing I could find that looked "wrong" in your code. It was just a wild guess.

Furthermore, try writing lines of code that don't exceed 80/120 characters. Otherwise they'll be hard to read, force you to scroll horizontally, and are difficult to debug. I'd write it like this:

$sqlStatement2 = $db2->prepare("
    UPDATE `regtags`
    SET
        `gamesKids` = ?,
        `gamesMild` = ?,
        `gamesMature` = ?,
        `gamesPsychotic` = ?,
        ....
        `genreRealistic` = ?,
        `genreOriginal` = ?
    WHERE `ID` = ?
");

PHP:

 

Just as a side note - you don't have to encapsulate / quote table-names in SQL queries - depending on the language, that can actually cause errors as well. Actually, as long as you're not using restricted words as table-names (which you shouldn't be doing anyway), there's no need to quote table-names either.

 

Thanks for all the info. I took the time to wrap all my database calls in all scripts in try catch blocks, so I could properly handle errors without displaying any information. After messing around I finally got that last call to the regTags table to go, but there's still an error. No matter what is checked all values go in as 1's lol. I've alerted out the check boxed variables in javascript, and echoed them out in php. They seem to be messing up in the php end.

I have all my variables laid out like this.

     $gamesKids = 0;
     $gamesMild = 0;
     $gamesMature = 0;
     $gamesPsychotic   = 0;

PHP:

and add the values based on $_POST[]

     if($_POST['gamesKids'] == true){$gamesKids = 1; }else{$gamesKids = false;}
     if($_POST['gamesMild'] == true){$gamesMild = 1; }else{$gamesMild = false;}
     if($_POST['gamesMature'] == true){$gamesMature = 1; }else{$gamesMature = false;}

PHP:

and then run the query above. I had the if statements written out another way. using question mark notation, but I thought that I had the syntax wrong, but this method didn't work either. Is there anything stupid I'm missing about these if statements? lol

 

okay,

I see one mistake in the if else statements that I hadn't noticed before. It shouldn't be else false. It should be else 0 here. There's probably some other dumb mistake I'll find. The problem will be easier to isolate now, lol... The things I miss.