mysql UPDATE on dropdown boxes

[MadJuggla9]

I have a mysql DB im writing a small update script for. Basically, the page takes in the primary key thru the URL string and queries the DB, then fills each of the form elements (input boxes) in with the info. Changes can be made, then click submit can be clicked and the stuff is then updated in the DB.

Im having problems getting dropdown boxes to work with this. I can add just fine the first time, but the update seems to have problems. It's kind of like this:

Where do you live:
Tennessee
California
Michigan
etc...

When i perform my update query, where i say:
UPDATE myTable SET state=$form_state

I get this: "Unknown column 'Michigan' in 'field list'"
It's like its reading it backwards and trying to put the DB table's column into the variable, then saying that the variable is not a field in my DB's table.

 

[jpmkm]

I believe you need quotes around your variable in the query. Otherwise mysql interprets it as a table field rather than a value.

 

[Fark_Maniac]

if you are doing this via php...try

$action = "UPDATE myTable SET state=".$form_state.";";

bascially, concatonating $form_state (guessing should be $from_state)...concatonating a semicolon for the mysql statement..and ending with a semicolon for php.

 

[MadJuggla9]

I believe you need quotes around your variable in the query. Otherwise mysql interprets it as a table field rather than a value.

My other variables work just fine in the form, input boxes for instance.

Fark, I'm not sure I follow the concatenation purpose? Those are examples, I was referring to the form's state(i see your description too now that i think of it); i also would have stuff like form_name, form_address etc...

I am using PHP, forgot to mention.

 

[kju1]

Could you post the portion of your code in code tags that is giving the error?

You probably want to do something like this:

$query = "UPDATE myTable SET state = '" . $state_name . "' WHERE id = " . $fromurl;

You shouldnt need to terminate your query with a ; when using php since the built in php-mysql function does this.

If I get this correctly this is what you are trying to do:

user visits the site and clicks on some link.
said link contains an ID (primary key) which correlates to a user in your table.
the script builds the page with all the users info
user selects a field (ie state for examle) and modifies it.
user hits submit. the script processes the changes and issues update commands to the db.

if this is correct then the above should work for you.

 

[Fark_Maniac]

You shouldnt need to terminate your query with a ; when using php since the built in php-mysql function does this.

I did not know this...I'll have to update my pages.

http://us3.php.net/function.mysql-query

 

[DragonNOA1]

I just recently had the same error. I solved it by putting single quotes around the php variable name, ie "WHERE table LIKE '$var' "

 

[memphis_1220]

I just recently had the same error. I solved it by putting single quotes around the php variable name, ie "WHERE table LIKE '$var' "

True, when the data type is a string you HAVE to add single quotes due to the syntax for an sql statement.

 

[MadJuggla9]

My code is at work, I should have called to FTP it up today. All of my other variables are strings as well and they dont need quotes. I will definately give it a try but in every other language ... ever ... you can use quotes for direct comparisons using literals, however this is a variable, anywho, I will give it a try asap, thanks

 

[memphis_1220]

My code is at work, I should have called to FTP it up today. All of my other variables are strings as well and they dont need quotes. I will definately give it a try but in every other language ... ever ... you can use quotes for direct comparisons using literals, however this is a variable, anywho, I will give it a try asap, thanks

I am talking from experience with JDBC and SQL. For example:

Java variables

int id;
String name;

id=5, name="MyName";

therefore in order to execute an SQL UPDATE, the following would be needed:

.executeUpdate("UPDATE table SET ID=" + id + ", Name=   '   "   + name +   "   '   WHERE ID=5");

NOTE: I have expanded the quotes and apostrophes just so that they can be seen.
Basically, the apostrophes are for SQL's sake and the quotes are for Java's sake.

Hope this helps

 

[MadJuggla9]

I am talking from experience with JDBC and SQL. For example:

Java variables

int id;
String name;

id=5, name="MyName";

therefore in order to execute an SQL UPDATE, the following would be needed:

.executeUpdate("UPDATE table SET ID=" + id + ", Name=   '   "   + name +   "   '   WHERE ID=5");

NOTE: I have expanded the quotes and apostrophes just so that they can be seen.
Basically, the apostrophes are for SQL's sake and the quotes are for Java's sake.

Hope this helps

In SSI there arent variable types so to speak for most non-abstract variables, therefore a string is treated like and int and so forth. typecasting would be used if i were to have different types. All of my variables are text/ints/whatever as far as the script is concerned.

The only thing that i can think of now that you brought up that point is that the DB has the declared types so maybe adding single quotes would help seeing as it may be expecting an explicit string variable. I appreciate all the help, i now see how this may help. Im going to die if i dont get to my code soon (tuesday)

I still dont get why the error is as it is ... it has nothing do do with what the resolution may be it seems. Anyways, Ill try it asap and thanks for the continued support.

 

[memphis_1220]

Yeah, i put the java variables there for show. I do mean the DB entry types, ie number, text, date, etc.

 

[Fark_Maniac]

when I was initially having problems with my php/mysql code, I did the following.

- ran what I wanted via a mysql client first...just to make sure my mysql syntax was correct.
- once mysql syntax was correct, I commented out any real action in my php and just echo'd out what I want my action to be. This will guaruntee that my php is sending the correct command to mysql.
- once my php is behaving to what the mysql won't compain about, I comment out my echo line and create my action/query command in the php...which should result in a correct and error free result...just for kicks, I may also tack on a "or die(mysql_error())" after the query to let me know what the hell happened.

 

[MadJuggla9]

basically, if i have html/css on my verification page it doesnt set the session vars like it should!?!? commented lines are the ones i seems to have problems with if i add them ...


bump, I don't get it