UPDATE doesn't pass string into field which is initially NULL, but blank after

TrekJunky

When I fill out my form to upload pictures, it moves the files into a directory and updates my MySQL database with the string containing the file name with extension (ex: picture,jpg). The INSERT command works fine, but the UPDATE command does not fill the first field with the string that is passed, but changes the NULL into a blank field. I have spent three days on this trying all sorts of things like making the field I want first to be second, I have altered the column to be text character set utf8; and a bunch of stuff, but it always is blank. Please help me. The current code is below:

if (isset($_FILES['imgs']['tmp_name'])){
	$name = $_POST['name'];
	foreach($_FILES['imgs']['tmp_name'] as $key => $tmp_name ){
		$file_name = $_FILES['imgs']['name'][$key];
		$file_size = $_FILES['imgs']['size'][$key];
		$file_tmp = $_FILES['imgs']['tmp_name'][$key];
		$file_type = $_FILES['imgs']['type'][$key];
	$desired_dir = "../images/{$name}/";
	if (!is_dir($desired_dir)) {
		if (mkdir($desired_dir)) {
			echo "The directory: " . $desired_dir . " has been created.<br />";
		}else{
			echo "The directory: " . $desired_dir . " has NOT been created.<br />";
		}
	}
        move_uploaded_file($file_tmp,$desired_dir.$file_name);
	$num = $key + 1;
	$imgnum = "img{$num}";
	if ($file_name == "") {
	    echo "File number {$num} not changed.<br />";
	}else{
        $sqlfile = "UPDATE animals SET {$imgnum} = 'NULL' WHERE id = {$id}";
	$result = mysqli_query($con,$sqlfile) or trigger_error("Query Failed! SQL: $sqlfile - Error: ".mysqli_error(), E_USER_ERROR);;
	if (!$result) {
	  echo "<h1>Could not successfully run query ($sqlfile) from DB: " . mysqli_error() . "</h1>";
	  exit;
	}else{
	      echo "Database updated with SQL: " . $sqlfile . " and MySQL Error: " . mysqli_error() . ".<br /><br />";
	    }
        $sqlfile = "UPDATE animals SET {$imgnum}  = '{$file_name}'  WHERE id = {$id}";
	$result = mysqli_query($con,$sqlfile) or trigger_error("Query Failed! SQL: $sqlfile - Error: ".mysqli_error(), E_USER_ERROR);;
	if (!$result) {
	  echo "<h1>Could not successfully run query ($sqlfile) from DB: " . mysqli_error() . "</h1>";
	  exit;
	}else{
	      echo "Database updated with SQL: " . $sqlfile . " and MySQL Error: " . mysqli_error() . ".<br /><br />";
	    }
	}
	}
}

Weedpacket

First, I don't think you should be quoting [font=monospace]NULL[/font] unless you really do want to store a string containing the letters [font=monospace]N U L L[/font].
More significantly, where does that [font=monospace]$id[/font] come from?

TrekJunky

Weedpacket;11049389 wrote:
First, I don't think you should be quoting [font=monospace]NULL[/font] unless you really do want to store a string containing the letters [font=monospace]N U L L[/font].
More significantly, where does that [font=monospace]$id[/font] come from?

NULL is not necessary. I can remove it completely. I just tried it without the quotes and it didn't help. $id gets passed to this php file through the URL. The new code is below. It should work but doesn't. I think there is something wrong with the MySQL Database?

$id = $_GET['id'];
if (isset($_FILES['imgs']['tmp_name'])){
	$name = $_POST['name'];
	foreach($_FILES['imgs']['tmp_name'] as $key => $tmp_name ){
		$file_name = $_FILES['imgs']['name'][$key];
		$file_size = $_FILES['imgs']['size'][$key];
		$file_tmp = $_FILES['imgs']['tmp_name'][$key];
		$file_type = $_FILES['imgs']['type'][$key];
	$desired_dir = "../animal_images/{$name}/";
	if (!is_dir($desired_dir)) {
		if (mkdir($desired_dir)) {
			echo "The directory: " . $desired_dir . " has been created.<br />";
		}else{
			echo "The directory: " . $desired_dir . " has NOT been created.<br />";
		}
	}
        move_uploaded_file($file_tmp,$desired_dir.$file_name);
	$num = $key + 1;
	$imgnum = "img{$num}";
	if ($file_name == "") {
	    echo "File number {$num} not changed.<br />";
	}else{
        $sqlfile = "UPDATE animals SET {$imgnum}  = '{$file_name}'  WHERE id = {$id}";
	$result = mysqli_query($con,$sqlfile) or trigger_error("Query Failed! SQL: $sqlfile - Error: ".mysqli_error(), E_USER_ERROR);;
	if (!$result) {
	  echo "<h1>Could not successfully run query ($sqlfile) from DB: " . mysqli_error() . "</h1>";
	  exit;
	}else{
	      echo "Database updated with SQL: " . $sqlfile . " and MySQL Error: " . mysqli_error() . ".<br /><br />";
	    }
	}
	}
}

dalecosp

Apart from the fact I've never used curly braces in an SQL statement (and therefore don't know if that works or not), I'm curious: do you really have columns named "{$imgnum}" ?

TrekJunky

dalecosp;11049393 wrote:
Apart from the fact I've never used curly braces in an SQL statement (and therefore don't know if that works or not), I'm curious: do you really have columns named "{$imgnum}" ?

The curly braces allows you to do away with " . $variable . ". My variable $imgnum builds a string call img1 if the file key is 0. So, img1 and img2 and so on are my columns containing the file name.

dalecosp

You don't need ".$variable." anyway, since your SQL is built inside double quotes ("). PHP interpolates the value of $imgnum without the curly brackets. Is there any possibility you're confusing the SQL interpreter? You do seem to have error checking here:

$result = mysqli_query($con,$sqlfile) or trigger_error("Query Failed! SQL: $sqlfile - Error: ".mysqli_error(), E_USER_ERROR);;

Interesting, I now note two semicolons there ... doubt that's a big issue, but ...

The other thing, as this is an update, you might want to check the number of affected rows, as the query could be "successful" but do nothing, I think ....

TrekJunky

Thanks for noticing the two semicolons. I removed one. I have tried the affected_rows function before and it returns the number 1. To re-iterate, the field is set to NULL using PHPMyAdmin, but when the operation is done, the field is blank. No more NULL. I can also edit the field with PHPMyAdmn and input the string manually and it populates the string. So it seems that the update is being attempted AND the result is a changed field, but the change is not the string intended but a blank field.

Bonesnap

It's late, so forgive me if I am misinterpreting this.

It sounds like the value in $file_name isn't making it to your query. Have you tried dumping/printing it just before the query?

One other thing: the column will never be NULL after the operation because regardless of the value you're passing it, you have quotes around it, so it's a string. Either $file_name has a value in it and the NULL value is overwritten, or it's empty and somehow makes it past your error checking, in which case it's an empty string and overwrites your NULL value.

TrekJunky

Bonesnap, I used <code>echo "File-Name Contents are: {$file_name}";</code> just before the $sql statement and it printed the file name picture.jpg. Please note that I also have a condition, that goes like this: <code>if ($file_name == "") {
echo "File number {$num} not changed.<br />";
}else{</code> that also pertains to your comments...

Bonesnap

Make sure to use [noparse]

...

[/noparse] blocks instead of <code></code> blocks. 🙂

All I can tell is that if the original value was NULL and afterwards it's an empty string, that means an empty string is being sent, or something is being cast as an empty string and being sent. Have you tried the query without the { } braces or even simply concatenating the string?

I saw your $file_name == "" statement which is what I meant by "... and somehow makes it past your error checking". Personally if I am checking if a string is empty then I trim() it and use empty().

TrekJunky

Hello folks. I figured it out with a MySQL developer's help. I have code re0rdering the pictures below this code and it was blanking the field. Thanks to everyone for their time and effort!