Cannot get UPDATE to work

[hadoob024]

On my site, a user enters in listing information (for a real estate property). My script takes in the information, INSERTs it into my db (which it's doing properly), then I get the value of the uniqueid (UNSIGNED INT(11), primary key, set to auto_increment) using mysql_insert_id(), then move the uploaded file and rename it using the value returned from mysql_insert_id() as the base of the filename, and the variable $ext as the extension. Once I move the file, I then try and UPDATE the previous entry with the new value for the picture's path. However, the UPDATE seems to run no problem, but nothing seems to get UPDATEd. I think it might have to do with how I'm checking it, but cannot tell. Here's my code:

//this variable is initialized as an emptry string because we do not yet have the //
//correct filename yet
$picpath = '';

$insert_query = "INSERT INTO ofrelistings(firstname, lastname, emailaddress, phonenumber, companyname, askingprice, listingtype, location, city, listingdescription, picture, dateadded) VALUES('$firstname', '$lastname', '$phonenumber', '$emailaddress', '$companyname', '$askingprice', '$listingtype', '$location', '$city', '$listingdescription', '$picpath', 'Date()')";
$insert_query_result = mysql_query($insert_query);

if (!$insert_query_result)
{
	$problemtext = 'Error INSERT-ing information into main table in addlistingprocess.php.\r\n'.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}

//else the query passed and the record was INSERT-ed successfully
else
{
	//to help with the creation of a unique file name for the uploaded pic.
	$basename = strval(mysql_insert_id());

//if we're here, then the SQL string was INSERT-ed properly.  send a            
    //message that says that the main info was added.  we'll send a final 
    //message after we UPDATE the db with the picture file information.
$subject = 'OFRE.com New Listing Alert (part 2): '.date("F j, Y, g:i a");
$body = "The new listing was successfully added to the db. \r\n\r\nListing Number: $basename \r\nWas Picture Uploaded (1 for YES, 0 for NO): $uploadsuccess \r\n";
mail($adminrecipient, $subject, $body);

//this means that a file was uploaded successfully, so now we need to find
//the value of the primary key value for the record, so that we can create
//a unique filename for this record, using the value from mysql_insert_id()
//as the base and the value from $ext as the extension.
if ($uploadsuccess === 1)
{
	//pathname for where we're moving file
	$picture = 'listingpics/'.$_FILES['picture']['name'];

	//moving file to location specified by $picture
	if (!move_uploaded_file($_FILES['picture']['tmp_name'], $picture))
	{
		//just a quick alert that a listing was not moved successfully
		$subject = 'OFRE.com New Listing Alert (part 3): '.date("F j, Y, g:i a");
		$body = "The picture for the new listing was NOT moved successfully. \r\nListing Number: $basename \r\nPicture Path: $picture\r\n";
		mail($adminrecipient, $subject, $body);
	}
	//else we successfully moved the picture to its new location
	else
	{
		$newpath = 'listingpics/'.$basename.'.'.$ext;
		rename ($picture, $newpath);
		$basename = intval($basename);
		$newpath = mysql_real_escape_string($newpath);
		$update_query = "UPDATE ofrelistings SET picture='$newpath' WHERE ofrelistings.uniqueid='$basename'";
		echo $update_query;
		$update_result = mysql_query($update_query);
		if (!$insert_query_result)
		{
			$problemtext = 'Error UPDATE-ing picture information in main tables in addlistingprocess.php. \r\n'.mysql_error($connresult);
			trigger_error($problemtext, E_USER_ERROR);
		}
		//else the query was executed successfully
		else
		{
			//just a quick alert that a listing was updated successfully with the
			//path of the uploaded picture
			$subject = 'OFRE.com New Listing Alert (part 3): '.date("F j, Y, g:i a");
			$body = "The db was successfully updated with the picture path info. \r\nListing Number: $basename \r\nPath to Picture: $newpath \r\n";
			mail($adminrecipient, $subject, $body);
		}
	}
}
}

However, although the UPDATE seems to execute without error, nothing gets updated. I think it has to do with how I do my comparison. Like I check the following:

WHERE ofrelistings.uniqueid='$basename'

But I know that uniqueid is stored as a UNSIGNED INT(11). Could this be why the record's not being updated properly?

[devinemke]

are all of the uploaded files stored in that same "listingpics" directory? if so then why do you need to store the path? in fact all you need to store is the file extension since the filename will be the primary key value.

[hadoob024]

I set it up that way for the users that don't upload pictures. Then the default empty string gets stored in the database. Thus when users search, if the path value is an empty string, I display a generic image, instead of loading the path of the image that was uploaded.

[hadoob024]

This is weird. I just noticed that if I take the resulting string from this:

$update_query = "UPDATE ofrelistings SET ofrelistings.picture='$newpath' WHERE ofrelistings.uniqueid='$basename'";

echo $update_query;

Like the last one I ran generated:

UPDATE ofrelistings SET ofrelistings.picture='listingpics/9.jpg' WHERE ofrelistings.uniqueid='9'

When this is executed in my script, nothing gets updated. However, if I go into phpMyAdmin, and run this query through the SQL command line, the record gets updated correctly. Any thoughts?

[Roger_Ramjet]

Why are you a) converting the id into a string and b) putting single quotes around an interger value?

The user notes in the manual on strval discuss the problem you are encountering, not worth repeating here as the problem is of your own making. The fix is just to get the last insert id and use it as-is and as it should be used, as an int.

[hadoob024]

I converted the id to a string because I wanted to use the value of uniqueid to rename the file that's being uploaded by the user. Basically, I INSERT all the user information, then I get the value of uniqueid using mysql_insert_id(). I take this value, along with $ext (the extension of the picture file which I calculate earlier in the script), and move and then rename the uploaded file. I then want to go back and UPDATE the record with the new name/path-info of the uploaded file.

Hmmm. OK. I made the changes that you suggested, but it doesn't seem to help. Here's my updated code:

$basename = mysql_insert_id();
$newpath = 'listingpics/'.$basename.'.'.$ext;
rename ($picture, $newpath);
$newpath = mysql_real_escape_string($newpath);
$basename = mysql_real_escape_string(mysql_insert_id());

$update_query = "UPDATE ofrelistings SET ofrelistings.picture = '$newpath' WHERE ofrelistings.uniqueid = $basename";

$update_result = mysql_query($update_query);

I even tried:

$update_query = "UPDATE ofrelistings SET ofrelistings.picture = '$newpath' WHERE ofrelistings.uniqueid = ".mysql_insert_id();

But when I check my table afterwards, nothing has been inserted for the picture field. But if I take that same SQL statement, and run it through the command line in phpMyAdmin, it correctly updates the record with the new picture path information. Any thoughts?

[devinemke]

hadoob024 wrote:

I set it up that way for the users that don't upload pictures. Then the default empty string gets stored in the database. Thus when users search, if the path value is an empty string, I display a generic image, instead of loading the path of the image that was uploaded.

i still don't see why you need the extra field. have your "ext" field default to NULL. when a picture is uploaded use the file's extension as the value for the "ext" field. then you will know if there is a related picture for each row based on whether "ext" IS NULL or IS NOT NULL. again, the filename is the PK so you do not need to store that separately.

i recently designed a very similar system for uploading/logging text files and this is the exact appraoch i used.

[hadoob024]

Oh OK! Now I get it. At first I thought we were doing the same thing but just storing different values. But using your method, I wouldn't even have to perform an UPDATE. I could just do everything on the initial INSERT, right? Is this what you're getting at? Thanks!

[devinemke]

hadoob024 wrote:

...But using your method, I wouldn't even have to perform an UPDATE. I could just do everything on the initial INSERT, right?

yes but you will still need to rename the file since you will not know the auto_incremented id number until after you do the insert query.

[hadoob024]

Yup. Thanks. Yeah, I changed the INSERT code to also add the $ext variable. And upon a successful INSERT, I grab the value of mysql_insert_id(), and use it along with the extension and move the uploaded file to the proper location. It works like a charm now. Thanks!