Simply trying to update an item in mysql database

Question

I am trying to take a user input and update that specific item in my database. For some reason I cannot seem to get the item to get updated.

<?php

include('../inclass/db_connect.php');

$id = $_GET["id"];

$sql = "SELECT id, teamName, coach, yearFormed, numStanleyCups, numPlayers, teamValue, totWins FROM hockeyTeams WHERE id=$id";
$result = $pdo->prepare($sql);
$result->execute();

$row = $result->fetch();
?>

<h1 align="center">Edit Team - <?php echo $row['teamName'] ?></h1>

<form method="post">
    <table align="center" border="1">
        <tr>
            <td>Team Name:<input type="text" name="teamName" value="<?php echo $row['teamName'] ?>" required /></td>
        </tr>
        <tr>
            <td>Coach Name:<input type="text" name="coach" placeholder="Coach Name" value="<?php echo $row['coach'] ?>" required /></td>
        </tr>
        <tr>
            <td>Year Team Formed:<input type="date" name="yearFormed" value="<?php echo $row['yearFormed'] ?>" required /></td>
        </tr>
        <tr>
            <td>Number of Stanley Cups Won:<select name="numStanleyCups"><option value="" selected><?php echo $row['numStanleyCups'] ?></option><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option></select></td>
        </tr>
        <tr>
            <td>Number of Players:<input type="text" name="numPlayers" value="<?php echo $row['numPlayers'] ?>"/></td>
        </tr>
        <tr>
            <td>Team Value: <input type="text" name="teamValue" value="<?php echo $row['teamValue'] ?>"/></td>
        </tr>
        <tr>
            <td>Total Team Wins: <input type="text" name="totWins" value="<?php echo $row['totWins'] ?>" /></td>
        </tr>        
        <tr>
            <td>
                <button type="submit" name="btn-edit"><strong>Edit Team</strong></button></td>    </tr>
    </table>
</form>

<?php

if(isset($_POST['btn-edit']))
{
    $sql = "UPDATE hockeyTeams SET teamName = '".$_POST['teamName']."', coach = '".$_POST['coach']."', yearFormed = '".$_POST['yearFormed']."', numStanleyCups = '".$_POST['numStanleyCups']."', numPlayers = '".$_POST['numPlayers']."', teamValue = '".$_POST['teamValue']."', totWins = '".$_POST['totWins']."' WHERE id = ".$_GET['id'];
    $result = $pdo->prepare($sql);
    $result->execute();
}

When I took out the top portion of the PHP the update function oddly enough worked once and than broke. I cant seem to put together a reasoning of why the update wouldnt be working other than having something to do with me pulling in the data at the top of the file.

I couldnt seem to find any posts that were finding a good answer to what was going wrong, I checked to make sure all my '" are correct and working so I am stuck on why it wont update!

Any help is greatly appreciated. Thanks!

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 22 '16 at 20:19
^^ It wasn't meant to be help answering the question. It was meant to be guidance on things you should do to improve the safety and security of your code. — Jay Blanchard, Apr 22 '16 at 20:45

score 3 · Accepted Answer · answered Apr 22 '16 at 20:31

The problem is that you're displaying the form before you perform the update. So you're showing the old values from the database. Move the code that performs the update to the top.

And since you're using PDO, you should use $pdo->bindParam() for your parameters, instead of concatenating strings into the SQL.

<?php

include('../inclass/db_connect.php');

$id = $_GET["id"];

if(isset($_POST['btn-edit']))
{
    $sql = "UPDATE hockeyTeams SET teamName = '".$_POST['teamName']."', coach = '".$_POST['coach']."', yearFormed = '".$_POST['yearFormed']."', numStanleyCups = '".$_POST['numStanleyCups']."', numPlayers = '".$_POST['numPlayers']."', teamValue = '".$_POST['teamValue']."', totWins = '".$_POST['totWins']."' WHERE id = ".$_GET['id'];
    $result = $pdo->prepare($sql);
    $result->execute();
}

$sql = "SELECT id, teamName, coach, yearFormed, numStanleyCups, numPlayers, teamValue, totWins FROM hockeyTeams WHERE id=$id";
$result = $pdo->prepare($sql);
$result->execute();

$row = $result->fetch();
?>

<h1 align="center">Edit Team - <?php echo $row['teamName'] ?></h1>

<form method="post">
    <table align="center" border="1">
        <tr>
            <td>Team Name:<input type="text" name="teamName" value="<?php echo $row['teamName'] ?>" required /></td>
        </tr>
        <tr>
            <td>Coach Name:<input type="text" name="coach" placeholder="Coach Name" value="<?php echo $row['coach'] ?>" required /></td>
        </tr>
        <tr>
            <td>Year Team Formed:<input type="date" name="yearFormed" value="<?php echo $row['yearFormed'] ?>" required /></td>
        </tr>
        <tr>
            <td>Number of Stanley Cups Won:<select name="numStanleyCups"><option value="" selected><?php echo $row['numStanleyCups'] ?></option><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option></select></td>
        </tr>
        <tr>
            <td>Number of Players:<input type="text" name="numPlayers" value="<?php echo $row['numPlayers'] ?>"/></td>
        </tr>
        <tr>
            <td>Team Value: <input type="text" name="teamValue" value="<?php echo $row['teamValue'] ?>"/></td>
        </tr>
        <tr>
            <td>Total Team Wins: <input type="text" name="totWins" value="<?php echo $row['totWins'] ?>" /></td>
        </tr>        
        <tr>
            <td>
                <button type="submit" name="btn-edit"><strong>Edit Team</strong></button></td>    </tr>
    </table>
</form>

This solution worked thank you. I have never used bindParam() so am not sure exactly how it would work but I will look into it and try to apply it to this! Thank you for your help. — Zgbrell, Apr 22 '16 at 20:38
He is accessing the $_POST global directly when updating though so wouldn't the order not even matter? — georaldc, Apr 22 '16 at 20:41
@georaldc The problem is that he's doing the `SELECT` query before doing the `UPDATE` query, so he's retrieving the old values from the database and displaying them in the form. So even though the database has updated, the page displays the old values when it redisplays. — Barmar, Apr 22 '16 at 20:43
@Zgbrell Read the linked question about preventing SQL injection, it shows how to use it. — Barmar, Apr 22 '16 at 20:45
@Barmar I was trying out his test link earlier and it was doing a 302 redirect to a list page after editing but I believe the list was not showing updated data so his update code wasn't working. I thought that was his problem — georaldc, Apr 22 '16 at 20:50

Simply trying to update an item in mysql database

1 Answers1