PHP for editing/updating content in mySQL database

[shaunah]

I seem to be doing something wrong. I'm using PHP 4.22 and am wanting to edit the content of a single record. However, I can't ever get the record to update. Can someone help me find out where I'm going wrong??? Thanks!!

<?
session_start();
$id = $_GET["id"];
$server = "localhost";
$user = "xxx";
$pass = "xxx";
$database = "xxx";

if (isset($Submit)):
{
$id = $_GET["id"];
$sql = "update employees
set first = '$first',
last = '$last',
department = '$department',
title = '$title',
extension = '$extension',
email = '$email',
bday = '$bday',
anniversary = '$anniversary',
bio = '$bio',
homephone = '$homephone',
cellphone = '$cellphone',
username = '$username',
password = '$password',
WHERE id = $id";
$result = mysql_query($sql, $connection);
}
endif;

$connection = mysql_connect($server, $user, $pass);

mysql_select_db($database, $connection);

$sql = "SELECT * FROM employees WHERE id = '$id'";

$result = mysql_query($sql, $connection);

while ($row = mysql_fetch_array($result)) {
echo ("<form name=\"form1\" method=\"post\" action=\"index.php?id=");
echo ("$id");
echo ("\">");
echo ("<p><img src=\"");
echo ("photos/");
echo ("$row[photo]");
echo (".jpg\" ");
echo ("width=\"100\" height=\"150\" align=\"left\"><b>");

echo ("First: <input type=\"text\" value=\"");
echo ("$row[first]");
echo ("\" name=\"first\">");

echo ("<br>");

echo ("Last: <input type=\"text\" value=\"");
echo ("$row[last]");
echo ("\" name=\"last\">");

echo ("<br>");

echo ("Department: <input type=\"text\" value=\"");
echo ("$row[department]");
echo ("\" name=\"department\">");

echo ("<br>");

echo ("Title: <input type=\"text\" value=\"");
echo ("$row[position]");
echo ("\" name=\"title\">");

echo ("<br>");

echo ("Work Phone: <input type=\"text\" value=\"");
echo ("$row[extension]");
echo ("\" name=\"extension\">");

echo ("<br>");

echo ("Email: <input type=\"text\" value=\"");
echo ("$row[email]");
echo ("\" name=\"email\">");

echo ("<br>");

echo ("Birthday: <input type=\"text\" value=\"");
echo ("$row[bday]");
echo ("\" name=\"bday\">");

echo ("<br>");

echo ("Anniversary: <input type=\"text\" value=\"");
echo ("$row[anniversary]");
echo ("\" name=\"anniversary\">");

echo ("<br>");
echo ("<br>");
echo ("<br>");

echo ("Biography: <br><textarea name=\"bio\" rows=\"5\" cols=\"30\">");
echo ("$row[bio]");
echo ("</textarea>");

echo ("<br>");

echo ("Home Phone: <input type=\"text\" value=\"");
echo ("$row[homephone]");
echo ("\" name=\"homephone\">");

echo ("<br>");

echo ("Cell Phone: <input type=\"text\" value=\"");
echo ("$row[cellphone]");
echo ("\" name=\"cellphone\">");

echo ("<br>");

echo ("Pager: <input type=\"text\" value=\"");
echo ("$row[pager]");
echo ("\" name=\"pager\">");

echo ("<br>");

echo ("User Name (for updating this information): <input type=\"text\" value=\"");
echo ("$row[username]");
echo ("\" name=\"username\">");

echo ("<br>");

echo ("Password (for updating this information): <input type=\"text\" value=\"");
echo ("$row[password]");
echo ("\" name=\"password\">");


echo ("<br><br><input type=\"submit\" name=\"Submit\" value=\"Submit\"></form>");
}

?>

[Spookster]

At first glance I see a few problems in your coding. First one:

if (isset($Submit)):

You got a colon on the end there. There should be nothing between that last parenthesis and the opening bracket of the if statement.

Second one:

WHERE id = $id";
$result = mysql_query($sql, $connection);
}
endif;


You are trying to run the query before you have set up the connnection and selected the database.

[Spookster]

And also a tip. Instead of using a hundred echo statements you could either use heredoc syntax or just exit out of php mode to display content not requiring variables. Here is a thread with more info on that:

http://www.codingforums.com/showthread.php?s=&threadid=2840

[Spookster]

Oh and almost forgot....since you are using PHP4.22 your host may have register_globals set to off as that is the default setting now. Some hosts turn it back on for backwards compatibility but not all may do that. So keep that in mind when grabbing variables from the form.

See here for more on that:

http://www.codingforums.com/showthread.php?s=&threadid=1706&highlight=phpself

Also since your form is in the same page as your script you can set the action of your form like so:

<FORM METHOD=POST ACTION="<?php echo $_SERVER[PHP_SELF];?>">

[shaunah]

thanks for the tip. i still have a LOT to learn. i appriciate all the help i can get!!

still not working, after making the corrections though... any other thoughts??

if (isset($Submit))
{
$id = $_GET["id"];
$sql = "update employees
set first = '$first',
last = '$last',
department = '$department',
title = '$title',
extension = '$extension',
email = '$email',
bday = '$bday',
anniversary = '$anniversary',
bio = '$bio',
homephone = '$homephone',
cellphone = '$cellphone',
username = '$username',
password = '$password',

WHERE id = $id";
$result = mysql_query($sql, $connection);
}

[shaunah]

I think register_globals is on because I am able to update with a form on other applications (updating multiple rows at once). I just can't see what I'm missing here...

Is there a better way to update data than what I'm doing??

[Spookster]

You are still running the query before setting up the database connection and selecting the database.

these two lines need to happen before running the query:

$connection = mysql_connect($server, $user, $pass);

mysql_select_db($database, $connection);

[Spookster]

And also in situations where you need to put a variable into a file name you can concatentate a string together like so:

echo $row[photo] . ".jpg";

[Spookster]

Also just noticed you are using the post method in your form and using _GET in php to retrieve the variable:

$id = $_GET["id"];

try

$id = $_POST["id"];

[shaunah]

$connection = mysql_connect($server, $user, $pass);
mysql_select_db($database, $connection);

if (isset($Submit))
{
$id = $_GET["id"];
$sql = "update employees
set first = '$first',
last = '$last',
department = '$department',
title = '$title',
extension = '$extension',
email = '$email',
bday = '$bday',
anniversary = '$anniversary',
bio = '$bio',
homephone = '$homephone',
cellphone = '$cellphone',
username = '$username',
password = '$password',
WHERE id = $id";
$result = mysql_query($sql, $connection);
}

$sql = "SELECT * FROM employees WHERE id = '$id'";

$result = mysql_query($sql, $connection);

while ($row = mysql_fetch_array($result))

or am i still missing it??

[Spookster]

yes that is better.

[shaunah]

still no luck... thanks for the input though...

[Spookster]

You are still attempting to retrieve the form value using get verses post:

$id = $_GET["id"];

Also here is a tutorial on working with mysql and php. You can go straight to part 4:

http://www.mysql.com/articles/ddws/

[shaunah]

Actually, I'm using $_POST["id"] with still no luck. I'll look in the documentation though. Thanks.