how do you update a row in mysql with php from a user

Question

$pos = $_POST['pos'];
$played = $_POST['played'];
$won = $_POST['won'];
$drawn = $_POST['drawn'];
$lost = $_POST['lost'];
$goalsfor = $_POST['goalsfor'];
$goalsag = $_POST['goalsag'];
$goaldif = $_POST['goalsdif'];
$points = $_POST['points'];



if (mysqli_query($db,"UPDATE division2 SET Pos ='".$pos."', played ='".$played."', won ='".$won."', drawn ='".$drawn."', lost ='".$lost."', goalsfor ='".$goalsfor."', goalsag ='".$goalsag."', goalsdif ='".$goaldif."', points ='".$points."',  WHERE team = Treaty Celtic") === true) {
    echo '<script language="javascript">';
    echo 'alert("Row Successfully Inserted")';
    echo '</script>';
    include("index.html");

This is what i have so far but i keep getting "error inserting row" I want to update the entire row from a user input using $_Post in php.

Possible duplicate of [How do I update MySQL row in PHP?](http://stackoverflow.com/questions/670964/how-do-i-update-mysql-row-in-php) — Vigikaran, Mar 08 '16 at 13:05
just tried WHERE team = 'Treaty Celtic' didn't work thanks @Azeez Kallayi — Walshe007, Mar 08 '16 at 13:10
Please check your column name is Pos or pos. Because all athor fields are small cases.. — Azeez Kallayi, Mar 08 '16 at 13:14
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Mar 08 '16 at 13:29

score 2 · Answer 1 · edited May 23 '17 at 12:31

2

WHERE team = Treaty Celtic is one of the problems here.

That needs to be wrapped in quotes, since we're dealing with a string.

WHERE team = 'Treaty Celtic'

Those 2 syntax errors would have been caught had you checked for errors.

Read up on string literals:

http://dev.mysql.com/doc/en/string-literals.html

and checking for errors would have thrown you a syntax error about it.

http://php.net/manual/en/mysqli.error.php

and a trailing comma in

points ='".$points."', <<< THERE

Remove it.

Also make sure those POST arrays have values with their respective name attributes in the HTML form.

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);

// Then the rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

Add or die(mysqli_error($db)) to mysqli_query().

Or as an else to the if:

else { 
    echo "Error: " . mysqli_error($db); 
}

Seeing now that it's not working for you, make sure that you are using the same MySQL API as your query, being mysqli_.

Different APIs do not intermix.

And as bonus:

Your present code is open to SQL injection. Use mysqli_* with prepared statements, or PDO with prepared statements.

edited May 23 '17 at 12:31

Community

1
1

answered Mar 08 '16 at 13:07

Funk Forty Niner

74,450
15
68
141

I wrapped WHERE team = Treaty Celtic in quotes and error reporting is at the top of my files, still getting error inserting rows. – Walshe007 Mar 08 '16 at 13:16
@Walshe007 I need to know the exact error. – Funk Forty Niner Mar 08 '16 at 13:16
@Walshe007 also, `Treaty Celtic` that must be the exact string with no trailing spaces. `Treaty Celtic` and `Treaty celtic` isn't the same thing. – Funk Forty Niner Mar 08 '16 at 13:18
@Walshe007 add an `else` for the `if` - As in `else { echo "Error: " . mysqli_error($db); }` and tell me what you see, something I have in my answer. I can't help you if I don't know what the exact error is. Same for error reporting, if any. – Funk Forty Niner Mar 08 '16 at 13:28
@Walshe007 make up your mind as to which one you're going to accept. You've been going back and forth with this twice already. – Funk Forty Niner Mar 08 '16 at 13:35

score 1 · Accepted Answer · answered Mar 08 '16 at 13:09

1

I found two errors in your query additional , at the end of field list and missing ' in where condition. Update it to following:

if (mysqli_query($db,"UPDATE division2 SET Pos ='".$pos."', played ='".$played."', won ='".$won."', drawn ='".$drawn."', lost ='".$lost."', goalsfor ='".$goalsfor."', goalsag ='".$goalsag."', goalsdif ='".$goaldif."', points ='".$points."'  WHERE team = 'Treaty Celtic'") === true) {

answered Mar 08 '16 at 13:09

Dharmesh Patel

1,881
1
11
12

Tried that still getting error inserting rows. – Walshe007 Mar 08 '16 at 13:13
what does `mysqli_error($db)` gives to you in case of error, try that in else section, that may help you – Dharmesh Patel Mar 08 '16 at 13:19
it gave me the error unknown column in field set and i checked my DB columns and found I had misspelled two columns in my php, so i fixed that and it works now, the error reporting helped a lot thanks @Dharmesh Patel – Walshe007 Mar 08 '16 at 13:32

score 0 · Answer 3 · answered Mar 08 '16 at 13:35

You need to make sure that a POST has been sent from the user form. Use the if (!empty($_POST)){...} to confirm whether the post has happened or Not. Move your entire block code to the if block.

if (!empty($_POST)){
   //your entire code here... 
}else{
   echo "No POST !!!";
}

May be there is some columns with NOT NULL attribute which caused the error... Good Luck..

how do you update a row in mysql with php from a user

3 Answers3