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
In the form, you use a variable named $id for two different purposes:
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.
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;