Skip to main content
U
user7747249
Participating Frequently
June 9, 2020
Answered

update clears image filename from db table when when editing record file upload php mysql

  • June 9, 2020
  • 1 reply
  • 1189 views

Hi all,

So I'm trying to edit a staff members photo in the database I have created. There are three browse buttons for different documents to upload.

I can add the photo and save it in a location and it stays there. But if I want to edit any of the text boxes but keep the photo of the staff member it will delete the photo.

I am using DW's built in Update routine and then a separate php file to do the image processing, do you think it could be happening because of this?

 

This is the UPDATE query:

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
include('staffimageprocessing.php');
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE sure_staff SET sure_staff_fname=%s, sure_staff_sname=%s, sure_staff_telno=%s, staff_photo=%s, staff_photoid=%s, staff_dbs=%s, sure_staff_email=%s, sure_staff_password=%s, sure_staff_active=%s WHERE sure_staff_id=%s",
                       GetSQLValueString($_POST['fname'], "text"),
                       GetSQLValueString($_POST['sname'], "text"),
                       GetSQLValueString($_POST['telno'], "text"),
                       GetSQLValueString($_FILES['pic']['name'], "text"),
                       GetSQLValueString($_FILES['photoid']['name'], "text"),
                       GetSQLValueString($_FILES['dbs']['name'], "text"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['password'], "text"),
                       GetSQLValueString($_POST['active'], "text"),
                       GetSQLValueString($_POST['staffid'], "int"));

  mysql_select_db($database_db, $db);
  $Result1 = mysql_query($updateSQL, $db) or die(mysql_error());

  $updateGoTo = "staff.php";

This is the Form:

 <form action="<?php echo $editFormAction; ?>" method="POST" enctype="multipart/form-data" name="form1">
        <table width="100%" border="0" cellpadding="2" cellspacing="2" class="tabledisplay">
		  <tr>
		    <th width="14%" scope="row">First Name</th>
		    <td width="2%">&nbsp;</td>
		    <td width="84%"><input name="fname" type="text" id="fname" value="<?php echo $row_loadcasenotes['sure_staff_fname']; ?>"></td>
	      </tr>
		  <tr>
		    <th scope="row">Last Name</th>
		    <td>&nbsp;</td>
		    <td><span id="tenantid"><span class="selectRequiredMsg">Please select an item.</span>
		      <input name="sname" type="text" id="sname" value="<?php echo $row_loadcasenotes['sure_staff_sname']; ?>">
		    </span></td>
	      </tr>
		  <tr>
		    <th scope="row">&nbsp;</th>
		    <td>&nbsp;</td>
		    <td>&nbsp;</td>
	      </tr>
		  <tr>
		    <th scope="row">Telephone</th>
		    <td>&nbsp;</td>
		    <td><input name="telno" type="text" id="telno" value="<?php echo $row_loadcasenotes['sure_staff_telno']; ?>"></td>
	      </tr>
		  <tr>
		    <th scope="row">Email</th>
		    <td>&nbsp;</td>
		    <td><input name="email" type="text" id="email" value="<?php echo $row_loadcasenotes['sure_staff_email']; ?>" size="50"></td>
	      </tr>
		  <tr>
		    <th scope="row">Password</th>
		    <td>&nbsp;</td>
		    <td><input name="password" type="password" id="password" value="<?php echo $row_loadcasenotes['sure_staff_password']; ?>"></td>
	      </tr>
		  <tr>
		    <th scope="row">&nbsp;</th>
		    <td>&nbsp;</td>
		    <td>&nbsp;</td>
	      </tr>
		  <tr>
		    <th scope="row">Photo</th>
		    <td>&nbsp;</td>
		    <td><input type="file" name="pic" id="pic" value="<?php echo $row_loadcasenotes['staff_photo']; ?>">
	        <a href="../documents/staff/photoid/<?php echo $row_loadcasenotes['staff_photo']; ?>" target="_blank">View File</a></td>
	      </tr>
		  <tr>
		    <th scope="row">Photo ID</th>
		    <td>&nbsp;</td>
		    <td><input type="file" name="photoid" id="photoid" value="<?php echo $row_loadcasenotes['staff_photoid']; ?>">
	        <a href="../documents/staff/photoid/<?php echo $row_loadcasenotes['staff_photoid']; ?>" target="_blank">View File</a></td>
	      </tr>
		  <tr>
		    <th scope="row">DBS</th>
		    <td>&nbsp;</td>
		    <td><input type="file" name="dbs" id="dbs" value="<?php echo $row_loadcasenotes['staff_dbs']; ?>">
	        <a href="../documents/staff/dbs/<?php echo $row_loadcasenotes['staff_dbs']; ?>" target="_blank">View File</a></td>
	      </tr>
		  <tr>
		    <th scope="row">&nbsp;</th>
		    <td>&nbsp;</td>
		    <td><select name="active">
		      <option value="YES" <?php if (!(strcmp("YES", $row_loadcasenotes['sure_staff_active']))) {echo "selected=\"selected\"";} ?>>YES</option>
		      <option value="NO" <?php if (!(strcmp("NO", $row_loadcasenotes['sure_staff_active']))) {echo "selected=\"selected\"";} ?>>NO</option>
		    </select>
	        <input name="staffid" type="hidden" id="staffid" value="<?php echo $row_loadcasenotes['sure_staff_id']; ?>"></td>
	      </tr>
		  <tr>
		    <th scope="row">&nbsp;</th>
		    <td>&nbsp;</td>
		    <td><input type="submit" name="addstaff" id="addstaff" value="Add Staff"></td>
	      </tr>
	  </table>
        <input type="hidden" name="MM_update" value="form1">
      </form>

 

And this is the image processing file:

if(!empty($_FILES['pic']['name'])) {
   if(isset($_FILES['pic']['name'])){
      $errors= array();
      $file_name = $_FILES['pic']['name'];
	  $file_size = $_FILES['pic']['size'];
      $file_tmp = $_FILES['pic']['tmp_name'];
      $file_type = $_FILES['pic']['type'];
      $file_ext=strtolower(end((explode('.',$_FILES['pic']['name']))));
      
      $extensions= array("jpeg","jpg","png","pdf","doc","docx");
      
      if(in_array($file_ext,$extensions)=== false){
         $errors[]="extension not allowed, please choose a JPEG,PNG,PDF,DOC or DOCX file.";
      }
      
      if($file_size > 6291456) {
         $errors[]='File size must be smaller than 5 MB';
      }
      
      if(empty($errors)==true) {
         move_uploaded_file($file_tmp,"../documents/staff/photoid/".$file_name);
        // echo "Success";
      }else{
        // print_r($errors);
      }
   }
}

As you can see it all seems fine.

I am even checking if there is anything in the input.

Am I placing the image processing file in the wrong place. I can't work it out. I've watched a few videos on YT and they point to the file in the form and then create the image processing file to include the update command, but if I was to do this for my other pages it would be crazy because 2 of the other pages have more than 15 file upload controls.

This topic has been closed for replies.
Correct answer

Hi Osgood,

That would be really helpful.

I would be open to a more modern workflow. I only used the DW server behaviours becase they are easy to work with visually. I come from a Visual Basic 5/6 background so what I am used to is totally different.


Below is some example code for the update page. The page is named update_user.php

 

I've used your database naming convention  so you should only have to change the database connection details at the start of the code to run the demonstration code.

 

Code assumes you have come from another page where you are passing through the 'sure_staff_id' of the staff member you wish to update. For example purposes I have hard-coded that as 'sure_staff_id = 2'. If there isnt a staff member in your database with an 'id' of 2 change that for the demonstration purpose.

 

Form is basic, no styling, showing several of your database fields so you can get an idea of the workflow. Also I'm sure you know that passwords should be stored in your database after being hashed for security so you will need to make provision for that plus validating all the other form fields before they are passed through to the database.

 

 

 

<?php
$conn = new mysqli('localhost' , 'username' , 'password' , 'databaseName');
?>
<?php
$selectUserDetails = mysqli_query($conn,"select * from sure_staff WHERE sure_staff_id = 2");
while($row = mysqli_fetch_assoc($selectUserDetails)){
$sure_staff_fname = $row['sure_staff_fname'];
$sure_staff_sname = $row['sure_staff_sname'];
$sure_staff_telno = $row['sure_staff_telno'];
$staff_photo = $row['staff_photo'];
$sure_staff_email = $row['sure_staff_email'];
$sure_staff_password = $row['sure_staff_password'];
$sure_staff_id = $row['sure_staff_id'];
}
?>
<?php
if(isset($_POST['updateStaffDetails'])) {
$sure_staff_id = $conn->real_escape_string($_POST['staffid']);
if($_FILES['pic']['name'] !== ''){
$errors = array();
$file_name = $_FILES['pic']['name'];
$file_size =$_FILES['pic']['size'];
$file_tmp =$_FILES['pic']['tmp_name'];
$file_type=$_FILES['pic']['type'];
$file_ext = strtolower(end(explode('.',$_FILES['pic']['name'])));
$extensions = array("jpeg","jpg","png");
if(in_array($file_ext, $extensions) === false){
$error['extention'] = "<p>Extension not allowed, please choose a JPEG or PNG file.</p>";
}
if($file_size > 2000000){
$error['size'] = '<p>File size must be less than 2 MB</p>';
}
if(!$error){
move_uploaded_file($file_tmp,"documents/staff/photoid/".$file_name);
$conn->query("UPDATE sure_staff SET staff_photo = '$file_name' WHERE sure_staff_id = '$sure_staff_id'");
}
header('Location: update_user.php');
}
$sure_staff_fname = $conn->real_escape_string($_POST['fname']);
$sure_staff_sname = $conn->real_escape_string($_POST['sname']);
$sure_staff_telno = $conn->real_escape_string($_POST['telno']);
$sure_staff_email = $conn->real_escape_string($_POST['email']);
$sure_staff_password = $conn->real_escape_string($_POST['password']);
$conn->query("UPDATE sure_staff SET
sure_staff_fname='$sure_staff_fname',
sure_staff_sname='$sure_staff_sname',
sure_staff_telno='$sure_staff_telno',
sure_staff_email='$sure_staff_email',
sure_staff_password='$sure_staff_password'
WHERE sure_staff_id = '$sure_staff_id'");
$updated_successfully = "Successfully Updated";
}
?>
<!DOCTYPE html>
<html lang="eng">
<head>
<title>Update Form</title>
<style>
img {
width: 150px;
display: block;
margin: 0 0 10px 0;
}
.status-message {
color: red;
}
</style>
</head>
<body>
<form name="updateForm" action="update_user.php" method="post" enctype="multipart/form-data">
<h3>Update Staff Details</h3>
<p>
<label for="fname">First Name</label><br>
<input type="text" name="fname" id="fname" value="<?php echo $sure_staff_fname; ?>">
</p>
<p>
<label for="sname">Last Name</label><br>
<input type="text" name="sname" id="sname" value="<?php echo $sure_staff_sname; ?>">
</p>
<p>
<label for="pic">Staff Photo</label><br>
<img src="documents/staff/photoid/<?php echo $staff_photo; ?>"<br>
<input type="file" name="pic" id="pic">
<?php if(isset($error['extention'])) { echo $error['extention']; }?>
<?php if(isset($error['size'])) { echo $error['size']; }?>
</p>
<p>
<label for="telno">Telephone</label><br>
<input type="text" name="telno" id="telno" value="<?php echo $sure_staff_telno; ?>">
</p>
<p>
<label for="email">Email</label><br>
<input type="text" name="email" id="email" value="<?php echo $sure_staff_email; ?>">
</p>
<p>
<label for="password">Password</label><br>
<input type="text" name="password" id="password" value="<?php echo $sure_staff_password; ?>">
</p>
<input type="hidden" name="staffid" id="staffid" value="<?php echo $sure_staff_id; ?>">
<p>
<input type="submit" name="updateStaffDetails" value="Update Staff Details">
</p>
</form>
<?php 
if(isset($updated_successfully)) {
echo "<p class='status-message'>$updated_successfully</p>"; 
}
?>
</body>
</html>

 

 

 

1 reply

A
Anonymous
June 10, 2020

Does this happen if when editing you use the browse button for the image to select it again?

 

What could be happening is the file name is empty when you are updating so the script doesnt run, resulting in an empty entry in your database column.

if(!empty($_FILES['pic']['name'])) {

 

Its difficult to tell as I long gave up using the archaic DW server behaviours as they are well out-dated. To be honest I can't even see how the dedicated image processing script you have included before even testing if the form button has been clicked is working.

 

What happens if you change:

 

GetSQLValueString($_FILES['pic']['name'], "text"),

 

To:

 

GetSQLValueString($_POST['pic'],"text")

 

I'm assuming the image would then not be deleted from the database as its taking the value from the 'pic' form 'value' attribute and re-inserting it into the database but then you cant update the image if you wanted to without finding another way like linking to  a modal window which would  allow for an alternative image to be selected and uploaded to your upload image folder

 

Edited: After closer inspecton of your code I think I see your problem type="file" doesnt accept a 'value' attribute as far as I know, it opens up a scecurity risk.

<input type="file" name="pic" id="pic" value="<?php echo $row_loadcasenotes['staff_photo']; ?>">

 

The way I see it is you can 'update' the text fields but if you want to update the image you need to do that independently by providing a link to a seperate upload workflow, a pop up window or page evoked from a link on the update page, where a script can then upload the new image into your chosen folder, then update the name of the image in the database using the appropriate id which you can provide via the link to the update pop-up window/page.

    U
    user7747249Author
    Participating Frequently
    June 10, 2020

    Thanks Osgood,

    I've just checked your message.

    I understand that file input does not accept value, I was hoping it would. pull the info from the database but no.

    I think I will have to do it the way you have described but it seems odd that there isn't a straight forward way of doing it.

     

    Perhaps in my image processing file I can update the column that way and remove it from the main update query.

    Thanks for your post I will check it out.

    The other points you asked about were tried without any luck.

     

     

    A
    Anonymous
    June 10, 2020

    'Perhaps in my image processing file I can update the column that way and remove it from the main update query.'

     

    Thats what I would try, keep the handling of the image seperate. Just show a small thumbnail of the image on the update page with an update link if someone requires to update the image. Script can then delete the old image from the upload folder, upload the new image and update the column in the database with the new image name, then redirect back to the update page showing the new thumbnail image.

     

    You could get into drag and drop ajax update, which would handle the process seamlessly, but that's another whole story.

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices