0

So basically I'm trying to make my php script update a few of the values in a db table but for some reason it isn't actually updating. It gives me the success message but I guess that just confirms that the code executed and not that it found a row that matched the WHERE statement to insert the data. to test it I've been using HttpRequester with values set as id = 5, weight = 100, reps = 5 and sets = 5 and as a POST request. There is definitely a row in the table where id = 5 which is what my WHERE statement asks for. Here is the php:

<?php
 if($_SERVER['REQUEST_METHOD']=='POST'){

 $id = $_POST['id'];
 $weight = $_POST['weight'];
 $reps = $_POST['reps'];
 $sets = $_POST['sets'];

 require_once('dbConnect.php');

 $sql = "UPDATE strength_exercises SET previous_weight = '$weight', previous_reps = '$reps', previous_sets = '$sets' WHERE exercise_id = '$id'";

 if(mysqli_query($con,$sql)){
  echo 'Previous weight, sets and reps updated';
 }else{
  echo 'Could not update exercise stats';
 }

 mysqli_close($con);

}

The tables columns are:

exercise_id int(11)
user_id int(11)
exercise_name varchar(100)
previous_weight int(11)
previous_reps int(11)
previous_sets int(11)

When I just execute the following query on the database (which uses the same values as my test should be using) it works fine:

UPDATE strength_exercises SET previous_weight = 100, previous_reps = 5, previous_sets = 5 WHERE exercise_id = 5

Any ideas on what to try would be great

DarkBee
  • 16,592
  • 6
  • 46
  • 58
user3193861
  • 43
  • 6
  • Add an `echo mysqli_error($con)` whithin the else-clause to get some more info. – Peter van der Wal Apr 24 '16 at 15:52
  • 1
    offtopic : Your code is open to SQL injection, please look into prepared statements to prevent this – DarkBee Apr 24 '16 at 15:53
  • (1) you are open to [`sql injection`](http://stackoverflow.com/q/60174/689579). you should at least wrap your `$_POST` values in a `mysqli_real_escape_string()`. (2) since all your columns in the `UPDATE` are an `int`, there is no need to quote the values (although MySQL is smart enough to convert a quoted string/int into an int value) – Sean Apr 24 '16 at 15:56
  • It's not giving an error so there is no info to give, it also doesn't reach the else-clause since the query does execute, it just isn't actually updating for whatever reason. As for preventing sql injection I will be looking into the security side soon but this is for a uni project so the app will never actually be available, so my main focus for now is just getting the functionality working. Thanks for the advice though :) – user3193861 Apr 24 '16 at 16:03
  • @Sean I tried removing the quotes just to see if that would help but if I do that then it gives me a syntax error "near ' previous_reps = , previous_sets = WHERE exercise_id =' at line 1". Maybe this could help with identifying the problem? – user3193861 Apr 24 '16 at 16:07
  • That syntax error tells you the problem. It tells you that your `$_POST` values are empty. Relook at your HttpRequester, or do a `var_dump($_POST)` to see what is actually there. – Sean Apr 24 '16 at 16:11
  • Ok so I pressed the "Parameter Body" button on HttpRequester and it changed the content type from "applications/json" to "application/x-www-form-urlencoded" and now it works fine. I have no idea why but thanks for the help! haha – user3193861 Apr 24 '16 at 16:18

0 Answers0