Jump to content


Photo

Cannot get UPDATE to work


  • Please log in to reply
9 replies to this topic

#1 hadoob024

hadoob024
  • Members
  • PipPipPip
  • Advanced Member
  • 192 posts

Posted 18 April 2006 - 08:12 PM

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?

#2 hadoob024

hadoob024
  • Members
  • PipPipPip
  • Advanced Member
  • 192 posts

Posted 18 April 2006 - 08:48 PM

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?

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 April 2006 - 07:46 AM

Look at these two lines and you'll see the problem:

$update_result = mysql_query($update_query);
            if (!$insert_query_result)

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 hadoob024

hadoob024
  • Members
  • PipPipPip
  • Advanced Member
  • 192 posts

Posted 19 April 2006 - 05:14 PM

hmmmm... i can't find an emoticon to represent feeling like an embarrassed a$$... :)

anyways, thanks. i changed it up now, and am getting an error which pretty much just solved my problem. actually, i'm sure it will. just gotta test it out. thanks!!!!

#5 hadoob024

hadoob024
  • Members
  • PipPipPip
  • Advanced Member
  • 192 posts

Posted 19 April 2006 - 07:06 PM

Cool. I tested it out and turns out that the user didn't have the SELECT priviledge and that's why it kept failing. But when I was logged into phpMyAdmin, I was logged in as Admin, so I did have the priviledge. Interesting. I didn't know you needed the SELECT priviledge when you want to UPDATE. Thanks for solving my problem!!!

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 April 2006 - 07:02 PM

Glad you got it working... but you don't need the SELECT privilege to UPDATE!?!?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 hadoob024

hadoob024
  • Members
  • PipPipPip
  • Advanced Member
  • 192 posts

Posted 20 April 2006 - 10:47 PM

really??? that's weird, cuz when i changed that line to check for errors on the UPDATE command, i got an email with the error that said "user blahblah does not have the SELECT permission to perform this operation" or something like that. you see my UPDATE statement. what do you think caused that error message?


#8 desithugg

desithugg
  • Members
  • PipPipPip
  • Advanced Member
  • 281 posts
  • LocationScarborough

Posted 21 April 2006 - 12:47 AM

i hink it's supposed to be
$update_result = mysql_query($update_query);
            if (!$update_result)
and than ur error code

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 April 2006 - 03:22 AM

Weird.... the only time that I can think of where UPDATE would even think of use SELECT privileges is with multi-table updates.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 hadoob024

hadoob024
  • Members
  • PipPipPip
  • Advanced Member
  • 192 posts

Posted 21 April 2006 - 05:37 AM

yeah. isn't that weird. i still have no idea why it's happening. i just changed the logic so that i don't need to perform an UPDATE anymore. and yeah, i have no idea why it was failing because i didn't have the SELECT priviledge. i wasn't even doing anything across multiple tables. it was all within the same main table.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users