php form updates mySQL database

Shauna

Global variables is on on my server -- but I still can't seem to get this code to "update" my mySQL database from the web. Does anyone have an idea of what could be wrong? I'm totally at a loss.

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

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

mysql_select_db($database, $connection);

if (isset($submit))
{
$sql = "update employees
set
first = '$first'
last = '$last'
department = '$department'
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)) {
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><br><input type=\"submit\" name=\"submit\" value=\"submit\"></form>");
}

mrmufin

Just for the heck of it, why don't you echo the $sql queries you've constructed from the form data prior to running them. Make sure you're running the query you think you're running:

if (isset($submit)) { 
	$sql = "update employees set 				first='$first' 
       	last = '$last' 
       	department = '$department' 
        WHERE id = $id"; 
        $result = mysql_query($sql, $connection); 

// stick an echo in here:
echo $sql;
}

Once you do this, you'll probably see what I missed the first time I looked at this... You should have commas seperating the fields in your UPDATE statement...

SET first='$first', last='$last, etc...

Shauna

Still no luck... : (

You can view it at:

http://209.235.236.82/cuzone/staff/index.php?id=31

mrmufin

Okay, we're echo-ing the query and it looks OK.

Now, let's run the UPDATE query:

$r = mysql_query($sql);
// If there are no rows affected  by the query, see if MySQL has any error message to offer which might help us out: 
if(mysql_affected_rows() < 1) {
	echo mysql_error();
}

siwis

Try changing your sql to this:

$sql = "UPDATE employees SET first = '$first', last = '$last', department = '$department' WHERE id = $id";

Notice the COMMAS.
OOPS Sorry mrmufin! didn't read all of your first post!

Shauna

That did it!!! It told me there was a colum I misnamed and I fixed it and it now works!! You've been great help!! And I can use that code in the future to troubleshoot other problems I might have!! THANK YOU!!!!!

mrmufin

You're quite welcome, Shauna.

Shauna

Just when I thought I was getting the hang of things!!!

So I added a "log in" that users have to go through in order to edit their information... It works great and redirects them to the script I posted last time. So on that page I added this to the very top of the code (so unauthenticated users could not edit):

<?
session_start();
if(!isset($myname))
{
header("location:index.php");
}
$id = $_GET["id"];
$server = "localhost";
$user = "xxx";
$pass = "xxx";
$database = "xxx";

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

And now I can't edit any of the form fields again. Why would this change things??

Here is the code I'm using for the login form:

<?
session_start();
if($action=="logout")
{
session_destroy();
$message = "You have successfully logged off.";
}
if(isset($submit))
{
$SQL = "SELECT username, password, id FROM employees WHERE username = '$username' AND password = '$password'";
$server = "localhost";
$user = "xxx";
$pass = "xxx";
$database = "xxx";

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

                    if(mysql_num_rows($result)>0)
                    {

                            $row = mysql_fetch_array($result);
                            $myname = $row["username"];
                            session_register("myname");
                            header("location: edit.php?id=$row[id]");
                    }
                    else
                    {
                            $action = "fail";
                            $message = "Your USERNAME/PASSWORD is incorrect.";
                    }


    }

?>
<html>
<head>
<title></title>
</head>

<body>
<div align="center">
<p> </p>
<form name="login" action=index.php method=post>
<table width="100%" border="0" cellspacing="0" height="203">
<tr align="center">
<td height="240"> <br>
<br>
<? echo $message; ?>
<table width="40%" border="1" cellspacing="0" height="80" bordercolor="#0033CC">
<tr>
<td height="81">Username</td>
<td height="81">
<input type="text" name="username">
</td>
</tr>
<tr>
<td height="81">Password</td>
<td height="81">
<input type="password" name="password">
</td>
</tr>
</table>
<p>
<input type="submit" name="submit" value="Submit">
<br>
<br>
</p>
</td>
</tr>
</table>
</form>
<p> </p>
</div>
</body>
</html>

mrmufin

Ah, Shauna... unfortunately, I have no experience at all with PHP's native session management functions, which is what I suspect is being misunderstood or mis-applied. I rolled my own session management utilities way back before PHP had built-in sessions, and I've used mine ever since...

Shauna

Thanks anyway! : )

I'll look into it some more htis afternoon...