PHP driven form doesn't update data back to database!!!

[immohito]

        $name = sanitize($_POST['name'], "text"); 
		$designation = sanitize($_POST['designation'], "text");
		$phone = sanitize($_POST['phone'], "int");
		$date = sanitize($_POST['date'], "text");
        $id = sanitize($_POST['id'], "text"); 
        $new_id = create_guid(); 
		
		include_once "connect.php";
		
		$update_sql = "UPDATE `projects` SET `Name`={$Name}, `id`='{$new_id}' WHERE `projects`.`id`={$id};"; 
        $update_result = mysql_query($update_sql); 
        mysql_free_result($update_result);

I have built an html form that is filled out in two steps- first by an administrator and then by the user. The code above shows the user part after the admin has already filled a few fields and hit submit. The php generates a link to another page that contains these 4 fields- name, designation, phone and date. The code above is supposed to add data to the database in the column name but its not working. Also I get a warning and a few notices about undefined variables and indexes

Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in C:\Program Files\xampp\htdocs\badger.php on line 211

Can anyone tell me whats the problem in the code? Is it the update statement? My connect.php has no error because the previous few fields filled by the admin are being saved. The phone field shows a 0, but the other 3 are empty. Urgent help will be much appreciated.

[tom8]

Your mysql_query() probably did not return the update result. Update it to include this to see why:

$update_result = mysql_query($update_sql);
if (!$update_result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
mysql_free_result($update_result);

[immohito]

This is what it says. Could not run query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `id`='{7466FFB9-20FC-B4BD-E2D5-3DC6C1BAD403}' WHERE `projects`.`id`='{D5DB5A4C-' at line 1

I can't figure out whats wrong with that though. Its a syntactical error. Whats wrong with the syntax? Can you help me with that? In the line of code I am updating the id to a new ID so that the page that the user sees is different from the page that the admin just filled out.

[maxdream01]

Try it without the {}

[tom8]

Update the the query to this and try again:

$update_sql = "UPDATE projects SET Name='{$Name}', id='{$new_id}' WHERE id='{$id}'";

Without the {} then you need to do this:

$update_sql = "UPDATE projects SET Name='".$Name."', id='".$new_id."' WHERE id='".$id."'";

[immohito]

I tried both without the {} and tried tom8's update_sql statement. Doesn't work. Could not run query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{5F4FA1CB-050D-A534-0383-0F8F8782FCE1}''' at line 1

Its the value of the $new_id variable. Any other suggestions?

[maxdream01]

what if you did the where statement like this:
WHERE id = '$id'
would that work?

[r937]

Any other suggestions?

please echo your $update_sql variable so we can see the entire query

i suspect the string values are missing the single quotes

[immohito]

None of it works. Its the same error. What do you mean echo my entire variable? I posted the entire chunk of code right in the beginning!

[scout1idf]

I don't know if this will fix it or not, but I removed the ` and used ' and changed the "WHERE `projects`.`id`={$id}" part and removed the {} as maxdream01 suggested.

$name = sanitize($_POST['name'], "text"); 
		$designation = sanitize($_POST['designation'], "text");
		$phone = sanitize($_POST['phone'], "int");
		$date = sanitize($_POST['date'], "text");
        $id = sanitize($_POST['id'], "text"); 
        $new_id = create_guid(); 
 
		include_once "connect.php";
 
		$update_sql = "UPDATE projects SET Name='$Name', id='$new_id' WHERE id='$id';"; 
        $update_result = mysql_query($update_sql); 
        mysql_free_result($update_result);

Hope it helps....

[r937]

What do you mean echo my entire variable? I posted the entire chunk of code right in the beginning!

you posted the code, but that's not what mysql sees

mysql sees the sql ~after~ the $Name and $new_id and $id variables have been replaced by actual values

see http://ca.php.net/manual/en/function.echo.php

put it here --

$update_sql = "UPDATE ... ";

echo $update_sql 

$update_result = mysql_query($update_sql);

[immohito]

Oh ok, I got that. And tried it. It was showing the value of the variable id in double quotes. So basically the function I used to create the unique ID for every instance of the form had a variable with single quotes in it. Anyway, I removed that and there is no more MySQL syntax error. However, my data still doesn't get saved back to the database. I tried trouble shooting, and removed the updating of id to $new_id but it still doesn't work. Please see if you can help here is the code.

$update_sql = "UPDATE projects SET Name='$name' WHERE dex=$dex;";
        echo $update_sql;
        $update_result = mysql_query($update_sql);
        
        
        mysql_free_result($update_result); 

gives this output:

UPDATE projects SET Name=''kbdsb'' WHERE dex='{43D135BF-BE9E-8358-0213-0F8B5E8DD94E}';

However, the name column in my database is not being updated. Its text type. Does the length matter?

[r937]

UPDATE projects SET Name=''kbdsb'' WHERE dex='{43D135BF-BE9E-8358-0213-0F8B5E8DD94E}';

well, there's the problem

somehow, you are adding an extra set of single quotes

after you echo the sql, copy/paste it into mysql, and you will see that it does produce an error

[immohito]

Yeah actually there is an error. I used mysql_error(). Could not run query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'gkwde'' WHERE dex='{71C50C85-E4F2-987A-833F-2B0F00B63224}'' at line 1

But I can't seem to figure out where I have added the extra set of single notes.

Here's the function I use to create my id.

function create_guid() {     
    //--------------------------------------------------------------------- 
    // A function to create a unique ID 
    //--------------------------------------------------------------------- 
    static $guid = ''; 
    $uid = uniqid("", true); 
    $data = $namespace; 
    $data .= $_SERVER['REQUEST_TIME']; 
    $data .= $_SERVER['HTTP_USER_AGENT']; 
    $data .= $_SERVER['LOCAL_ADDR']; 
    $data .= $_SERVER['LOCAL_PORT']; 
    $data .= $_SERVER['REMOTE_ADDR']; 
    $data .= $_SERVER['REMOTE_PORT']; 
    $hash = strtoupper(hash('ripemd128', $uid . $guid . md5($data))); 
    $guid = '{' .   
        substr($hash,  0,  8) . 
        '-' . 
        substr($hash,  8,  4) . 
        '-' . 
        substr($hash, 12,  4) . 
        '-' . 
        substr($hash, 16,  4) . 
        '-' . 
        substr($hash, 20, 12) . 
        '}'; 
    return $guid; 
} 

[immohito]

Can anyone help me figure this out? Its really frustrating!

[r937]

the single quotes around the guid are needed

the extra single quotes around $name are what's giving the error

[immohito]

Which of the guid needs single quotes. I just removed them from $name and now everything's getting saved. Thank you so much. Everything is working now. Except when I try to update the id. Can't you update a field whose value you are using in the condition for the UPDATE-SET command. Should I write another UPDATE statement to update the id's value to that of $new_id? Here's the statement.

$update_sql = "UPDATE projects SET dex={$new_id}, Name=$name, Designation=$designation, Phone=$phone, Date=$date WHERE dex=$dex ";
        echo $update_sql;
        $update_result = mysql_query($update_sql);
        if (!$update_result) {
        echo 'Could not run query: ' . mysql_error();
        exit; 

This code gives the following error:

Could not run query: Unknown column '9A17' in 'field list'

Here's what echo $update_sql returns:

UPDATE projects SET dex={B0EE358D-9A17-FE7A-089C-C7DD2E02A27D}, Name='bcksbkd', Designation='blkcdsl', Phone=6302732, Date='blkcsnl' WHERE dex='{B1FD2661-1DB9-0778-8A1E-AEC8E4611B5D}'

So basically its a part of the $new_id that's causing the error. But I don't know why. Is there a problem in the create_guid function?

[r937]

Here's what echo $update_sql returns:

UPDATE projects SET dex={B0EE358D-9A17-FE7A-089C-C7DD2E02A27D}, Name='bcksbkd', Designation='blkcdsl', Phone=6302732, Date='blkcsnl' WHERE dex='{B1FD2661-1DB9-0778-8A1E-AEC8E4611B5D}'

that thing in red there, that needs quotes around it

[tom8]

You need to remove {} from $new_id and add '(single quote) around each variables:

$update_sql = "UPDATE projects SET dex='$new_id', Name='$name', Designation='$designation', Phone='$phone', Date='$date' WHERE dex='$dex' ";

[immohito]

Thanks for all your help tom8, r937, maxdream01 and scout1idf. The form is working perfectly. The id is getting updated in my database, however it is not getting updated in the URL after the user submits the form. I don't know if I should be bothered though lol. Thanks again. I'll bother you guys again if I get stuck somewhere.