UPDATE has a strange behavior

CGTroll · August 29, 2013

I have a setup where the user has the following options:

- upload a new image to an existing article that has no existing image

- Updating article removing existing image leaving article with no image at all

- update article with a new image, replacing old image (delete it).

Deleting old image and removing image name in table works. Replacing old image with a new and update table with new image name works. Uploading new image to an article that has no existing image and updating table with new image new works. But, if user only wants to update the article itself, but keep existing image, the old image name is removed in table.

I have tried to update table by retrieving old image name and posting it again into the table, shows up blank

I have tried to update table removing the code to update the $image_name at all by only updating those that are changed, this also removes the old image name, leaving 'image_name' in table blank.

Any idea what could cause this?

Here is my code where I have tried to update table with old image name: (The code has been edited as it is part of a file called upload_data.php that also handel 8 other tables for other pages. If the code is missing something, that would be why.. The complete file is attached).

	require_once 'includes/db_connect.php';
	include 'includes/functions.php';

	sec_session_start();
	if(login_check($mysqli) !== true) 
	{
		header('Location: ./login.php?login');
		exit;
 	}
	
	if ($_SESSION['perm'] < 69) //This is the lowest rank that can view the page all ranks above can view but none below.
	{ 	echo "<script>";
		echo "alert(\" Desverre  ! Du har ikke rettigheter til å se denne siden!\");"; //Show Message Box
		echo "</script>";
		echo "<script>";
		echo "window.location = \"index.php\";"; //Send To Secure Index Page
		echo "</script>";
		exit;
	}
$_GET['pid'];
$pid = $_GET['pid'];
				
// Get data from form
	$content_id = $_POST["content_id"];
	$news_priority = $_POST['news_priority'];
	$layout = $_POST['news_layout'];
	$news_menu_title = $_POST['news_menu_title'];
	$news_title = $_POST['news_title'];
	$news_time = $_POST['news_time'];
	$myFile = $_FILES['myFile'];
	$old_image = $_POST['old_image'];
	$news_image_txt = $_POST['news_image_txt'];
	$news_content = $_POST['news_content'];
	$date = $_POST['date'];
					
// Define other options
	$table = "content_news";
	$image = 'news_image';
	define("UPLOAD_DIR", "../images/news/");
	$imageToDelete = $r["news_image"];
	$DirToDeleteIn = '../images/news/';


// Check if file was uploaded	
	if (is_uploaded_file($_FILES['myFile']['tmp_name'])) 
		{
			$fileCheck = "1";
		} 
	else 
		{
			$fileCheck = "2";
		}

				
// If file exists, upload it
	if ($fileCheck == 1) 
		{
		// Process file upload
			include "includes/process_image.php";
		// End Process file upload
		}
// END If file exists, upload it
			
// Check if old image
	if($old_image != NULL)
		{
		// Check if it needs to be deleted
			$checkVars2 = array(4, null);
			if(in_array($layout, $checkVars2))
				{
				// Delete image
					if (!unlink($DirToDeleteIn.$old_image))
						{
						// If it failed to delete
							echo ("OBS! Klarte ikke slette $DirToDeleteIn$old_image , ERROR line 259");
									
						// Pop-up-message
							echo "<script>";
							echo "alert(\"Advarsel! Innholdet ble IKKE oppdatert fordi tilhoereden fil $DirToDeleteIn$imageToDelete heller ikke kunne slettes. Vennlisgt kontakt webmaster for hjelp!\");";
							echo "window.location = \"administrate.php?pid=$pid\";";
							echo "</script>";
							exit;
						} 
					else
						{
						// If file was deleted, show message
							echo ("Deleted $DirToDeleteIn$old_image");
						}
				}
		} 
	else 
		{
		// If there are no old file, check if layout is set wrong.
			$checkVars3 = array(1, 2, 3);
			if(in_array($layout, $checkVars3))
				{
				// If layout is set to 1, 2 or 3, check if this is correct by checking if new file is present
					if($fileCheck == 2)
						{
						// No new file was added, layout was set incorectly, fix it.
							$layout = 4;
							echo "New layout was set to $layout";
						}
				}
		}
// Check if existing file has to be deleted because if new file
// First check if new file has been uploaded
	if($fileCheck == 1)
		{
		// Check if old file exist
			if($old_image != NULL)
				{
				// IF old file exist, delete it
				// Delete image
					if (!unlink($DirToDeleteIn.$old_image))
						{
						// If it failed to delete
							echo ("OBS! Klarte ikke slette $DirToDeleteIn$old_image , ERROR line 259");
									
						// Pop-up-message
							echo "<script>";
							echo "alert(\"Advarsel! Innholdet ble IKKE oppdatert fordi existerende fil $DirToDeleteIn$imageToDelete heller ikke kunne slettes. Vennlisgt kontakt webmaster for hjelp!\");";
							echo "window.location = \"administrate.php?pid=$pid\";";
							echo "</script>";
							exit;
						} 
					else
						{
						// If file was deleted, show message
							echo ("Deleted $DirToDeleteIn$old_image");
						}
									
				}
		}
	// End check to see if old file has to be deleted because of new file is uploaded.
	}
				
// Prepeare update DB
	if($fileCheck == 2) // If new file is not uploaded
		{
			if($old_image != NULL) // If old file exists
				{
					if($layout != '4') // If layout is set to 1, 2, or 3
							{
							// Update possible imge text  and set feedback message for image
								$updated_image_txt = $news_image_txt;
								$feedback_on_image = " Er fortsatt " . $old_image;
										
							// Update DB with no new image
								$inputquery = "UPDATE `content_news` SET `news_priority`='$news_priority', `news_layout`='$layout', `news_menu_title`='$news_menu_title', `news_title`='$news_title', `news_time`='$news_time', `news_image`='$old_image', `news_image_txt`='$updated_image_txt', `news_content`='$news_content', `date`='$date' WHERE `news_id`='$content_id'";
							// Print UPDATE results
								$mysqli->query($inputquery);
								print "<br /><br /><br /><br /><br /><br /><br />\n";
								print "$inputquery <br />linje 357\n";
								echo "<p>Intet nytt bilde å laste opp</p>";
								echo "<script>";
								echo "alert(\"Gratulerer! Nyheten er naa endret. Gammelt bilde: $feedback_on_image	\");";
								echo "window.location = \"administrate.php?pid=$pid\";";
								echo "</script>";
						
								if($mysqli->connect_errno) 
									{
										echo 'OBS! Klarte ikke å endre nyhet. Kontakt webmaster for hjelp. ';
									}
							}
						else // If layout is set to 4
							{
							// Set file name  and file text to blank, and set feedback image for file
								unset($GLOBALS['updated_image_name']);
								unset($GLOBALS['updated_image_txt']);
								$feedback_on_image = $old_image . "- Er nå slettet";
									
							// Update DB with no new image
								$inputquery = "UPDATE `content_news` SET `news_priority`='$news_priority', `news_layout`='$layout', `news_menu_title`='$news_menu_title', `news_title`='$news_title', `news_time`='$news_time', `news_image`='$updated_image_name', `news_image_txt`='$updated_image_txt', `news_content`='$news_content', `date`='$date' WHERE `news_id`='$content_id'";
							// Print UPDATE results
								$mysqli->query($inputquery);
								print "<br /><br /><br /><br /><br /><br /><br />\n";
								print "$inputquery <br />linje 357\n";
								echo "<p>Intet nytt bilde å laste opp</p>";
								echo "<script>";
								echo "alert(\"Gratulerer! Nyheten er naa endret. Gammelt bilde: $feedback_on_image	\");";
								echo "window.location = \"administrate.php?pid=$pid\";";
								echo "</script>";
						
								if($mysqli->connect_errno) 
									{
										echo 'OBS! Klarte ikke å endre nyhet. Kontakt webmaster for hjelp. ';
									}
							}
					}
				else 
					{
					// Set feedback message
						$feedback_on_image = 'Var ingen.';
					}

			// Update DB with no image and image_txt
				$inputquery = "UPDATE `content_news` SET `news_priority`='$news_priority', `news_layout`='$layout', `news_menu_title`='$news_menu_title', `news_title`='$news_title', `news_time`='$news_time', `news_content`='$news_content', `date`='$date' WHERE `news_id`='$content_id'";
			// Print UPDATE results
				$mysqli->query($inputquery);
				print "<br /><br /><br /><br /><br /><br /><br />\n";
				print "$inputquery <br />linje 357\n";
				echo "<p>Intet nytt bilde å laste opp</p>";
				echo "<script>";
				echo "alert(\"Gratulerer! Nyheten er naa endret. Gammelt bilde: $feedback_on_image	\");";
				echo "window.location = \"administrate.php?pid=$pid\";";
				echo "</script>";
						
				if($mysqli->connect_errno) 
					{
						echo 'OBS! Klarte ikke å endre nyhet. Kontakt webmaster for hjelp. ';
					}
			}
	// New file uploaded - update DB with new file name
		if($old_image)
			{
			// Set file name to old image file name
				$feedback_on_image = $old_image . "- Er nå slettet";
			}
		else 
			{
			// Set file name to blank
				$feedback_on_image = 'Var ingen.';
			}
					
		$inputquery = "UPDATE `content_news` SET  `news_priority`='$news_priority', `news_layout`='$layout', `news_menu_title`='$news_menu_title', `news_title`='$news_title', `news_time`='$news_time', `news_image`='$image_name', `news_image_txt`='$news_image_txt', `news_content`='$news_content', `date`='$date' WHERE `news_id`='$content_id'";
						
	// Print UPDATE results
		$mysqli->query($inputquery);
		print "<br /><br /><br /><br /><br /><br /><br />\n";
		print "$inputquery <br />linje 360\n";
		echo "<p>Nytt bilde ble lagret som " . $image_name . " til mappe: .</p>";
							
		// Check if DB was updated
		if($mysqli->connect_errno) 
			{
				echo 'Feil! Klarte ikke å oppdatere nyhet med nytt bilde i databasen. Kontakt webmaster. ';
			}
		else
			{
			// Pop-up-message
				echo "<script>";
				echo "alert(\"Gratulerer! Nyheten er naa oppdatert med nytt bilde " . $image_name . "!  Gammelt bilde: $feedback_on_image\");";
				echo "window.location = \"administrate.php?pid=$pid\";";
				echo "</script>";
			}
	// END check if DB was updated

update_data.php

Muddy_Funster · August 29, 2013

have you dumped the query to screen to see that the values being set are accurate at the time of performing the update?

CGTroll · August 29, 2013

have you dumped the query to screen to see that the values being set are accurate at the time of performing the update?

Yes, and that is what is strange, it says the correct filename is being sent to table, yet it is left blank.

Muddy_Funster · August 29, 2013

well my suggestion would be that you ignore the column altogether. I don't think you should be updating any columns that you are not actualy updating. remove all refference to your image column from the statement, as well as any other columns that are not actualy being changed and see what happens.

CGTroll · August 30, 2013

I have tried that as well and it still deletes the value in the table. I just can't understand what is causing this. Leaving those columns that doesn't need to updated out, is what I have used to do, but it didn't work, that's why I set it up like I did, setting the value by updating it with the old value.

Screen dump from the query where I have left the column out.

CGTroll · August 30, 2013

Could there be a bug or similar in the MySQL table causing this error?

PaulRyan · August 30, 2013

Could it be the spaces around the ID number in the WHERE part of the query?

CGTroll · August 31, 2013

Could it be the spaces around the ID number in the WHERE part of the query?

If that was the case, then wouldn't the whole query fail? All other columns are updated, except the news_image.

jcbones · August 31, 2013

What is the column type for the news_image? Or, just give us a structure dump of the table.

CGTroll · September 1, 2013

Here is the table structure. The only data stored on the column "news_image" is the file name, which for the test has been "test_bilde-1.jpg" (image_name = news_image):








CREATE TABLE IF NOT EXISTS `content_news` (
  `news_id` int(11) NOT NULL AUTO_INCREMENT,
  `news_priority` varchar(10) NOT NULL DEFAULT '',
  `news_layout` varchar(10) NOT NULL DEFAULT '',
  `news_menu_title` varchar(70) NOT NULL DEFAULT '',
  `news_title` varchar(150) NOT NULL DEFAULT '',
  `news_time` varchar(100) NOT NULL DEFAULT '',
  `news_image` varchar(200) NOT NULL DEFAULT '',
  `news_image_txt` varchar(150) NOT NULL,
  `news_content` longtext NOT NULL,
  `date` date NOT NULL DEFAULT '0000-00-00',
  KEY `id` (`news_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Aktuelt på Oppegardkunstforening' AUTO_INCREMENT=65 ;

Edited September 1, 2013 by CGTroll

Psycho · September 1, 2013

Off topic. Pro tip. This is a waste of code

// Check if file was uploaded
if (is_uploaded_file($_FILES['myFile']['tmp_name']))
{
    $fileCheck = "1";
}
else
{
    $fileCheck = "2";
}

and can be replaced with this

$fileCheck = (is_uploaded_file($_FILES['myFile']['tmp_name']));

$fileCheck will now be a Boolean (true/false)

CGTroll · September 1, 2013

Off topic. Pro tip. This is a waste of code
// Check if file was uploaded
if (is_uploaded_file($_FILES['myFile']['tmp_name']))
{
    $fileCheck = "1";
}
else
{
    $fileCheck = "2";
}
and can be replaced with this
$fileCheck = (is_uploaded_file($_FILES['myFile']['tmp_name']));
$fileCheck will now be a Boolean (true/false)

Cool! Thanks a lot!

Sign In

UPDATE has a strange behavior

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information