Uploading image to server: INSERT works, UPDATE doesn't

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Uploading image to server: INSERT works, UPDATE doesn't

Post by cdickson »

I'm working on a very small database for a client so that she can maintain her own Staff page online.

I have written code to upload an image along with other information when there is a new employee, but I am having trouble with uploading a new image when editing an employee.

To add an employee, this works:

Code: Select all

<?php
if($HTTP_POST_VARS['Upload'] == "Upload"){
}
 
if($_POST['Upload'] == "Upload"){
 
$originalImageFileName = $_FILES['StaffPhoto']['name'];
$tempImageFileName = $_FILES['StaffPhoto']['tmp_name'];
 
$siteRoot = $_SERVER["DOCUMENT_ROOT"];
$uploadDir = $siteRoot . "/staffadmin/";
$newImagePath = "/staffadmin/photos/" . $originalImageFileName;
 
umask(0);
move_uploaded_file($tempImageFileName, $newImagePath);
system("chmod 755 $newPath");
 
$query = "INSERT INTO staff (
   Category, List_no, StaffName, StaffTitle, Extension, StaffPhoto)
   VALUES (' " . $_POST['Category'] . " ', ' " . $_POST['List_no'] . " ', ' " . $_POST['StaffName'] . " ', ' " . $_POST['StaffTitle'] . " ', ' " . $_POST['Extension'] . " ', ' " . $_FILES['StaffPhoto']['name'] . " ')";

$result = mysql_query($query);
$lastInserted = mysql_insert_id();
}
?><form action="<?php echo $_SERVER['SCRIPT_URI']; ?>" method="post" enctype="multipart/form-data" name="Upload" id="Upload">
      <table width="100%"  border="0" cellspacing="0" cellpadding="3">
        <tr>
          <td><strong>Category:</strong></td>
          <td><select name="Category" class="bodytext">
            <option selected>Select Category...</option>
            <option value="1">Owner</option>
            <option value="2">Office</option>
            <option value="3">New Vehicles</option>
            <option value="4">Used Vehicles</option>
            <option value="5">Collision Center</option>
            <option value="6">Service Department</option>
            <option value="7">Parts Counter</option>
            <option value="8">Building Maintenance</option>
          </select></td>
        </tr>
        <tr>
          <td><strong>Order Within Category:</strong></td>
          <td><input name="List_no" type="text" id="List_no" size="10" maxlength="3" class="bodytext"></td>
        </tr>
        <tr>
          <td><strong>Name:</strong></td>
          <td><input name="StaffName" type="text" class="bodytext" id="StaffName" size="40"></td>
        </tr>
        <tr>
          <td><strong>Title:</strong></td>
          <td><input name="StaffTitle" type="text" class="bodytext" id="StaffTitle" size="40"></td>
        </tr>
        <tr>
          <td><strong>Extension:</strong></td>
          <td><input name="Extension" type="text" class="bodytext" id="Extension" size="10"></td>
        </tr>
        <tr>
          <td><strong>Image:</strong></td>
          <td><input name="StaffPhoto" type="file" class="bodytext" id="StaffPhoto" size="28">
            <input name="MAX_FILE_SIZE" type="hidden" id="MAX_FILE_SIZE2" value="6000000"></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td><input name="Upload" type="submit" id="Upload" value="Upload" class="bodytext"></td>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
      </table>
      </form>
To update an employee I have been trying several variations on this code, but the image filename is not being read when uploading:

Code: Select all

<?php
if($HTTP_POST_VARS['Upload'] == "Upload"){
}
 
if($_POST['Upload'] == "Upload"){
 
$originalImageFileName = $_FILES['StaffPhoto']['name'];
$tempImageFileName = $_FILES['StaffPhoto']['tmp_name'];
 
$siteRoot = $_SERVER["DOCUMENT_ROOT"];
$uploadDir = $siteRoot . "/staffadmin/";
$newImagePath = "/staffadmin/photos/" . $originalImageFileName;
 
umask(0);
move_uploaded_file($tempImageFileName, $newImagePath);
system("chmod 755 $newPath");

    
     //define query
     $query = "UPDATE staff SET Category='{$_POST['Category']}', List_no='{$_POST['List_no']}', StaffName='{$_POST['StaffName']}', StaffTitle='{$_POST['StaffTitle']}', Extension='{$_POST['Extension']}', StaffPhoto='{$_FILES['StaffPhoto']['name']}' WHERE id={$_POST['id']}";
     $r - mysql_query ($query); //execute query
    
     //report result
     if (mysql_affected_rows() == 1) {
        echo '<p class=\"bodytext\">The staff member information has been successfully updated!</p>';
         } else {
      echo "<p class=\"bodytext\">The staff member information could not be updated because: <b>" . mysql_error() . "</b>.
      The query was <b>$query</b>.</p>";
      }
      
         } else { //display event in form
         
      // check for valid id in URL
      if (is_numeric ($_GET['id'])) {
      
      //define query
      $query = "SELECT * FROM staff WHERE id={$_GET['id']}";
      if ($r = mysql_query ($query)) { //run query
      
      $row = mysql_fetch_array ($r); //retrieve information
      
      //make form
      echo '<form action="edit_staff.php" method="post" name="Upload" id="Upload">
      <table>
      <tr>
        <td class="bodytext">Category: </td>
        <td><input class="bodytext" type="text" name="Category" value="' . $row['Category'] . '" /></td>
      </tr>
      <tr>
        <td class="bodytext">Order within Category:</td>
        <td><input class="bodytext" type="text" name="List_no" value="' . $row['List_no'] . '" /></td>
      </tr>
      <tr>
        <td class="bodytext">Name:</td>
        <td><input class="bodytext" size="40" type="text" name="StaffName" value="' . $row['StaffName'] . '" /></td>
      </tr>
      <tr>
        <td class="bodytext">Title:</td>
        <td><input class="bodytext" size="40" type="text" name="StaffTitle" value="' . $row['StaffTitle'] . '" /></td>
      </tr>
      <tr>
        <td class="bodytext">Extension:</td>
        <td><input class="bodytext" size="40" type="text" name="Extension" value="' . $row['Extension'] . '" /></td>
      </tr>
      <tr>
        <td class="bodytext">Photo:</td>
        <td><input name="photo" type="file" id="photo" size="40" value="' . $row['StaffPhoto'] . '" />
              <input name="MAX_FILE_SIZE" type="hidden" id="MAX_FILE_SIZE" value="6000000"></td>
      </tr>
      <tr>
        <td colspan=\"2\" class="bodytext"><strong>Remember!</strong> Photos must be in JPG format and must be sized to 75 pixels wide by  110 pixels high prior to uploading.
        </td>
      </tr>
      <tr>
        <td colspan=\"2\" class="bodytext">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="2" align="center"><input type="hidden" name="id" value="' . $_GET['id'] . '" />
        <input type="submit" name="Upload" class="bodytext" value="Upload" />
        </td>
      </tr>
      </table>
      </form>';
                           
                           } else { //couldn't get information
                              echo "<p class=\"bodytext\">Couldn't retrieve the information because: <b>" . mysql_error() . "</b>.  The query was <b>$query</b>.</p>";
                           }
                           
                        } else { //no id set
                           echo '<p class=\"bodytext\">There was an error using this page.  Please contact XXXX for assistance.</p>';
                        }
                        
                     } //end main IF
                     
                     mysql_close(); //close db connection

Can anyone help me figure out where I've gone wrong?
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

You didn't put the magic words needed for upload in the updating code's form tag :arrow: enctype="multipart/form-data" ;)
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Oops... I posted my old code. I did add enctype="multipart/form-data" before I posted here, but forgot to include it in this post.

Even with this added to the form tag, it's not working. The error message I get is:
The staff member information could not be updated because: . The query was UPDATE staff SET Category='1', List_no='1', StaffName='Don Tubbs', StaffTitle='CEO', Extension='105', StaffPhoto='' WHERE id=1.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Code: Select all

$r - mysql_query ($query); //execute query
don't you mean

Code: Select all

$r = mysql_query($query); //execute query
??
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Good eye, Wayne! I corrected this, but am still getting the same error message.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Just as a test, try

Code: Select all

$r = mysql_query($query) or die(mysql_error());
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

I tried that and still get the same error message... verbatim.

There has to be some small thing that is different when using UPDATE versus INSERT with an image upload, because based on the error message I'm getting, the code is submitting all of the field information properly with the exception of the image file.

Hmmm... :?:
andrei.mita
Forum Commoner
Posts: 65
Joined: Sun May 08, 2005 4:06 am
Location: Barlad/Romania

Post by andrei.mita »

i was looking for some help with an image upload script and i found this topic. i can't help with the solving of the problem but i thought it would be better to post my question here then in a new topic. hope you don't minde... here it goes...
would it be better for me to upload pictures from users into the db in a blob field or it would be better to save them in a dir like the above script?
thanks,
andrei
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Actually my photos are doing both: uploading in a blob file in the db with the image being stored in the subdirectory that I want. Because this is an area that requires access by the client, I prefer to keep the images separate from the other web files.

Also, I solved my problem thanks to help from the smart people in this forum. Revised code is:

Code to Add Record

Code: Select all

<?php
if($HTTP_POST_VARS['Upload'] == "Upload"){
}
 
if($_POST['Upload'] == "Upload"){
 
$originalImageFileName = $_FILES['StaffPhoto']['name'];
$tempImageFileName = $_FILES['StaffPhoto']['tmp_name'];
 
$siteRoot = $_SERVER["DOCUMENT_ROOT"];
$uploadDir = $siteRoot . "/home/www/www/staffadmin/";
$newImagePath = "/home/www/www/staff/" . $originalImageFileName;
 
umask(0);
move_uploaded_file($tempImageFileName, $newImagePath);
system("chmod 755 $newImagePath");
 
$query = "INSERT INTO staff (
	Category, List_no, StaffName, StaffTitle, Extension, StaffPhoto) 
	VALUES ('" . $_POST['Category'] . "', '" . $_POST['List_no'] . "', '" . $_POST['StaffName'] . "', '" . $_POST['StaffTitle'] . "', '" . $_POST['Extension'] . "', '" . $_FILES['StaffPhoto']['name'] . "')";

$result = mysql_query($query);
$lastInserted = mysql_insert_id();
} 
?>
<form action="<?php echo $_SERVER['SCRIPT_URI']; ?>" method="post" enctype="multipart/form-data" name="Upload" id="Upload">
      <table width="100%"  border="0" cellspacing="0" cellpadding="3">
        <tr>
          <td><strong>Category:</strong></td>
          <td><select name="Category" class="bodytext">
            <option selected>Select Category...</option>
            <option value="1">Owner</option>
            <option value="2">Office</option>
            <option value="3">New Vehicles</option>
            <option value="4">Used Vehicles</option>
            <option value="5">Collision Center</option>
            <option value="6">Service Department</option>
            <option value="7">Parts Counter</option>
            <option value="8">Building Maintenance</option>
          </select></td>
        </tr>
        <tr>
          <td><strong>Order Within Category:</strong></td>
          <td><input name="List_no" type="text" id="List_no" size="10" maxlength="3" class="bodytext"></td>
        </tr>
        <tr>
          <td><strong>Name:</strong></td>
          <td><input name="StaffName" type="text" class="bodytext" id="StaffName" size="40"></td>
        </tr>
        <tr>
          <td><strong>Title:</strong></td>
          <td><input name="StaffTitle" type="text" class="bodytext" id="StaffTitle" size="40"></td>
        </tr>
        <tr>
          <td><strong>Extension:</strong></td>
          <td><input name="Extension" type="text" class="bodytext" id="Extension" size="10"></td>
        </tr>
        <tr>
          <td><strong>Image:</strong></td>
          <td><input name="StaffPhoto" type="file" class="bodytext" id="StaffPhoto" size="28">
            <input name="MAX_FILE_SIZE" type="hidden" id="MAX_FILE_SIZE2" value="6000000"></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td><input name="Upload" type="submit" id="Upload" value="Upload" class="bodytext"></td>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
      </table>
      </form>
	  </td>
            </tr>
          </table>
</body>
</html>
Code to Edit Record

Code: Select all

if($HTTP_POST_VARS['Upload'] == "Upload"){
}
 
if($_POST['Upload'] == "Upload"){
 
$originalImageFileName = $_FILES['StaffPhoto']['name'];
$tempImageFileName = $_FILES['StaffPhoto']['tmp_name'];
 
$siteRoot = $_SERVER["DOCUMENT_ROOT"];
$uploadDir = $siteRoot . "/home/www/www/staffadmin/";
$newImagePath = "/home/www/www/staff/" . $originalImageFileName;
 
umask(0);
move_uploaded_file($tempImageFileName, $newImagePath);
system("chmod 755 $newImagePath");

	  $query = "UPDATE staff SET Category='" . $_POST['Category'] ."', List_no='" . $_POST['List_no'] . "', StaffName='" . $_POST['StaffName'] . "', StaffTitle='" . $_POST['StaffTitle'] . "', Extension='" . $_POST['Extension'] . "', StaffPhoto='" . $_FILES['StaffPhoto']['name'] . "' WHERE id={$_POST['id']} LIMIT 1";
	  $r = mysql_query($query) or die(mysql_error());
	  
	  //report result
	  if (mysql_affected_rows() == 1) {
	  	echo '<p class=\"bodytext\">The staff member information has been successfully updated!</p>';
			} else {
		echo "<p class=\"bodytext\">The staff member information could not be updated because: <b>" . mysql_error() . "</b>.
		The query was <b>$query</b>.</p>";
		}
		
			} else { //display event in form
			
		// check for valid id in URL
		if (is_numeric ($_GET['id'])) {
		
		//define query
		$query = "SELECT * FROM staff WHERE id={$_GET['id']}";
		if ($r = mysql_query ($query)) { //run query
		
		$row = mysql_fetch_array ($r); //retrieve information
		
		//make form
		echo '<form action="edit_staff.php" method="post" enctype="multipart/form-data" name="Upload" id="Upload">
		<table>
		<tr>
		  <td class="bodytext">Category: </td>
		  <td><input class="bodytext" type="text" name="Category" value="' . $row['Category'] . '" /></td>
		</tr>
		<tr>
		  <td class="bodytext">Order within Category:</td>
		  <td><input class="bodytext" type="text" name="List_no" value="' . $row['List_no'] . '" /></td>
		</tr>
		<tr>
		  <td class="bodytext">Name:</td>
		  <td><input class="bodytext" size="40" type="text" name="StaffName" value="' . $row['StaffName'] . '" /></td>
		</tr>
		<tr>
		  <td class="bodytext">Title:</td>
		  <td><input class="bodytext" size="40" type="text" name="StaffTitle" value="' . $row['StaffTitle'] . '" /></td>
		</tr>
		<tr>
		  <td class="bodytext">Extension:</td>
		  <td><input class="bodytext" size="40" type="text" name="Extension" value="' . $row['Extension'] . '" /></td>
		</tr>
		<tr>
		  <td class="bodytext">Photo:</td>
		  <td><input name="MAX_FILE_SIZE" type="hidden" id="MAX_FILE_SIZE" value="6000000">
		  <input name="StaffPhoto" type="file" id="photo" size="40" value="' . $row['StaffPhoto'] . '" />
          </td>
		</tr>
		<tr>
		  <td colspan="2" class="bodytext"><strong>Remember!</strong> Photos must be in JPG format and must be sized to 75 pixels wide by  110 pixels high prior to uploading.
		  </td>
		</tr>
		<tr>
		  <td colspan="2" class="bodytext">&nbsp;</td>
		</tr>
		<tr>
		  <td colspan="2" align="center"><input type="hidden" name="id" value="' . $_GET['id'] . '" />
		  <input type="submit" name="Upload" id="Upload" class="bodytext" value="Upload" />
		  </td>
		</tr>
		</table>
		</form>';
									
									} else { //couldn't get information
										echo "<p class=\"bodytext\">Couldn't retrieve the event because: <b>" . mysql_error() . "</b>.  The query was <b>$query</b>.</p>";
									}
									
								} else { //no id set
									echo '<p class=\"bodytext\">There was an error using this page.  Please contact XXXX for assistance.</p>';
								}
								
							} //end main IF
							
							mysql_close(); //close db connection
							?>
Code to Delete Record

Code: Select all

if (isset ($_POST['submit'])) { // handle form
	  
	  $query = "DELETE FROM staff WHERE id={$_POST['id']} LIMIT 1";
	  $r = mysql_query ($query); //execute query
	  
	  // report result
	  if (mysql_affected_rows() == 1) {
	  	echo '<p class=\"bodytext\">The staff member has been deleted.</p>';
			} else {
		echo "<p class=\"bodytext\">Could not delete the staff member because: <b>" . mysql_error() . "</b>.  The query was <b>$query</b>.</p>";
		}
		
			} else { //display entry in a form
			
		// check for valid id in URL
		if (is_numeric ($_GET['id']) ) {
		
		//define query
		$query = "SELECT * FROM staff WHERE id={$_GET['id']}";
		if ($r = mysql_query ($query)) { // run query
		
		$row = mysql_fetch_array ($r); //retrieve information
		
		//make form
		echo ("<form action=\"delete_staff.php\" method=\"post\">");
		echo ("<p class=\"bodytext\"><b>Are you sure you want to delete this staff member?</b><br>
		You will not be able to retrieve this information once it has been deleted.</p>");
		
		echo ("<p><b>{$row['StaffName']}  -  {$row['StaffTitle']} </b></p>");
				
				echo ("<input type=\"hidden\" name=\"id\" value=" . $_GET['id'] . " />");
				echo ("<br><br><input class=\"bodytext\" type=\"submit\" name=\"submit\" value=\"Delete Staff Member\" /> &nbsp;<a href=\"list_staff.php\"><input class=\"bodytext\" type=\"button\" name=\Button\" value=\"Cancel\"></a>");
						
						} else { //couldn't get info
						
				echo "<p class=\"bodytext\">Could not retrieve the personnel information because: <b>" . mysql_error() . "</b>.<br />
				Info is " . mysql_info() . ".  <br />
				The query was $query.</p>";
				
				}
				
					} else { // no id set
					
				echo '<p class=\"bodytext\"><b>There has been an error using this page. Please contact XXXX for assistance.</b></p>';
				}

			} // End of main IF.
			
			mysql_close(); // Close the database connection.
Post Reply