0

i am trying to add to an int and than update the int where the name = $loginuser but i get a 500 error i do not know what i did wrong big change i did something stupid. i also do not know hot to do math in php script also whatever i try i can not update the value in the database.

the code:

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

//database info
$servername = "localhost";
$username = "Transact";
$password = "FaTRwwvJHLPXWtR6";
$dbname = "puntsysteem";

//by user
$loginUser = $_POST["loginUser"];
$transactPoint = $_POST["transactPoint"];

//create Connection
$conn = new mysqli($servername, $username, $password, $dbname);

//check Connection
if($conn->connect_error) {
    die("Connection Failed: " . $conn->connect_error);
}

$stmt = $conn->prepare("UPDATE user SET punten = punten + ? WHERE naam = ?");
$stmt->bind_param("is", $transactPoint, $loginUser);
$stmt->execute();

$stmt->close();
$conn->close();

?>

any suggestions?

kajvanschalk
  • 49
  • 5
  • `$result2` is likely to be an array. If you got a 500 error, check your PHP error log file to find the underlying exception message – ADyson Jan 29 '21 at 13:00
  • A 500 error is a generic error message and covers pretty much every single thing that can go wrong with a PHP script. Check your server error logs to find out the exact error message. – aynber Jan 29 '21 at 13:00
  • But you don't need the first select anyway. Sql can do the addition for you within the UPDATE query – ADyson Jan 29 '21 at 13:01
  • i am using MAMP where can i find those error logs – kajvanschalk Jan 29 '21 at 13:02
  • Also your UPDATE statement is totally wrong. Check examples and documentation. It doesn't have the same syntax as an insert. This is basic SQL knowledge – ADyson Jan 29 '21 at 13:02
  • You php.ini file will record the location of the log file (assuming you've set it up) – ADyson Jan 29 '21 at 13:03
  • https://stackify.com/php-error-logs-guide/ is a good guide to setting up PHP error logging if it isn't done already – ADyson Jan 29 '21 at 13:04
  • And I think you need to read https://dev.mysql.com/doc/refman/8.0/en/update.html ...and study some SQL tutorials in general. – ADyson Jan 29 '21 at 13:04
  • oke i added the missing closing brace but still nothing works. you say i did the UPDATE wrong but i am totally new to it so i do not see what i did wrong – kajvanschalk Jan 29 '21 at 13:06
  • `i do not see what i did wrong`..that's because you haven't spent even 1 minute studying how an UPDATE statement works! If you had, you'd see immediately. We're not here to be a tutorial service...but... – ADyson Jan 29 '21 at 13:06
  • `UPDATE user SET punten = punten + ? WHERE naam = ?` should be the query, anyway. And then `$stmt->bind_param("is", $transactPoint, $loginUser);` would be the parameters. And then you don't need the SELECT at all. – ADyson Jan 29 '21 at 13:07
  • this is a basic UPDATE: $sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2"; but i tried adding prepared staments and than it did not work – kajvanschalk Jan 29 '21 at 13:07
  • `i tried adding prepared staments and than it did not work `... no, prepared statements is not the reason for the problem. The reason is your SQL is completely different and incorrect. Compare `UPDATE SET user (naam, punten) VALUES (?, ?) WHERE naam = ` with what I just wrote, and with the correct MyGuests example you just gave. Surely you can see? I assume maybe you know how an INSERT works because the `(naam, punten) VALUES (?, ?)` bit is from INSERT syntax. – ADyson Jan 29 '21 at 13:08
  • A separate piece of advice: **Never** get your web app to login to the database as root. Root can do whatever it likes, so if there are security errors in the PHP, this leaves your database wide open for hackers. Instead create a separate user account specifically for this application which has only the permissions it actually _needs_ in order to work properly. Don't even use the root account as a shortcut during development or testing, because you need to test your account permissions as well - otherwise when you go live you might have unexpected errors relating to the user account setup. – ADyson Jan 29 '21 at 13:11
  • P.S. Another reason you might not see errors is because you haven't configured mysqli to throw them. See https://stackoverflow.com/a/14578644/5947043 for the super-simple setup of that. – ADyson Jan 29 '21 at 13:12
  • No, it's a problem. Read the last part of my comment again: _Don't even use the root account as a shortcut during development or testing, because you need to test your account permissions as well - otherwise when you go live you might have unexpected errors relating to the user account setup._ And you don't want unexpected errors immediately when you go live. – ADyson Jan 29 '21 at 13:15
  • 1
    Remove this if statement `if ($conn->query($stmt) == TRUE)` – Dharman Jan 29 '21 at 13:16
  • Thanks for the update. Now you've made that change, as I explained earlier you don't need `$sql2 = "SELECT punten FROM user WHERE naam = ?";` or any of the code associated with it. – ADyson Jan 29 '21 at 13:17
  • And yes Dharman is right, `$conn->query($stmt) == TRUE` is gibberish. ->query executes a SQL query. But 1) $stmt isn't a string containing a SQL query, and 2) you've already executed your query on the previous line. If you want to know whether the query updated any rows, use this: https://www.php.net/manual/en/mysqli-stmt.affected-rows.php. If you want to know if the query failed entirely, ->execute will return true/false, but you aren't checking it. Also `New record created successfully` doesn't make any sense when you're doing an update. `Record updated successfully" would be more meaningful – ADyson Jan 29 '21 at 13:19
  • i did forget to change `New record created successfully` this is because i copied the script from another script – kajvanschalk Jan 29 '21 at 13:23
  • i changed it like you did say but i still get the 500 error @ADyson – kajvanschalk Jan 29 '21 at 13:38
  • Ok. And did you actually check for an error message? Did you set up the error logging? Did you set up mysqli to throw errors? We've already told you that 500 is no use as a message on its own. Go and look for the real error. – ADyson Jan 29 '21 at 13:53
  • Anyway you didn't remove everything. What do you think is the purpose of this line now: `$transactPoint = $transactPoint + $result2;`? And where do you think $result2 is coming from? Clearly this isn't necessary anymore because you removed the SELECT which created $result2. And it will throw an error because $result2 is not defined. You need to pay closer attention to your code, and think carefully about what each line is actually for, and use logic to try and work out if it's going to succeed. You might not always get it right, and sometimes you'll get errors, but you should think a bit more. – ADyson Jan 29 '21 at 13:54
  • 1
    no errors anymore sorry for some stupid moments from me. thanks you guys for the help. have a nice day. – kajvanschalk Jan 29 '21 at 20:54

0 Answers0