Update Problem

K0108788

$update =      "UPDATE student_project " .
                               "SET lecturer_role= ". $_POST['role'] .", " .
                               "project_name=". $_POST['project_name']  .", " .
							   "project_desc=". $_POST['project_desc'] ." " .
                               "WHERE project_ID ='$projectid' " ;               

          $result =    mysql_query($update);  echo $update;

I am having problems with this update statement. It won't update the database. I have echoed the query and the WHERE clause doesn't seem to contain the project_ID. I am getting the project_ID via $_REQUEST and have set it as $projectid.

Any help appreciated.

rincewind456

Well if you know for sure that $projectid is not being found you need to find out why!

I would also rewrite the query like so

function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}


$update =      sprintf("UPDATE student_project
		SET lecturer_role= %s
		project_name= %s
		project_desc= %s
		WHERE project_ID =%s",
		quote_smart($_POST['role']),
		quote_smart($_POST['project_name']),
		quote_smart($_POST['project_desc']),
		quote_smart($projectid));

$result =    mysql_query($update);  echo $update;

K0108788

Here is the query echo:

UPDATE student_project SET lecturer_role= Supervisor, project_name=Online Shopping Website, project_desc=TEST WHERE project_ID =

The project_ID doesn't seem to be getting the value. The $_REQUEST used to assign project_ID to $projectid works. So I am unsure why the project_ID isn't working. Is the query layed out properly? There is no missing quotes etc?

rincewind456

Echo out the $projectid after it has been assigned the value from $REQUEST, if that is blank echo out $REQUEST variable.

K0108788

Both $_REQUEST['projectid'] and $projectid echo 27. That is the correct id!

Here is the entire code for this page, maybe someone can spot something that I have missed.

<?php 
include "conn.inc.php";

$projectid = $_REQUEST['projectid'];

$query = "SELECT * FROM student_project WHERE project_ID='$projectid' ";
$result =mysql_query($query);
$row = mysql_fetch_assoc($result);

if (isset($_POST['submit']) && $_POST['submit'] == "Update Project") {
         $update =      "UPDATE student_project " .
                               "SET lecturer_role= ". $_POST['role'] .", " .
                               "project_name=". $_POST['project_name']  .", " .
							   "project_desc=". $_POST['project_desc'] ." " .
                               "WHERE project_ID=$projectid ";               

          $result =    mysql_query($update);  echo $update;
        	}  

	if ($result) { 
?>
<p>
<form action = "editproject.php" method="post">
<table border="0" cellpadding="15" bgcolor="#E6E6E6">
		To update project details, add and remove as necessary and then press 'Update Project'.
<tr class="feature">
	<td><span class="style3"><b>Current Lecturer Role</b><br>
	</span></td>
	<td>
		<span class="style3">
		<input type="radio" name="role" value="Supervisor"
		<?php echo $row['lecturer_role']=='Supervisor' ? ' checked' : '';?>>
		Supervisor<br>
		<input type="radio" name="role" value="Second Marker"
		<?php echo $row['lecturer_role']=='Second Marker' ? ' checked' : '';?>>
		Second Marker<br>
		<input type="radio" name="role" value="Third Marker"
		<?php echo $row['lecturer_role']=='Third Marker' ? ' checked' : '';?>>
		Third Marker<br>
		<input type="radio" name="role" value="Fourth Marker"
		<?php echo $row['lecturer_role']=='Fourth Marker' ? ' checked' : '';?>>
		Fourth Marker<br>
	    </span></td>
</tr>
<br>
<tr class="feature">
	<td><span class="style3"><b>Current Project Name</b></span></td>
	<td><input name="project_name" type="text"
				value="<?php echo $row['project_name']; ?>" size="61">
	</td>
</tr>
<br>
<tr class="feature">
	<td><span class="style3"><b>Current Project Description</b></span></td>
	<td><input name="project_desc" type="text"
		value="<?php echo $row['project_desc']; ?>" size="61">
	</td>
</tr>
<td colspan="2">
		<input type="hidden" name="project_ID" value="<?php echo $row['projectid']; ?>" />
      	<input type="submit" name="submit" value="Update Project">
<?php } ?>
    </td>
  </tr>
</table>
</form>
</p>

obrienkev

echo this line...

$query = "SELECT * FROM student_project WHERE project_ID='$projectid' ";
echo $query

See if $projectid has a value.

Then echo the update query. Add below quotes around $projectid
"WHERE project_ID= '$projectid' ";

Finally error check update query...

$result =  mysql_query($update) or die(mysql_error());

K0108788

echo $projectid = 27
echo $query = SELECT * FROM student_project WHERE project_ID='27'

When I add the die(mysql_error()) to the $update query; I get the following error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Shopping Website, project_desc=Sells CDs, DVDs and Video Games WHERE project_ID=' at line 1

rincewind456

Is project_id stored as a number in an integer type field or a string stored in a varchar type field?
Because your searching for a string not an integer, that is why in my code I made use of the quote_smart() function.

K0108788

project_ID is stored as INT(4) in the MySQL database.

rincewind456

K0108788 wrote:
project_ID is stored as INT(4) in the MySQL database.

Then you should not be quoting the $project_id in the query!
This

echo $query = "SELECT * FROM student_project WHERE project_ID='27' ";

Should be this

echo $query = "SELECT * FROM student_project WHERE project_ID=27";