field info not being inserted in database

[paulmo]

this code is in a process.php page to get form info inserted to database table; have a mysql table with 'name' and 'theme' fields (xxx's only posted here), but form info is not being inserted into database table fields.

 

the 'name' form field is text and 'theme' is radio button; i've set them both up as varchar in database table. both fields are in one row in database table. have set 'name' as primary because mysql had a warning that a primary index should be created.

 

help? thanks

 

mysql_connect("xxx", "xxx", "xxx") or die(mysql_error()); 
mysql_select_db("xxx") or die(mysql_error()); 
$name = mysql_real_escape_string($_POST['name']);
$theme = mysql_real_escape_string($_POST['theme']);
mysql_query("INSERT INTO `interactive` VALUES('name', 'theme')"); 

 

[Maq]

Please echo out $name and $theme to make sure they are the correct values.  Also, make sure "name" and "theme" are field names.

 

 mysql_query("INSERT INTO `interactive` ('name', 'theme') VALUES ('$name', '$theme'")) or die (mysql_error()); 

[burnside]

mysql_query("INSERT INTO `interactive`` (`name`, `theme`) VALUES ('$name', '$theme' ") or die(mysql_error());  

[Maq]

Actually, the field names don't need any quotes...  Sorry bout that.  But it is good practice to use the back ticks like burnside used.

 

[burnside]

Actually, the field names don't need any quotes...  Sorry bout that.  But it is good practice to use the back ticks like burnside used.

 

 

I normaly dont use them lol but i woke up other day and poof its like second nature to add them lol.

 

If that makes any sense ???

[paulmo]

i'm getting this message on website after processing form:

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 'name`, `theme`) VALUES ('Sdf', 'grey'' at line 1

 

tried removing that extra ` after interactive in query, and got similar error message, but just "error at line 1"

 

did confirm 'name' 'theme' are the correct fields in form and in db table. ('Sdf' and 'grey' above are name/themes from form.)

mysql_connect("xxx", "xxx", "xxx") or die(mysql_error()); 
mysql_select_db("xxx") or die(mysql_error()); 
$name = mysql_real_escape_string($_POST['name']);
$theme = mysql_real_escape_string($_POST['theme']);

mysql_query("INSERT INTO `interactive`` (`name`, `theme`) VALUES ('$name', '$theme' ") or die(mysql_error()); 

[PFMaBiSmAd]

Your original query was at least correct from a syntax standpoint. It was probably not working because you have more then the two fields and you must specify the field names if providing values for less than all the fields (and you are missing the $ in front of the two variables.)

 

This is the insert syntax as it applies to what you are doing -

INSERT

    INTO tbl_name (col_name,...)

    VALUES (expr,...)

 

Filling in your specific details -

INSERT 
    INTO interactive (name,theme)
    VALUES ('$name', '$theme')

 

And then putting that into a string in the mysql_query -

 

mysql_query("INSERT 
    INTO interactive (name,theme)
    VALUES ('$name', '$theme')") or die(mysql_error());

 

It often better to form query strings in a variable (it allows you to log them, echo them...) and then just use that variable in the mysql_query() statement.

 

[paulmo]

thanks—success! had another field ('message') in database table, so deleted that and also inserted the ") after 'theme' in query. so not sure which fix did the trick but form data is posting to database table.

 

was previously using same database connection with only one value, 'message' on another page (for a different form). after adding 'name' and 'theme' values to db table, 'message' data stopped posting to db. so can 2 forms with different values apply to same database connection code, which includes all the values from both forms? then have 1 table in database containing all fields/value names? that's what i was trying to do and it didn't work.

 

if that's not possible then do i need to set up a new db table for each form?

 

also, necessary to have primary index? just selected first field ('name') for it and db seems happy.

 

thanks

[PFMaBiSmAd]

Any table would have the columns/fields you need for whatever you are using that table for. If you have name, theme, and message fields that are related, than that table would have those fields. If your message field is not related to the name and theme, then you should have a separate table for the data you are putting into the message field.

 

The database connection is just that, a connection to the database server. What data you insert, update, select.. over that connection is dependent on the database you have selected and on the table the query is operating on.

 

You don't need a primary index, but to prevent duplicate values and to allow your data to be more organized and easily referenced in other related tables you should have at least an autoincrementing primary index. To get specific help with what mysql was complaining about, you would need to post the actual error message and the query that caused that error.

[corbin]

Actually, the field names don't need any quotes...  Sorry bout that.  But it is good practice to use the back ticks like burnside used.

 

 

 

I generally don't use quotes because some other dialects of SQL don't use backticks.  For example, MSSQL uses [].  Every now and then I've had to change the DB type, and removing all of the ` is just a waste of time when they're not needed to begin with.

[Maq]

Actually, the field names don't need any quotes...  Sorry bout that.  But it is good practice to use the back ticks like burnside used.

 

 

 

I generally don't use quotes because some other dialects of SQL don't use backticks.  For example, MSSQL uses [].  Every now and then I've had to change the DB type, and removing all of the ` is just a waste of time when they're not needed to begin with.

 

Thanks for the info.  So it's good practice NOT to use backticks for portability purposes?

[fenway]

You won't need backticks if you don't use special characters or reserved keywords...

[corbin]

Actually, the field names don't need any quotes...  Sorry bout that.  But it is good practice to use the back ticks like burnside used.

 

 

 

I generally don't use quotes because some other dialects of SQL don't use backticks.  For example, MSSQL uses [].  Every now and then I've had to change the DB type, and removing all of the ` is just a waste of time when they're not needed to begin with.

 

Thanks for the info.  So it's good practice NOT to use backticks for portability purposes?

 

 

Like fenway said, they're unneeded.  Just don't be the guy who names his columns time or something like that lol.

 

For portability, backticks are evil.  For example, in MySQL:

 

SELECT `col1`, `col2` FROM `table` WHERE `col1` = 'blah';

 

Would be SELECT [col1], [col2] FROM

WHERE [col1] = 'blah';   In MSSQL.  ([] are optional in MSSQL too.)       So yeah, backticks are bad for portability.  (Although, for any kind of weird or complex query, things will usually have to be changed anyway.)