Updating MySQL database with PHP

Question

Basically I am trying to use PHP to update MySQL database and I am testing it with an HTML form.

I intend to use this in an android app so that is where the values will be taken from but currently I am just testing with a HTML form to test the PHP code. When I am testing with the HTML form the appropriate data is not being updated currently.

What is wrong with my code that causes this?

PHP code:

/*
* Following code will create a new product row
* All player details are read from HTTP Post Request
*/

// array for JSON response
$response = array();

// check for required fields
if (isset($_POST['PlayerID']) && isset($_POST['Score']) && isset($_POST['LastHolePlayed'])&&     
isset($_POST['Overall'])) {

$playerid = $_POST['PlayerID'];
$score = $_POST['Score'];
$lastholeplayed = $_POST['LastHolePlayed'];
$overall = $_POST['Overall'];

// include db connect class
require('db_connection.php');



// mysql inserting a new row
$result = mysql_query("UPDATE `week1` SET Score = `$score`, LastHolePlayed = `$lastholeplayed`, 

Overall` = $overall` WHERE PlayerID = `$playerid`");


// check if row inserted or not
if ($result) {
    // successfully inserted into database
    $response["success"] = 1;
    $response["message"] = "Player successfully added.";

    // echoing JSON response
    echo json_encode($response);
} else {
    // failed to insert row
    $response["success"] = 0;
    $response["message"] = "An error occurred.";

    // echoing JSON response
    echo json_encode($response);
}
} else {
// required field is missing
$response["success"] = 0;
$response["message"] = "Required field(s) is missing";

// echoing JSON response
echo json_encode($response);
}

html code:

<form action="http://localhost/realdeal/updateplayer.php" method="POST">
PlayerID <input type="text" id='PlayerID' name='PlayerID'><br/><br/>
Score <input type="text" id='Score' name='Score'><br/><br/>
LastHolePlayed <input type="text" id='LastHolePlayed' name='LastHolePlayed'><br/><br/>
Overall <input type="text" id='Overall' name='Overall'><br/><br/>

    &nbsp;  <input type="submit" value="submit">

</form>

I believe so each input value is to be representative of a column in mysql database correct? — dGray
– dGray, Commented Apr 24, 2013 at 20:57

Tony · Accepted Answer · 2013-04-24 20:45:47Z

2

change your query to:

$result = mysql_query("UPDATE `week1` SET `Score` = '$score', `LastHolePlayed` = '$lastholeplayed', `Overall` = '$overall' WHERE `PlayerID` = '$playerid'");

answered Apr 24, 2013 at 20:45

Tony

7514 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dGray Over a year ago

Thanks I changed it to this but the database is still not being updated . I am getting the output - {"success":0,"message":"An error occurred."}

George Cummins · Accepted Answer · 2013-04-24 21:00:49Z

0

Your query delimiters need to be corrected:

$result = mysql_query("UPDATE `week1` SET Score = '$score', `LastHolePlayed` = '$lastholeplayed', `Overall` = '$overall' WHERE `PlayerID` = '$playerid'");

Notice the backticks (`) around column and table names and single quotes (') around the values.

Also, when you are debugging a query, always check for MySQL errors:

$result mysql_query(...) or die("Query failed: " . mysql_error() );

Finally, you should know that your query leaves you open to SQL injection attacks. Always clean your input data before including it in a query.

answered Apr 24, 2013 at 21:00

George Cummins

29k8 gold badges74 silver badges91 bronze badges

5 Comments

dGray Over a year ago

Thanks for your advice I included the debugging query and it is saying that I am getting an error - '` WHERE PlayerID = 6' at line 1

George Cummins Over a year ago

@user2243017 You are missing a single-quote before the value of 6 ('$overall')

dGray Over a year ago

Thanks that solved that problem though, I think the statement is not doing what I want, I am trying to to update the row where PlayerID is equal to the PlayerID that has been entered where as it seems to be using the column names here.

George Cummins Over a year ago

Check the value of $playerid and confirm that it matches a row in your table. Perhaps the input data is not what you expect. Also, try echoing your query so that you can run it manually for debugging purposes.

dGray Over a year ago

Thanks for your help it was to do with having the $overall which refers to a column instead of '$overall' which refers to a row

Gaurav Raj · Accepted Answer · 2013-04-24 21:04:35Z

0

Your sql statement is wrong. You can write as stated above or you can directly write the statement without any apostrophe symbol as - $result = mysql_query("UPDATE week1 SET Score=$score, LastHolePlayed=$lastholeplayed, Overall=$overall WHERE PlayerID=$playerid");

Moreover, can you explain what do you mean by "appropriate data is not being updated". It would be more clear if you give/state the error you are getting.

answered Apr 24, 2013 at 21:04

Gaurav Raj

7086 silver badges21 bronze badges

Collectives™ on Stack Overflow

Updating MySQL database with PHP

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related