patheticsam Posted March 12, 2009 Share Posted March 12, 2009 Hi! I have a little update satement and I get an SQL error and can't seem to find what is the problem. Here's the statement if anyone can help me out : <?php $con = mysql_connect("localhost","user","pass"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("table", $con); mysql_query(" UPDATE moving SET `firstname` = '{$_POST['firstname']}', `lastname` = '{$_POST['lastname']}', `email` = '{$_POST['email']}', `movedate` = '{$_POST['movedate']}', `adress1` = '{$_POST['adress1']}', `adress2` = '{$_POST['adress2']}', `app1` = '{$_POST['app1']}', `app2` = '{$_POST['app2']}', `city1` = '{$_POST['city1']}', `city2` = '{$_POST['city2']}, `prov1` = '{$_POST['prov1']}', `prov2` = '{$_POST['prov2']}', `ind1` = '{$_POST['ind1']}', `phone1` = '{$_POST['phone1']}', `ind2` = '{$_POST['ind2']}', `phone2` = '{$_POST['phone2']}', `postal1` = '{$_POST['postal1']}', `postal11` = '{$_POST['postal11']}', `postal2` = '{$_POST['postal2']}', `postal22` = '{$_POST['postal22']}' WHERE `id` = '{$_POST['id']}'") or die(mysql_error()); echo "<center><font face=arial size=2>Update Sucessful</font></center>"; ?> Any help would be be greatly appreciated! Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/ Share on other sites More sharing options...
patheticsam Posted March 12, 2009 Author Share Posted March 12, 2009 here's de SQL error: 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 'prov1', `prov2` = 'prov2', `ind1` = '111', `phone1` = '1111111', `ind2` = '222',' at line 1 Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783372 Share on other sites More sharing options...
patheticsam Posted March 13, 2009 Author Share Posted March 13, 2009 nobody knows? Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783467 Share on other sites More sharing options...
npsari Posted March 13, 2009 Share Posted March 13, 2009 Hmm, i would do it differently actually... <? //// First, you create the variables $get_id = {$_POST['id']} ; $get_firstname = {$_POST['firstname']} ; $get_lastname = {$_POST['lastname']} ; /////AND SO ON... //// Second, you update each one on its own mysql_query(" UPDATE moving SET firstname = '$get_firstname' WHERE id = '$get_id' ") ; mysql_query(" UPDATE moving SET lastname = '$get_lastname' WHERE id = '$get_id' ") ; /////AND SO ON... ?> You see what i mean? This will work i think, but note that i have a unique way of writing my codes, so, you can wait for another answer which can be easier than mine. Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783468 Share on other sites More sharing options...
trq Posted March 13, 2009 Share Posted March 13, 2009 You see what i mean? Each one alone And the point of that would be what exactly? @patheticsam The problem is likely the fact that you not sanitising your input. You need to escape certain chars that will otherwise make sql choke and also open security holes. take a look at mysql_real_escape_string. Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783470 Share on other sites More sharing options...
npsari Posted March 13, 2009 Share Posted March 13, 2009 And the point of that would be what exactly? Yes, i just updated my reply, i mentioned that i write codes in a strange way But the good news are, that they work Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783472 Share on other sites More sharing options...
trq Posted March 13, 2009 Share Posted March 13, 2009 But the good news are, that they work Very inefficiently though. Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783475 Share on other sites More sharing options...
Philip Posted March 13, 2009 Share Posted March 13, 2009 As thorpe said, you need to sanitize your input.... But the query problem lies here: `city2` = '{$_POST['city2']}, (You're missing a ' ) `city2` = '{$_POST['city2']}', Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783476 Share on other sites More sharing options...
npsari Posted March 13, 2009 Share Posted March 13, 2009 Very inefficiently though. You are right, I had a problem sometime ago, which was: A slow server I accused my Scripts and started changing them to become more efficient My server is now faster actually, so, i guess it was my scripts Anyway, i am not a programmer, i am a Mechanical Engineer I am only doing this because i really like having a website Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783480 Share on other sites More sharing options...
grissom Posted March 13, 2009 Share Posted March 13, 2009 You can update more than one field at once, you just have a brain-stretching (for me anyway) job of getting the quotes and the brackets all right. Instead of mysql_query(" UPDATE moving SET `firstname` = '{$_POST['firstname']}', `lastname` = '{$_POST['lastname']}', `email` = '{$_POST['email']}', try this : mysql_query(" UPDATE moving SET firstname = '$_POST[firstname]', lastname = '$_POST[lastname]', email = '$_POST[email]', .. etc ... "); I've removed the single quote marks from off the field names everywhere and also taken out the curly brackets. No guarantees! But type it exactly like that and see if it works ! Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783634 Share on other sites More sharing options...
patheticsam Posted March 13, 2009 Author Share Posted March 13, 2009 As thorpe said, you need to sanitize your input.... But the query problem lies here: `city2` = '{$_POST['city2']}, (You're missing a ' ) `city2` = '{$_POST['city2']}', It was only the missing the ' ..... Thanks a lot! Quote Link to comment https://forums.phpfreaks.com/topic/149182-solved-update-statement-sql-error/#findComment-783686 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.