Jump to content


Photo

mysql syntax error


  • Please log in to reply
8 replies to this topic

#1 elginwick

elginwick
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 27 April 2006 - 07:12 PM

I am trying to load multiple pieces of information from a form that is submitted.

for some reason i am getting an errror in one place, but not in the other place.

example

//start code

$add_master = "INSERT INTO master_name values ('', now(), now(), '$_POST[f_name]', '$_POST[l_name]',
'$_POST[dob]', '$_POST[ss]', '$_POST[session]') address values ('', '$_POST[address]', '$_POST[city]',
'$_POST[state]', '$_POST[zipcode]')";
mysql_query($add_master) or die(mysql_error());


//end code

there are no errors for this script

but then in this script

//start code

7 if (($_POST[address]) || ($_POST[city]) || ($_POST[state]) || ($_POST[zipcode])) {
8 $add_address = "insert into address values ('', '$_POST[address]', '$_POST[city]',
9 '$_POST[state]', '$_POST[zipcode]')";
10 mysql_query($add_address) or die(mysql_error());
11 }

//end code


there is an errror
here is what it says.

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 'address values ('', '111 Main St.', 'san franscisco', 'ca', '90215')' at line

I am using mysql 4.0.16

Not sure what to do, any help would be greatly appreciated.

thanks in advance

#2 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 27 April 2006 - 08:22 PM

I think its to do with your blank value you are sending to MySQL. Try this instead:
$add_address = "INSERT INTO address VALUES (NULL, '$_POST[address]', '$_POST[city]', '$_POST[state]', '$_POST[zipcode]')";


#3 sanfly

sanfly
  • Members
  • PipPipPip
  • Advanced Member
  • 344 posts
  • LocationNew Zealand

Posted 27 April 2006 - 08:25 PM

probably an escaped characters problem. you should use addslashes() before inserting text data into your database

[a href=\"http://php.inspire.net.nz/manual/en/function.addslashes.php\" target=\"_blank\"]addslashes() in the php Manual[/a]
If you're not part of the solution, you're part of the precipitate

#4 koencalliauw

koencalliauw
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 27 April 2006 - 08:35 PM

the correct syntax for the insert query is:

insert into mytable(field1,field2) values(value1,value2);

you forgot the (field1,field2) part.

Koen

#5 sanfly

sanfly
  • Members
  • PipPipPip
  • Advanced Member
  • 344 posts
  • LocationNew Zealand

Posted 27 April 2006 - 08:37 PM

you dont have to add that if you are adding the values to the fields in the same order as the fields are listed in the database table
If you're not part of the solution, you're part of the precipitate

#6 koencalliauw

koencalliauw
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 27 April 2006 - 08:41 PM

I agree, but given the error mysql produces, it is however most probably this that produces the error, not to mention that it is horrible coding practice to not specify the fields (suppose you do this everywhere in your script and you need to add a field to your table, you would need to recode all your queries as this can only be used if the value count is the same as the total field count for the table)

#7 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 28 April 2006 - 12:02 AM

Instead of using addslashes() use mysql_real_escape_string() which will work even if you use a weird character encoding.

To the OP, you might want to try the alternative syntax:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']insert[/span] into tablename set `fieldname1` = 'value'. `fieldname2` = 'value'; [!--sql2--][/div][!--sql3--]
This way you always know what you're setting, you can leave out fields, and if you add a field to your database you might not have to add that field to all the mysql statements.

Ken

#8 elginwick

elginwick
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 28 April 2006 - 12:35 PM

I tried all those things but the error is still exactly the same. i think i am going to look at a different way of doing it.


thanks for all your help.



#9 bbaker

bbaker
  • Members
  • PipPipPip
  • Advanced Member
  • 127 posts
  • LocationNY

Posted 28 April 2006 - 01:24 PM

I've had a problems before with table names & field names that were pretty "generic" or could be used by or mistaken as something else. By renaming the table and/or field name, the problem was solved.

try renaming your table to addresses or user_address or somthing similar. & of course, update your query to reflect the same. I'm not guaranteeing that it'll work for you, but it's worth a shot.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users