Altering Data in a MySQL Table via PHP Form

VectorWolf

12-14-2008, 07:38 PM

Hey all,

I have a problem with my form not updating the column in my table, Which I can't see why it isn't. I'm also trying to get the form so set the Drop down menu to show the value in the database, Which I can't figure out how to do.

Heres the code for the form

<?php (include"includes/db_config_2.php"); ?>
<html>
<head>

<link rel="stylesheet" type="text/css" href="style/stylesheet.css" />

<title>Roster System</title>
</head>
<body>

<?php include("style/header.php"); ?>
<?php
//If cmd has not been initialized
if(!isset($cmd))
{
//display all the news
$result = mysql_query("SELECT * FROM members ORDER BY id");

//run the while loop that grabs all the members
while($r=mysql_fetch_array($result))
{
//grab the title and the ID of the news
$id=$r["id"];
$name=$r["name"];//take out the title
$psn=$r["psn"];//take out the id
$rank=$r["rank"];
$role=$r["role"];
$joindate=$r["joindate"];

//make the title a link
echo "<center><a href='altermember.php?cmd=edit&id=$id'>Alter $name - $psn - $rank - $role - $joindate</a></center>";
echo "<br>";
}
}
?>
<center>
<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
if (!isset($_POST["submit"]))
{
$id = $_GET["id"];
$sql = "SELECT * FROM members WHERE id=$id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>

<form action="altermember.php" method="post">
<input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
Name: <br/><input type="text" name="name" size="20" value="<?php echo $myrow["name"]?>" maxlength="255"/><br />
PSN: <br/><input type="text" name="psn" size="20" value="<?php echo $myrow["psn"] ?>" maxlength="255"
/><br />
Rank: <br/><select name="rank" value=<?php echo
($row["rank"])?>>
<option value="Field-Marshal">Field-Marshal</option>
<option value="General">General</option>
<option value="Lieutenant-General">Lieutenant-General</option>
<option value="Major-General">Major-General</option>
<option value="Brigadier">Brigadier</option>
<option value="Colonel">Colonel</option>
<option value="Lieutenant-Colonel">Lieutenant-Colonel</option>
<option value="Major">Major</option>
<option value="Captain">Captain</option>
<option value="Lieutenant">Lieutenant</option>
<option value="2nd Lieutenant">2nd Lieutenant</option>
<option value="Warrant Officer 1st Class">Warrant Officer 1st Class</option>
<option value="Warrant Officer 2nd Class">Warrant Officer 2nd Class</option>
<option value="Staff Sergeant">Staff Sergeant</option>
<option value="Sergeant">Sergeant</option>
<option value="Corporal">Corporal</option>
<option value="Lance Corporal">Lance Corporal</option>
<option value="Private">Private</option>
<option value="Recruit">Recruit</option>
</select>
<br />
Role: <br/><input type="text" name="role" size="20" value="<?php echo $myrow["role"] ?>" maxlength="255"
/><br />
Enlistment Date (yyyy-mm-dd): <br/><input type="text" name="join" size="20" value="<?php echo $myrow["joindate"] ?>" maxlength="255"
/><br />
<br/>

<input type="hidden" name="cmd" value="edit">

<input type="submit" name="submit" value="Alter Member">

</form>
</center>
<? } ?>


<?
if ($_POST["$submit"])
{
$id=$r["id"];
$name=$r["name"];//take out the title
$psn=$r["psn"];//take out the id
$rank=$r["rank"];
$role=$r["role"];
$joindate=$r["joindate"];

$sql = "UPDATE members SET name='$name',psn='$psn',rank='$rank',role='$role',joindate='$joindate' WHERE id='$id'";

$result = mysql_query($sql);
echo "Thank you! Information updated.";
}
}
?>
</body>
</html>

If anyone can help, That would be great!

Thanks in advance,
Sean

PappaJohn

12-14-2008, 07:49 PM

<?php (include"includes/db_config_2.php"); ?>
<html>
<head>

<link rel="stylesheet" type="text/css" href="style/stylesheet.css" />

<title>Roster System</title>
</head>
<body>

<?php include("style/header.php"); ?>
<?php
//If cmd has not been initialized
if(!isset($cmd))
{
//display all the news
$result = mysql_query("SELECT * FROM members ORDER BY id");

//run the while loop that grabs all the members
while($r=mysql_fetch_array($result))
{
//grab the title and the ID of the news
$id=$r["id"];
$name=$r["name"];//take out the title
$psn=$r["psn"];//take out the id
$rank=$r["rank"];
$role=$r["role"];
$joindate=$r["joindate"];

//make the title a link
echo "<center><a href='altermember.php?cmd=edit&id=$id'>Alter $name - $psn - $rank - $role - $joindate</a></center>";
echo "<br>";
}
}
?>
<center>
<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
if (!isset($_POST["submit"]))
{
$id = $_GET["id"];
$sql = "SELECT * FROM members WHERE id=$id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>

<form action="altermember.php" method="post">
<input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
Name: <br/><input type="text" name="name" size="20" value="<?php echo $myrow["name"]?>" maxlength="255"/><br />
PSN: <br/><input type="text" name="psn" size="20" value="<?php echo $myrow["psn"] ?>" maxlength="255"
/><br />
Rank: <br/><select name="rank" value=<?php echo
($row["rank"])?>>
<option value="Field-Marshal">Field-Marshal</option>
<option value="General">General</option>
<option value="Lieutenant-General">Lieutenant-General</option>
<option value="Major-General">Major-General</option>
<option value="Brigadier">Brigadier</option>
<option value="Colonel">Colonel</option>
<option value="Lieutenant-Colonel">Lieutenant-Colonel</option>
<option value="Major">Major</option>
<option value="Captain">Captain</option>
<option value="Lieutenant">Lieutenant</option>
<option value="2nd Lieutenant">2nd Lieutenant</option>
<option value="Warrant Officer 1st Class">Warrant Officer 1st Class</option>
<option value="Warrant Officer 2nd Class">Warrant Officer 2nd Class</option>
<option value="Staff Sergeant">Staff Sergeant</option>
<option value="Sergeant">Sergeant</option>
<option value="Corporal">Corporal</option>
<option value="Lance Corporal">Lance Corporal</option>
<option value="Private">Private</option>
<option value="Recruit">Recruit</option>
</select>
<br />
Role: <br/><input type="text" name="role" size="20" value="<?php echo $myrow["role"] ?>" maxlength="255"
/><br />
Enlistment Date (yyyy-mm-dd): <br/><input type="text" name="join" size="20" value="<?php echo $myrow["joindate"] ?>" maxlength="255"
/><br />
<br/>

<input type="hidden" name="cmd" value="edit">

<input type="submit" name="submit" value="Alter Member">

</form>
</center>
<? } ?>


<?
if ($_POST["$submit"])
{
$id=$r["id"];
$name=$r["name"];//take out the title
$psn=$r["psn"];//take out the id
$rank=$r["rank"];
$role=$r["role"];
$joindate=$r["joindate"];

$sql = "UPDATE members SET name='$name',psn='$psn',rank='$rank',role='$role',joindate='$joindate' WHERE id='$id'";

$result = mysql_query($sql);
echo "Thank you! Information updated.";
}
}
?>
</body>
</html>

Well, for one, your input field is named "join" yet you are looking for "joindate".

Whenever you encounter a problem such as this, it's a good idea to echo out the query so you can see what the variables contain.

$sql = "UPDATE members SET name='$name',psn='$psn',rank='$rank',role='$role',joindate='$joindate' WHERE id='$id'";
echo $sql;

hinch

12-14-2008, 08:03 PM

if ($_POST["$submit"]) is oO

change to
<input type="submit" name="submit" id="submit" value="Alter Member">

&&

if ($_POST['submit']=="Alter Member") {

}

VectorWolf

12-14-2008, 08:31 PM

Just tried both of these and it still doesn't update:( I don't get any errors, And when I echoed out the query, It showed there the data, But didn't update.

hinch

12-14-2008, 09:07 PM

$sql_run = mysql_query($sql) or die(mysql_error());

bet it throws an error then :)

VectorWolf

12-14-2008, 10:21 PM

After fixing what caused the error, I get this from the echo

UPDATE members SET name='',psn='',rank='',role='',joindate='' WHERE id=''

For some reason it doesn't seem to be getting the data from the form.

hinch

12-14-2008, 10:27 PM

$r is your problem use $_GET [''] and $_POST[''] instead depending on if the form is a post or a get :)

VectorWolf

12-14-2008, 11:20 PM

Thanks alot, Its working now. But my other problem is when it loads the form, It has the current data displayed. I want the drop down box to show the data thats in the database aswell. Is that possible?

hinch

12-14-2008, 11:36 PM

yup

<select>
<?php while () {?>
<option><?php echo $dbdata;?></option>
<?php } ?>
</select>

that should give you enough to get started its the structure of what you have to do you just need to put your vars in

for example this is the code I use on my sites for generating a dropdown of categories I've got it wrapped in a function as I use it in multiple places with different names etc

function genOfferCats($catname) {
$output = "<select name=\"$catname\" id=\"$catname\">";
$sql = "select * from `tblcats` ORDER BY CatName ASC";
$sql_run = mysql_query($sql) or die(mysql_error());
while ($values = mysql_fetch_array($sql_run)) {
$output = $output."<option value=\"".$values[0]."\">".$values[1]."</option>";
}
$output = $output."</select>";
return $output;
}