...

View Full Version : 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>
<!-- Style -->
<link rel="stylesheet" type="text/css" href="style/stylesheet.css" />
<!-- Site title -->
<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>
<? } ?>

<!-- Updates the database -->
<?
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>
<!-- Style -->
<link rel="stylesheet" type="text/css" href="style/stylesheet.css" />
<!-- Site title -->
<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>
<? } ?>

<!-- Updates the database -->
<?
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;
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum