Grab empty value that has an id from db

pingeyeg
pingeyeg used Ask the Experts™
on
I'm a bit perplexed here.  I am wanting to update the value for an entry in the db, but I can't seem to do it.  The empty value has an id, but when I try to write a conditional to look for that id and empty value, I'm returned the next id, which does have a value.  Is this not possible?  In other words, it looks like this:

id     value        prod_id
65                     756
66    image1      756
67    image2      756
68    image3       756

I want to fill in the blank value of id 65 with a file name, but I can't seem to do it.  Any ideas?
$prodId = (int)$_POST['id'];
		$grabImage = sprintf("SELECT image_id, image, prod_id 
			FROM prod_images 
			WHERE prod_id = %d
			ORDER BY image_id", $prodId);
		$gotImage = mysql_query($grabImage);
		$p = mysql_fetch_array($gotImage);
		$k=0;
		while($prod_det=mysql_fetch_array($gotImage))
		{
			$oldimg[$k]=$prod_det['image'];
			$imgid[$k]=$prod_det['image_id'];
			$k++;
		}
		if($_FILES['image']['name'] != "")
		{
			if($oldimg[0] != "")
			{
				echo $oldimg[0];
				@unlink("../images/items/".$oldimg[0]);
				$target_path = '../images/items/' .basename($_FILES['image']['name']);
				funResizeImagesUserside($_FILES['image']['tmp_name'], $target_path,'height',430,340);
				$action = basename($target_path);
				#--------------------------------- Update Image
				$updateImages = sprintf("UPDATE prod_images 
					SET image = '%s' 
					WHERE image_id = %d", $action, $imgid[0]);
				$imagesUpdated = mysql_query($updateImages) or die("Images were not updated because: " . mysql_error());
				#---------------------------------- Update Color
				$updatecolor = sprintf("UPDATE prod_colors 
					SET color = '%s' 
					WHERE image_id = %d", $color, $imgid[0]);
				mysql_query($updatecolor);
			}
			else if($oldimg[0] == "")
			{
				echo "Got here 2";
				$target_path = '../images/items/' . basename($_FILES['image']['name']);
				funResizeImagesUserside($_FILES['image']['tmp_name'], $target_path,'height',430,340);
				$action = basename($target_path);
				#--------------------------------- Update Image
				$updateImages = sprintf("UPDATE prod_images 
					SET image = '%s' 
					WHERE image_id = %d", $action, $imgid[0]);
				$imagesUpdated = mysql_query($updateImages) or die("Images were not updated because: " . mysql_error());
				#---------------------------------- Update Color
				$updatecolor = sprintf("UPDATE prod_colors 
					SET color = '%s' 
					WHERE image_id = %d", $color, $imgid[0]);
				mysql_query($updatecolor);
			}
			else
			{
				echo "Got here 3";
				$target_path = '../images/items/' . basename($_FILES['image']['name']);
				#----------------------- Insert Image
				funResizeImagesUserside($_FILES['image']['tmp_name'], $target_path,'height',430,340);
				$action = basename($target_path1);
				$updateImage = sprintf("INSERT INTO 
					prod_images 
					(image, prod_id)
					VALUES('%s',%d)", $action, $p['prod_id']);
				$imagesUpdated = mysql_query($updateImages) or die("Images were not updated because: " . mysql_error());	
				$igid=mysql_insert_id();
				#--------------
			$insqry=sprintf("INSERT INTO prod_colors 
				(color, image_id, prod_id)
				VALUES('%s',%d,%d)", $color, $igid, $p['prod_id']);
				mysql_query($insqry);
			}
		}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Couple of things to check.

Is the empty value actually a blank string "" or a NULL?

If its NULL or anything else like " " (single or more spaces) then statement, if($oldimg[0] != ""), will be true even for the NULL.

You should use instead is_null or === for this check.
e.g.
if($oldimg[0] != "" || !is_null($oldimg[0]))

similarly, if($oldimg[0] == "" || is_null($oldimg[0]))

Hope this helps.

Author

Commented:
Yeah, that's not working either.  It updated, but it only updated the second image that has a value.
Can you please explain what you are trying to do here? It would give more insight into the problem.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
Sure, the list you saw above, I want to be able to update the missing value.  Basically, the user can delete individual images from a product, but I want to leave that image's id so that the user can add something else in place of that old image.
How are you making sure that blank always come in top, what if user deletes image 2 or 3?

in the following statement:
$grabImage = sprintf("SELECT image_id, image, prod_id
                  FROM prod_images
                  WHERE prod_id = %d
                  ORDER BY image_id", $prodId);

ORDER BY image_id won't push blanks on top, instead it should be ORDER BY value, so that blanks are on top.

Also, what I see that user will be uploading one file at a time and you are trying to fill the blank entries, am I correct?

Author

Commented:
The user could be updating multiple entries at once, but the ones that have a blank value, I want to be updated as well.
In the code snippet provided by you it doesn't look like you are updating multiple values, because though you are populating the array but you are just using the index [0] for replacement.

// Here we are populating all the values
       while($prod_det=mysql_fetch_array($gotImage))
      {
            $oldimg[$k]=$prod_det['image'];
            $imgid[$k]=$prod_det['image_id'];
            $k++;
      }

// But here we are checking just index 0
           if($oldimg[0] != "")

Also, I don't see where you are trying to manipulate multiple changes by the user as the code is just checking for a single file.
if($_FILES['image']['name'] != "")

If you could show the form that is submitted, then the things will be much more clear and it would be easier to get to the solution.

Author

Commented:
There is a lot going on so it would be a bit difficult to post all the code, but here is a snippet inside the form where the images are.

Also, I have more conditionals for updating the image.  I have one for each image that is part of that product.
if(mysql_num_rows($imageFound) > 0)
		{
			if($image[0]!='')
				echo "<p><img src='../images/items/$image[0]' width='90' height=80  border='0'>&nbsp;<a href='editproduct.php?pos={$imageID[0]}'>Delete</a></p>";
			echo " 	<p> <label for='image'>Image</label> <input type='file' name='image' /></p>
					<p> <label for='color'>Colors</label><input type='text' name='color' value='{$clr[0]}' /></p>";
			if($image[1]!='')
				echo "<p><img src='../images/items/$image[1]' width='90' height=80  border='0'>&nbsp;<a href='editproduct.php?pos={$imageID[1]}'>Delete</a></p>";
            echo "<p>	<label for='image1'>Image 1</label>	<input type='file' name='image1' /> </p>
    			 <p>    <label for='color'>Color1</label><input type='text' name='color1' value='{$clr[1]}' /></p>";
			if($image[2]!='')
				echo "<p><img src='../images/items/$image[2]' width='90' height=80  border='0'>&nbsp;<a href='editproduct.php?pos={$imageID[2]}'>Delete</a></p>";
            echo "<p>	<label for='image2'>Image 2</label>	<input type='file' name='image2' /> </p>
				  <p> <label for='color'>Color2</label> <input type='text' name='color2' value='{$clr[2]}' /></p>";
			if($image[3]!='')
				echo "<p><img src='../images/items/$image[3]' width='90' height=80  border='0'>&nbsp;<a href='editproduct.php?pos={$imageID[3]}'>Delete</a></p>";
            echo "<p> <label for='image3'>Image 3</label> <input type='file' name='image3' /></p>
				  <p> <label for='color'>Color3</label><input type='text' name='color3' value='{$clr[3]}' /></p>";
			if($image[4]!='')
				echo "<p><img src='../images/items/$image[4]' width='90' height=80  border='0'>&nbsp;<a href='editproduct.php?pos={$imageID[4]}'>Delete</a></p>";

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
Let's take just this small part of things where I have added comments.  The $p var gets used later in the script, but I cannot figure out the design pattern that would be in play here.  From the example that says this:

id     value        prod_id
65                     756
66    image1      756
67    image2      756
68    image3       756

Wouldn't you want to SELECT id, prod_id FROM table WHERE value = '' LIMIT 1?  Or value = NULL maybe?
// SET A VARIABLE TO AN INTEGER VALUE OR ZERO IF POST-id IS NOT SET
$prodId = (int)$_POST['id'];

// CONFIGURE A QUERY STRING
$grabImage = sprintf("SELECT image_id, image, prod_id 
   FROM prod_images 
   WHERE prod_id = %d
   ORDER BY image_id", $prodId);

// RUN THE QUERY AND ASSIGN A RESOURCE OR FALSE TO THE gotImage VARIABLE
$gotImage = mysql_query($grabImage);

// REMOVE THE FIRST ROW OF THE RESULTS
$p = mysql_fetch_array($gotImage);
$k=0;

// USE THE WHILE ITERATOR TO ACCESS THE OTHER ROWS OF THE RESULTS SET
while($prod_det=mysql_fetch_array($gotImage))
{
   $oldimg[$k]=$prod_det['image'];
   $imgid[$k]=$prod_det['image_id'];
   $k++;
}

Open in new window

Ok got it so you would be checking for each image. Now there are 3 possible conditions
1. There are no images already for this product.
2. There is an existing image that we want to replace
3. There is a blank entry which we would like to replace, if the image is delete previously.

I would build the conditions as per following code snippet for each image. Ideally we can do it with a for loop as well.
//First check if there was a record already for that image. 
if(array_key_exists('0', $oldimg)) {
    if(!$oldimg[0]) {
       //The entry is blank, update record accordingly.
    } else {
       //The entry contains old image name, update record accordingly
    }
} else {
    //There are no records for this image index, insert the record in the table.
}

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
I would just gently suggest that there is no professional design pattern that calls for inserting a missing value into a slot in a data base table.  The keys should be just that - keys with no meaning beyond connecting the relationships in the data base.  In other words, you would not remove one column from one of the rows, you would just remove the entire row.  Typically tables that have image URLs for a product also contain an order column that facilitates the ORDER BY clause.  So instead of filling in a new image in row #65, you would just add a new row (whatever # is next) to the table for this prod_id.  Does that make sense to you?

Author

Commented:
nitinsawhney: Right now, with your code, it still looks for the first one with a image value and then overwrites that value's contents with the new one.  It's still not seeing the first empty entry and filling it in.
Most Valuable Expert 2011
Top Expert 2016

Commented:
@pingeyeg: Do you understand what is happening on line 13-14 of the annotated script at ID:35764047?  That is the same as line 7 of the script posted with the question.  If you follow that variable through the code you will almost certainly find what is wrong.

You can use var_dump() to print out the contents of the variable, and it might be a good idea to do that in every place that you find and use $p.

Best of luck with it, ~Ray
ok so you want to overwrite the first blank one, irrespective of which image user has replaced right?

Do one thing for that just change the order by clause to

ORDER BY value, image_id , with this all the blank entries will be on top.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial