Updating data in SQL Database using PHP and HTML form

Question

I want to create a form that will update my database. I want the user to select name from the list and then enter the new values, on submission, in the table, the name will be modified which is selected.

FORM

<!DOCTYPE html>

      <html>
     <head>
<title>Logging | Warriors Elite</title>
    <script src='https://code.jquery.com/jquery-2.1.3.min.js'></script>
 </head>

      <body><h1>Update Opposition War Details</h1>
          <form method = "post" action = "updateopponent.php">
 <p>Player Name: <select name="Name">

<?

  $con = mysql_connect("localhost","db","pswd");

    if (!$con)
 {
     die('Could not connect: ' . mysql_error());
}

// Select database
mysql_select_db("978947") or die(mysql_error());

        $cdquery="SELECT Name FROM opponent";
        $cdresult=mysql_query($cdquery) or die ("Query to get data from firsttable failed: ".mysql_error());

        while ($cdrow=mysql_fetch_array($cdresult)) {
        $cdTitle=$cdrow["Name"];
            echo "<option>
                $cdTitle
            </option>";

        }

        ?>
 </select>
</p>

<p>Clan Best Attacker
<input placeholder='#. Name' type='text' name='CBA' id='CBA' />
</p>


<p>Percentage(%)
<input type='text' name='percent' id='percent' />
</p>


<p>Stars Achieved
<input type='text' name='star' id='star' />
</p>

<button id='insert'>Submit</button>

<p id='result'></p>
</form>
        <?php
     }
  ?>

PHP

 <?php
      define('HOST','localhost');
   define('USERNAME', 'db');
   define('PASSWORD','pswd');
  define('DB','');

 $con = mysqli_connect(HOST,USERNAME,PASSWORD,DB);


     $sql="UPDATE opponent SET Clan Best attack='".$_POST['CBA']."', Stars Achieved='".$_POST['star']."', Damage percentage='".$_POST['percent']."'         WHERE   Name='".intval($_REQUEST['Name'])."'";
        $result=mysql_query($sql)or 
        die ("this stuffedup");

        mysql_close($conn);
        ?>

I am able to connect to database but on clicking submit it fails(This stuffedup)

<button id='insert'>Submit</button> that does nothing unless you're using JS/Ajax, which you're not. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 3, 2016 at 13:40
Then you're attempting to connect with the mysqli_ MySQL API, but later using mysql_, and those do NOT intermix. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 3, 2016 at 13:40
then these column names Clan Best attack -Stars Achieved - Damage percentage contain spaces and will throw a syntax error. edit: mysql_close($conn); referencing an undefined variable. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 3, 2016 at 13:41
In short, your code is a mess. Do spend some time reading and understanding how syntax works and SQL. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 3, 2016 at 13:42

Jay Blanchard · Accepted Answer · 2016-03-03 14:09:05Z

1

You can't use whitespaces in column names without back ticking them:

$sql = "
UPDATE opponent
SET `Clan Best attack`='" . mysqli_real_escape_string($con, $_POST['CBA']) . "', 
    `Stars Achieved`='" . mysqli_real_escape_string($con, $_POST['star']) . "', 
    `Damage percentage`='" . mysqli_real_escape_string($con, $_POST['precent']) . "'         
WHERE `Name`='" . intval($_REQUEST['Name']) . "'
";

btw: mysql is very outdated. You want to use mysqli instead. btw2: you may also want to protect your database from injections. e.g. mysqli_real_escape_string($con, $userInput)

edited Mar 3, 2016 at 14:09

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

answered Mar 3, 2016 at 13:45

Thomas E.

961 silver badge10 bronze badges

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

3 Comments

Funk Forty Niner Over a year ago

True, however that is but "one" of many problems in their code. Do go over my comments (again, if you have already) under their question. Just this won't fix this, but is a start.

Funk Forty Niner Over a year ago

@Lorenzo Belfanti your suggested edit stackoverflow.com/review/suggested-edits/11493075 clearly is breaking the answer. The ticks MUST be included here. I rejected the edit. Whoever approves this, will hear from me/mods.

Funk Forty Niner Over a year ago

Your edit: that will only work if the OP is using the mysqli_ throughout their entire code. They are presently mixing mysqli_ and mysql_. Remember to indent code with 4 spaces. I had to re-edit again.

Collectives™ on Stack Overflow

Updating data in SQL Database using PHP and HTML form

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related