UPDATE function Only Update if NOT Empty

[sblake161189]

Hi Guys,

 

I want my UPDATE function from the code below to only update those fields that are NOT empty - if they are empty on submit then do not update or change any values from that field. I know that sounds odd, but with my file fields, when I submit to change something else at a later date it will overwrite the photo, download1,2 & 3 values and as a result I loose my files from the mysql table.

 

Cheers, S

 

<?php 

include('config.php'); 
if (isset($_GET['Ter']) ) { 
$ter = (int) $_GET['Ter']; 
if (isset($_POST['submitted'])) {

//Photo & Document Upload Upload
$timestamp_photo = time();
$timestamp_download1 = time();
$timestamp_download2 = time();
$timestamp_download3 = time();
//This is the directory where the files will be saved
//Photos
$photo_target = "images/"; 
$photo_target = $photo_target .$timestamp_photo. basename( $_FILES['photo']['name']);
//Documents
$download_target = "documents/"; 
$download_target1 = $download_target .$timestamp_download1. basename( $_FILES['download1']['name']);
$download_target2 = $download_target .$timestamp_download2. basename( $_FILES['download2']['name']);
$download_target3 = $download_target .$timestamp_download3. basename( $_FILES['download3']['name']);

//This gets all the other information from the form 
$photo = ($_FILES['photo']['name']);
$download1 = ($_FILES['download1']['name']);
$download2 = ($_FILES['download2']['name']);
$download3 = ($_FILES['download3']['name']);
//Pause Photo/Document Upload


foreach($_POST AS $key => $value) { $_POST[$key] = mysql_real_escape_string($value); } 
$sql= "UPDATE `ter` SET `Ter` =  '{$_POST['Ter']}' , `BranchName` = '{$_POST['BranchName']}' ,  `BranchAddress` =  '{$_POST['BranchAddress']}' ,  `BranchTel` =  '{$_POST['BranchTel']}' , `BranchEmail` =  '{$_POST['BranchEmail']}' , `BranchLink` =  '{$_POST['BranchLink']}' , `Theme` =  '{$_POST['Theme']}' , `LocalInfo` =  '{$_POST['LocalInfo']}' , `BranchInfo` =  '{$_POST['BranchInfo']}' , `photo` =  '$timestamp_photo{$_FILES['photo']['name']}' , `download1` =  '$timestamp_download1{$_FILES['download1']['name']}' , `download1name` =  '{$_POST['download1name']}' , `download2` =  '$timestamp_download2{$_FILES['download2']['name']}' , `download2name` =  '{$_POST['download2name']}' , `download3` =  '$timestamp_download3{$_FILES['download3']['name']}' , `download3name` =  '{$_POST['download3name']}' WHERE `Ter` = '$ter' "; 

mysql_query($sql) or die(mysql_error()); 


//Unpause Photo/Document Upload
//Writes the photo to the server 
if(move_uploaded_file($_FILES['photo']['tmp_name'], $photo_target))
{
echo "<br />The file ".$timestamp_photo. basename( $_FILES['photo']['name']). " has been uploaded. <br />";
}
else {
echo "";
} //End of Photo/Document Upload

//Writes the photo to the server 
if(move_uploaded_file($_FILES['download1']['tmp_name'], $download_target1))
{
echo "<br />The file ".$timestamp_download1. basename( $_FILES['download1']['name']). " has been uploaded. <br />";
}
else {
echo "";
} //End of Photo/Document Upload

//Writes the photo to the server 
if(move_uploaded_file($_FILES['download2']['tmp_name'], $download_target2))
{
echo "<br />The file ".$timestamp_download2. basename( $_FILES['download2']['name']). " has been uploaded. <br />";
}
else {
echo "";
} //End of Photo/Document Upload

//Writes the photo to the server 
if(move_uploaded_file($_FILES['download3']['tmp_name'], $download_target3))
{
echo "<br />The file ".$timestamp_download3. basename( $_FILES['download3']['name']). " has been uploaded. <br />";
}
else {
echo "";
} //End of Photo/Document Upload


echo (mysql_affected_rows()) ? "<br />Edited Branch.<br />" : "<br />Nothing changed. <br />";  
} 
$row = mysql_fetch_array ( mysql_query("SELECT * FROM `ter` WHERE `Ter` = '$ter' "));
?>

[lalnfl]

Whats it doing when you submit?

[sblake161189]

When I 'upload' the first file lets say a photo in the photo field it uploads absolutley fine and shows in the table and also on the page.

 

However, at a later time, if i update the address for example and click submit. Then the file name+extension of the photo in the mysql table is removed and I'm just left with a new timestamp. But the address has been updated. This is fine for any normal text fields. But a pain with files on my form.

 

Cheers

[lalnfl]

You could use the file_exists() function, http://us2.php.net/manual/en/function.file-exists.php

[sblake161189]

Hey,

 

Yeah I like your idea... but how to do I link it in with the UPDATE command...

 

foreach($_POST AS $key => $value) { $_POST[$key] = mysql_real_escape_string($value); } 
$sql= "UPDATE `ter` SET `Ter` =  '{$_POST['Ter']}' , `BranchName` = '{$_POST['BranchName']}' ,  `BranchAddress` =  '{$_POST['BranchAddress']}' ,  `BranchTel` =  '{$_POST['BranchTel']}' , `BranchEmail` =  '{$_POST['BranchEmail']}' , `BranchLink` =  '{$_POST['BranchLink']}' , `Theme` =  '{$_POST['Theme']}' , `LocalInfo` =  '{$_POST['LocalInfo']}' , `BranchInfo` =  '{$_POST['BranchInfo']}' , `photo` =  '$timestamp_photo{$_FILES['photo']['name']}' , `download1` =  '$timestamp_download1{$_FILES['download1']['name']}' , `download1name` =  '{$_POST['download1name']}' , `download2` =  '$timestamp_download2{$_FILES['download2']['name']}' , `download2name` =  '{$_POST['download2name']}' , `download3` =  '$timestamp_download3{$_FILES['download3']['name']}' , `download3name` =  '{$_POST['download3name']}' WHERE `Ter` = '$ter' "; 

 

Cheers, S

[btherl]

You can do it in SQL

 

"`BranchName` = CASE WHEN '{$_POST['BranchName']}' != '' THEN '{$_POST['BranchName']}' ELSE `BranchName` END"

 

Or in PHP

 

"`BranchName` = " . ($_POST['BranchName'] ? "'{$_POST['BranchName']}'" : "`BranchName`")

 

In both cases, what you're saying is "Set it to the new value if the new value is not empty, otherwise set it to the old value".

[sblake161189]

I like the PHP one...

 

Do I just place that after

$sql= "UPDATE `ter` SET 

?

 

so it looks like this...

$sql= "UPDATE `ter` SET "`BranchName` = " . ($_POST['BranchName'] ? "'{$_POST['BranchName']}'" : "`BranchName`")

...etc

 

Cheers, S

[btherl]

Almost:

 

$sql= "UPDATE `ter` SET `BranchName` = " . ($_POST['BranchName'] ? "'{$_POST['BranchName']}'" : "`BranchName`")

 

There was an extra double quote in the version you just posted.  You'll need to do this for each column that may or may not need updating.