PHP update query not reaching database

Question

I am having an issue with my php/mysql UPDATE query not reaching my database. I know this probably has a simple fix I just cannot seem to find where my mistake is, here is my code:

This is the form I am using to send the data:

<?php

        if(!($stmt = $mysqli->prepare("SELECT bowl_games.id, bowl_games.name, stadiums.name, bowl_games.inaugural_year FROM bowl_games
                        INNER JOIN stadiums ON stadiums.id = bowl_games.stadium_id 
                        WHERE bowl_games.id = ? "))){
            echo "Prepare failed: "  . $stmt->errno . " " . $stmt->error;
        }

        if(!($stmt->bind_param("i", $_POST['bowl_game']))){
            echo "Bind failed: "  . $stmt->errno . " " . $stmt->error;
        }

        if(!$stmt->execute()){
            echo "Execute failed: "  . $mysqli->connect_errno . " " . $mysqli->connect_error;
        }
        if(!$stmt->bind_result($id, $name, $stadium, $inauguralyear)){
            echo "Bind failed: "  . $mysqli->connect_errno . " " . $mysqli->connect_error;
        }
        while($stmt->fetch()){

        }
        ?>

            <div class="container">
            <form method="post" action="update_bowl_game_2.php">
            <fieldset> <legend>Update Bowl Game</legend>

                <div class="form-group row">
                <label class="col-sm-2 col-form-label">Name</label>
                <div class="col-sm-10">
                    <input type="text", class="form-control", name="Name", value="<?php echo $name?>"/>
                </div>
                </div>

                <div class="form-group row">
                <label class="col-sm-2 col-form-label">Stadium</label>
                <div class="col-sm-10">
                    <select name="Stadium">
                        <?php 
                        if(!($stmt = $mysqli->prepare("SELECT id, name FROM stadiums ORDER BY name"))){
                            echo "Prepare failed: "  . $stmt->errno . " " . $stmt->error;
                            }

                        if(!$stmt->execute()){
                            echo "Execute failed: "  . $mysqli->connect_errno . " " . $mysqli->connect_error;
                        }
                        if(!$stmt->bind_result($id, $sname)){
                            echo "Bind failed: "  . $mysqli->connect_errno . " " . $mysqli->connect_error;
                        }
                        while($stmt->fetch()){
                            if($sname === $stadium){
                                echo "<option value=\"" .   $id . "\" selected>" . $sname . "</option>";
                                } else {
                                echo "<option value=\"" .   $id . "\">" . $sname . "</option>";
                                }
                        }
                        $stmt->close();
                        ?>
                    </select>
                </div>
                </div>

                <div class="form-group row">
                <label class="col-sm-2 col-form-label">Inaugural Year</label>
                <div class="col-sm-10">
                    <input type="number", class="form-control", name="InauguralYear", value="<?php echo $inauguralyear?>"/>
                </div>
                </div>

                    <input type="hidden" name="id" value="<?php echo $id?>"/>
                <div class="form-group row">
                <div class="offset-sm-2 col-sm-10">
                    <button type="submit" class="btn btn-primary">Update Bowl Game</button>
                </div>
                </div>
            </fieldset>
            </form>
        </div>
<?php
        $mysqli = "SELECT bowl_games.id, bowl_games.name, stadiums.name, bowl_games.inaugural_year FROM bowl_games
                   INNER JOIN stadiums ON stadiums.id = bowl_games.stadium_id"
        ?>

And here is the php code that should update the entry in the database:

<?php
//Turn on error reporting
ini_set('display_errors', 'On');
//Connects to the database
$mysqli = new mysqli("oniddb.cws.oregonstate.edu","dejarnen-db","*hidden*","dejarnen-db");
if(!$mysqli || $mysqli->connect_errno){
    echo "Connection error " . $mysqli->connect_errno . " " . $mysqli-   >connect_error;
    }

if(!($stmt = $mysqli->prepare("UPDATE bowl_games SET name=?, stadium_id=?, inaugural_year=? WHERE id= ?"))){
    echo "Prepare failed: " . $stmt->errno . " " . $stmt->error;
}

if(!($stmt- >bind_param("siii",$_POST['Name'],$_POST['Stadium'],$_POST['InauguralYear'],$_POST['id']))){
    echo "Bind failed: " . $stmt->errno . " " . $stmt->error;
}

if(!$stmt->execute()){
    echo "Execute failed: " . $stmt->errno . " " . $stmt->error;
} else {
    echo "Updated " . $stmt->affected_rows . " rows in bowl games.";
}
?>

When I submit the form, if the selected entry has been successfully updated, I should see the message "Updated 1 rows in bowl games." Instead, I get the message "Updated 0 rows in bowl games."

Can anyone point me in the right direction with this issue that I am having? Thanks

@EatPeanutButter sorry, no, my formatting was just off in the post...i have fixed it now — , Dec 04 '16 at 17:53
@NickD I would advise you setup up a development environment so that you can use a debugger to step through your code to find out what exactly is happening. So you can spot such a problem yourself next time. — NineBerry, Dec 04 '16 at 18:02
@NickD Another good idea is to enable logging in your MySQL database during development to see which statements are executed. Find out how in [this answer](http://stackoverflow.com/a/20485975/101087) — NineBerry, Dec 04 '16 at 18:05
@NineBerry thanks for that resource. Currently taking my first databases class so i'm trying to learn as much as I can — , Dec 04 '16 at 18:12

NineBerry · Accepted Answer · 2016-12-04T18:00:12.997

In the form, you use a variable named $id for two different purposes:

For the id of the game
For the id of the stadium

First you retrieve the id for the game into the variable, then you retrieve the ids of the stadiums into the variable, then you use the variable to create the hidden input for the game id.

By the time you write the hidden input, the $id variable contains an id of a stadium.

One possible solution: When listing the stadiums, use a separate variable name:

<select name="Stadium">
    <?php 
    if(!($stmt = $mysqli->prepare("SELECT id, name FROM stadiums ORDER BY name"))){
        echo "Prepare failed: "  . $stmt->errno . " " . $stmt->error;
        }

    if(!$stmt->execute()){
        echo "Execute failed: "  . $mysqli->connect_errno . " " . $mysqli->connect_error;
    }
    if(!$stmt->bind_result($stadium_id, $sname)){
        echo "Bind failed: "  . $mysqli->connect_errno . " " . $mysqli->connect_error;
    }
    while($stmt->fetch()){
        if($sname === $stadium){
            echo "<option value=\"" .   $stadium_id . "\" selected>" . $sname . "</option>";
            } else {
            echo "<option value=\"" .   $stadium_id . "\">" . $sname . "</option>";
            }
    }
    $stmt->close();
    ?>
</select>

Generally, it is a good idea to use variable names that are specific. So, instead of "$name", use "$stadium_name" and so on. The only exception to that rule is when you have local variables in a function that is very short.

Another possible solution would be to write the hidden input earlier, before filling the select with stadiums.

score -1 · Answer 2 · answered Dec 04 '16 at 18:04

-1

Firstly check $_POST data not null then use bind param like sssi.

//example
if(!($stmt- >bind_param("sssi", $_POST['Name'], $_POST['Stadium'], $_POST['InauguralYear'], $_POST['id']))){
echo "Bind failed: " . $stmt->errno . " " . $stmt->error;

answered Dec 04 '16 at 18:04

Shankar D

29
7

PHP update query not reaching database

2 Answers2