Update certain fields in a mysql table if an input value is equal to a value in the table

Question

I am currently attempting to create a dashboard for a personal trainer where they can update client records. I have a mySQL database and I am using PHP as the scripting language.

What I want to do: Be able to update client information via HTML input boxes. (Which I have already created). The first being username - which should correspond to a username in the mySQL database. Then the information in the next three input boxes should be inserted into the correct fields in the database.

The Problem: I currently cannot get the SQL statement to work correctly as the Client username is not recognized. This is the error message I am currently receiving :

Error: 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 ') WHERE Client_username = JSmith' at line 1

JSMith is a valid username in the database.

Below is the PHP I am attempting to use:

//insert

$value1 = $_POST['height1'];
$value2 = $_POST['weight1'];
$value3 = $_POST['bodyfat1'];
$value4 = $_POST['username'];

        $sql = "UPDATE client SET Height='$value1', Weight='$value2', Body_fat='$value3') WHERE Client_username = $value4";

if (!mysqli_query($con,$sql))
  {
    die('Error: ' . mysqli_error($con));
  }

mysqli_close($con);

 ?>

My connection etc is working just fine.

If ayone could help me out that would be great!

SQL told you what's wrong. Remove the bracket `'$value3')` <= right there. Then possibly quote `$value4` if it's a string which seems most likely. `$_POST['username']` <= — Funk Forty Niner, Feb 11 '15 at 19:28
remove both bracket in `Body_fat='$value3')` and your question too — u_mulder, Feb 11 '15 at 19:28
As you can see from reading the error you have a `)` that doesn't belong there. — pajaja, Feb 11 '15 at 19:28
^ I agree with that ^ (u_mulder's) just delete this. Answered in comments. — Funk Forty Niner, Feb 11 '15 at 19:29
I agree with the delete, though the OP may need to reference this question in the near future when they're dealing with a SQL Injection attack. — Ed Gibbs, Feb 11 '15 at 19:34
We've quit warning people @EdGibbs, seems they don't listen (or read) anyhow. — Jay Blanchard, Feb 11 '15 at 19:37
@JayBlanchard I'm surprised I only got [one up](http://stackoverflow.com/questions/28462836/update-certain-fields-in-a-mysql-table-if-an-input-value-is-equal-to-a-value-in#comment45251411_28462836). Unless username is called `123`. Aren't we all considered "numbers" in the end? ;-) — Funk Forty Niner, Feb 11 '15 at 19:38
...nor my rear end neither @JayBlanchard Least, I sure hope not. — Funk Forty Niner, Feb 11 '15 at 19:39
@JayBlanchard So... I take it you don't want to *put one in?* - I'm not talking about no *golf ball* neither ;-) One more vote and she's done for Cap'n. I rather not, but I could... yet, fearing it may open up a can of proverbial worms, and OP coming back to say: *"It doesn't work."* Or, do you also fear worms as much as a beakless bird? — Funk Forty Niner, Feb 11 '15 at 19:53
Nah, numbers only mess up my end. ;-) I'm already one of four (and wish I was related to seven of nine, if you know what I mean). Hopefully some other soul will come along and hammer the final nail @Fred-ii- — Jay Blanchard, Feb 11 '15 at 20:13
@JayBlanchard OP's not responding, so who knows whether or not any of this is going through, or if what's been said fixed it or not. Shooting golf balls at midnight and aiming for holes we can't see, doesn't tell us if it landed in the hole or not ;-) — Funk Forty Niner, Feb 11 '15 at 20:20
I'm not going to tell you what I have done in dangerous waters @Fred-ii- because I've been there several times, often without a suitable raft or thesaurus. — Jay Blanchard, Feb 11 '15 at 20:40

score 2 · Accepted Answer · edited May 23 '17 at 11:43

Here's the deal.

The first error is coming from the bracket just before your where clause:

$sql = "UPDATE client SET Height='$value1', Weight='$value2', Body_fat='$value3') WHERE...
                                                                                ^ there

Remove it.

MySQL was telling you:

...right syntax to use near ') WHERE
                             ^

Then, the "username" which is a string, needs to be treated as such, therefore wrapping the $value4 variable in your where clause with quotes.

WHERE Client_username = '$value4'

However, I need to point out that your present code is open to SQL injection. Use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

An insight:

Make sure that your form contains name attributes to go with your POSTs, and contain no typos, and that letter-case matches.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

Update certain fields in a mysql table if an input value is equal to a value in the table

1 Answers1