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

immohito · June 14, 2012, 1:04am

        $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 · June 14, 2012, 1:29am

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 · June 14, 2012, 1:34am

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 · June 14, 2012, 1:48am

Try it without the {}

tom8 · June 14, 2012, 1:49am

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 · June 14, 2012, 2:36am

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 · June 14, 2012, 3:05am

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

r937 · June 14, 2012, 3:13am

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

i suspect the string values are missing the single quotes

immohito · June 14, 2012, 3:27am

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 · June 14, 2012, 4:51am

I don’t know if this will fix it or not, but I removed the and used ' and changed the "WHEREprojects.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 · June 14, 2012, 7:20am

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 ... ";

[B][COLOR="#0000FF"]echo $update_sql[/COLOR][/B] 

$update_result = mysql_query($update_sql);

immohito · June 14, 2012, 7:44am

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 · June 14, 2012, 8:03am

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 · June 14, 2012, 8:21am

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 · June 14, 2012, 8:21am

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

r937 · June 14, 2012, 8:30am

the single quotes around the guid are needed

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

immohito · June 14, 2012, 9:40am

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 · June 14, 2012, 10:14am

that thing in red there, that needs quotes around it

tom8 · June 14, 2012, 10:18am

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 · June 15, 2012, 12:48am

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.