I am trying to modify a query that I had inserted using an update query instead the update query added an additional row to the database as well as not allowing me to update a row. I am using a form and giving the variables values during runtime. The main thing I don't understand why is a duplicate entry even a problem, if you are trying to update an already existing record? Please stick to the question do not give a separate suggestion to this question, for example: "You shouldn't do this, or that". Stick to the question. There is a SQL Injection attack or the way I am using INSERT INTO is incorrect thank you for your time. [EDITED] I am not sure if it is the order at which I put the if statements themselves or I have the syntax for one or the other SQL QUERY wrong. Thank you very much for your time if possible.
<?php
$NewCharacterIDErr = $NewCharacterNameErr =
$NewCharacterEyeColorErr = $NewCharacterHairColorErr =
$NewCharacterSkinColorErr =
$NewCharacterGenderErr = "";
$NewCharacterName = $NewCharacterEyeColor = $NewCharacterHairColor =
$NewCharacterSkinColor = $NewCharacterGender = "";
$CharacterIDErr = $CharacterNameErr = $CharacterEyeColorErr =
$CharacterHairColorErr
= $CharacterSkinColorErr = $CharacterGenderErr = "";
$newID = $CharacterID = $CharacterName = $CharacterEyeColor =
$CharacterHairColor = $CharacterSkinColor = $CharacterGender = "";
$newIDErr = "";
?>
Above are where the variables are. Below is where those variables are checked with their subsequent values.
<?php
if($_SERVER["REQUEST_METHOD"] == "POST")
{
//THIS IS WHERE THE RECORD WILL BE CREATED!!!!
if (empty($_POST["CharacterID"]))
{
$CharacterIDErr = "Character's ID must be made.";
}
else
{
$CharacterID = test_input($_POST["CharacterID"]);
if (!preg_match("/[0-9]/", $CharacterID))
{
$CharacterIDErr = "Only Numbers.";
}
}
if (empty($_POST["CharacterName"]))
{
$CharacterNameErr = "Character name required.";
}
else
{
$CharacterName = test_input($_POST["CharacterName"]);
if (!preg_match("/[a-z,A-Z]/",$CharacterName))
{
$CharacterNameErr = "Only letters and white spaces
allowed";
}
}
if (empty($_POST["CharacterEyeColor"]))
{
$CharacterEyeColorErr = "You need to enter character's eye
color.";
}
else
{
$CharacterEyeColor = test_input($_POST["CharacterEyeColor"]);
if (!preg_match("/[A-Z,a-z]/",$CharacterEyeColor))
{
$CharacterEyeColorErr = "Only letters, and white
spaces allowed.";
}
}
if (empty($_POST["CharacterHairColor"]))
{
$CharacterHairColorErr = "You cannot leave this blank!";
}
else
{
$CharacterHairColor = test_input($_POST["CharacterHairColor"]);
if (!preg_match("/[A-Z,a-z]/", $CharacterHairColor))
{
$CharacterHairColorErr = "Please enter letters.";
}
}
if (empty($_POST["CharacterGender"]))
{
$CharacterGenderErr = "Please enter your character's gender..";
}
else
{
$CharacterGender = test_input($_POST["CharacterGender"]);
if (!preg_match("/[A-Z,a-z]/",$CharacterGender))
{
$CharacterGenderErr = "Only letters!";
}
}
if (empty($_POST["CharacterSkinColor"]))
{
$CharacterSkinColorErr = "Please enter your character's skin
tone.";
}
else
{
$CharacterSkinColor = test_input($_POST["CharacterSkinColor"]);
if (!preg_match("/[A-Z,a-z]/",$CharacterSkinColor))
{
$CharacterSkinColorErr = "Only letters";
}
}
//RECORD CREATION ENDS!!!
//THIS IS THE BACKEND WHERE THE CHARACTER ID WILL BE MODIFIED!!!!
if (empty($_POST["NewCharacterID"]))
{
$NewCharacterIDErr = "Character's ID must be made.";
}
else
{
$NewCharacterID = test_input($_POST["NewCharacterID"]);
if (!preg_match("/[0-9]/", $NewCharacterID))
{
$NewCharacterIDErr = "Only Numbers.";
}
}
if (empty($_POST["NewCharacterName"]))
{
$NewCharacterNameErr = "Enter new character name";
}
else
{
$NewCharacterName = test_input($_POST["NewCharacterName"]);
if (!preg_match("/[a-z,A-Z]/",$NewCharacterName))
{
$NewCharacterNameErr = "Only letters and white spaces
allowed";
}
}
if (empty($_POST["NewCharacterEyeColor"]))
{
$NewCharacterEyeColorErr = "Please enter characte
r's eye color.";
}
else
{
$NewCharacterEyeColor =
test_input($_POST["NewCharacterEyeColor"]);
if (!preg_match("/[A-Z,a-z]/",$NewCharacterEyeColor))
{
$NewCharacterEyeColorErr = "Only letters, numbers, and
white spaces allowed.";
}
}
if (empty($_POST["NewCharacterHairColor"]))
{
$NewCharacterHairColorErr = "You cannot leave this blank!";
}
else
{
$NewCharacterHairColor =
test_input($_POST["NewCharacterHairColor"]);
if (!preg_match("/[A-Z,a-z]/", $NewCharacterHairColor))
{
$NewCharacterHairColorErr = "Please enter letters.";
}
}
if (empty($_POST["NewCharacterGender"]))
{
$NewCharacterGenderErr = "Please enter your character's
gender.";
}
else
{
$NewCharacterGender =
test_input($_POST["NewCharacterGender"]);
if (!preg_match("/[A-Z,a-z]/",$NewCharacterGender))
{
$NewCharacterGenderErr = "Only letters!";
}
}
if (empty($_POST["NewCharacterSkinColor"]))
{
$NewCharacterSkinColorErr = "Please enter your character's skin
tone.";
}
else
{
$NewCharacterSkinColor =
test_input($_POST["NewCharacterSkinColor"]);
if (!preg_match("/[A-Z,a-z]/",$NewCharacterSkinColor))
{
$NewCharacterSkinColorErr = "Only letters";
}
}
if (empty($_POST["newID"]))
{
$newIDErr = "Please enter an Id to modify.";
}
else
{
//This is the WHERE value for the UPDATE Query.
$newID = test_input($_POST["newID"]);
if (!preg_match("/[0-9]/",$newID))
{
$newIDErr = "Only numbers.";
}
}
}
function test_input($data)
{
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
?>
Below is the front end form for the Insert Into SQL statements. Above is where the values that are inserted are being checked to see if they meet requirements.
<div class ="characterCreation">
<h3>Character Creation Form</h3>
<p><span class="error">* required field</span></p>
<form method="post" action="<?php echo
htmlspecialchars($_SERVER["PHP_SELF"]);?>">
Please Enter your Character's ID: <input type="number"
name="CharacterID"
value="<?php echo $CharacterID;?>">
<span class="error">* <?php echo $CharacterIDErr;?></span>
<br><br>
Please Enter your Character's Name: <input type="text"
name="CharacterName"
value="<?php echo $CharacterName;?>">
<span class="error">* <?php echo $CharacterNameErr;?></span>
<br><br>
Please Enter your Character's Eye Color: <input type="text"
name="CharacterEyeColor"
value="<?php echo $CharacterEyeColor;?>">
<span class="error">* <?php echo $CharacterEyeColorErr;?></span>
<br><br>
Please Enter your Characters's Hair Color: <input type="text"
name="CharacterHairColor"
value="<?php echo $CharacterHairColor;?>">
<span class="error">* <?php echo $CharacterHairColorErr;?></span>
<br><br>
Please enter your Character's Gender: <input type="text"
name="CharacterGender"
value="<?php echo $CharacterGender;?>">
<span class="error">* <?php echo $CharacterGenderErr;?></span>
<br><br>
Please enter your Character's Skin Color: <input type="text"
name="CharacterSkinColor"
value="<?php echo $CharacterSkinColor;?>">
<span class="error">* <?php echo $CharacterSkinColorErr;?></span>
<br>
<input type="submit" name="submit" value="Enter">
</form>
</div>
Below is where after the submit button is being pressed the insert into query is being called.
<?php
$sqlInsert = "INSERT INTO charactercreationtable (
Character_ID, Character_Name, Character_EyeColor,
Character_HairColor,
Character_Gender, Character_SkinColor)
VALUES ('$CharacterID', '$CharacterName', '$CharacterEyeColor',
'$CharacterHairColor', '$CharacterGender',
'$CharacterSkinColor')";
mysqli_query($db, $sqlInsert)
or echo("This query failed!");
$linebreak = "<br>";
?>
Above is where the INSERT INTO SQL Query is being called.
<div class ="modifyForm">
<h4>Modifying the form.</h4>
<p><span class="error">* required field</span></p>
<form method="post" action="<?php echo
htmlspecialchars($_SERVER["PHP_SELF"]);?>">
Please Enter your Character's New ID: <input type="int"
name="NewCharacterID"
value="<?php echo $NewCharacterID;?>">
<span class="error">* <?php echo $NewCharacterIDErr;?></span>
<br><br>
Please Enter your Character's New Name: <input type="text"
name="NewCharacterName"
value="<?php echo $NewCharacterName;?>">
<span class="error">* <?php echo $NewCharacterNameErr;?></span>
<br><br>
Please Enter your Character's New Eye Color: <input type="text"
name="NewCharacterEyeColor"
value="<?php echo $NewCharacterEyeColor;?>">
<span class="error">* <?php echo $NewCharacterEyeColorErr;?></span>
<br><br>
Please Enter your Characters's New Hair Color: <input type="text"
name="NewCharacterHairColor"
value="<?php echo $NewCharacterHairColor;?>">
<span class="error">* <?php echo $NewCharacterHairColorErr;?></span>
<br><br>
Please enter your Character's New Gender: <input type="text"
name="NewCharacterGender"
value="<?php echo $NewCharacterGender;?>">
<span class="error">* <?php echo $NewCharacterGenderErr;?></span>
<br><br>
Please enter your Character's New Skin Color:
<input type="text" name="NewCharacterSkinColor" value="<?php echo
$NewCharacterSkinColor;?>">
<span class="error">* <?php echo $NewCharacterSkinColorErr;?></span>
<br><br>
Please enter which ID you would like to affect: <input type="text"
name="newID" value="<?php echo $newID;?>">
<span class="error">* <?php echo $newIDErr;?></span>
<br>
<input type="submit" name="submit" value="Enter">
</div>
Above is where the frontend form for the UPDATE SQL query. Below is where the record is the UPDATE SQL query.
<?php
//Here I am updating the table using this php sql query.
$sqlModify =
"UPDATE charactercreationtable SET Character_ID ='$NewCharacterID',
Character_Name = '$NewCharacterName',
Character_EyeColor = '$NewCharacterEyeColor',
Character_HairColor = '$NewCharacterHairColor',
Character_Gender = '$NewCharacterGender',
Character_SkinColor = '$CharacterSkinColor'
//Here I am declaring which row to affect.;
WHERE '$newID'"
//Here I am ordering the query to execute.
mysqli_query($db, $sqlModify) or echo("This query failed!");
?>
Here is what is happening within the database once I try to modify the information within.
For some reason the a random number gets entered into the database. Once modifying the record takes place. The PRIMARY KEY is set as the Character_ID also. ^
