7 replies to this topic

[Dat]

This may be a stupid question to ask but what is the difference between using VALUE and SET when inserting data.

This is what I have but in future tutorials that I have read they use VALUE

mysql_query( "INSERT INTO anime_reviews SET title = '".mysql_real_escape_string( $_POST['title'] )."', `title_jap` = '".mysql_real_escape_string( $_POST['japtitle'] )."', `summary_synopsis` = '".mysql_real_escape_string( $_POST['summary'] )."', `genres` = '".mysql_real_escape_string( $_POST['genres'] )."', `episode_number` = '".mysql_real_escape_string( $_POST['episode_number'] )."', `length` = '".mysql_real_escape_string( $_POST['length'] )."', `year_published` = '".mysql_real_escape_string( $_POST['year'] )."', `opening_theme` = '".mysql_real_escape_string( $_POST['openingtheme'] )."', `ending_theme` = '".mysql_real_escape_string( $_POST['endingtheme'] )."', `official_sites` = '".mysql_real_escape_string( $_POST['website'] )."' ")

Also is there a difference in the code I have and this kind:

mysql_query("INSERT INTO `colors` (name, favoriteColor) VALUES ('$name', '$color')");
echo "Success! Your favourite colour has been added!";

Probably the same question (Like I said this could be a stupid question!)

Edited by Dat, 24 August 2007 - 02:02 PM.

[Demonslay]

I have never seen SET used with anything but an UPDATE statement. It may be legal, probably depreciated or something as far as I'm concerned.

The latter is the better practice anyways, as when you use the parenthesis right after the column name {'`colors` (name, favoriteColor)' for example}, it is stating the exact columns you are inserting; something that not only could be useful in inserting only certain columns while letting MySQL use the default value (if applicable) to the others, but it also helps you as a developer keep track of what exactly you are inserting, letting you match the values up respectively. Really comes in handy when you edit the table, so you can keep track whether you edited the query for the new structure or not, lol.

[Dat]

Demonslay, on Aug 24 2007, 02:51 PM, said:

I have never seen SET used with anything but an UPDATE statement. It may be legal, probably depreciated or something as far as I'm concerned.

The latter is the better practice anyways, as when you use the parenthesis right after the column name {'`colors` (name, favoriteColor)' for example}, it is stating the exact columns you are inserting; something that not only could be useful in inserting only certain columns while letting MySQL use the default value (if applicable) to the others, but it also helps you as a developer keep track of what exactly you are inserting, letting you match the values up respectively. Really comes in handy when you edit the table, so you can keep track whether you edited the query for the new structure or not, lol.

So... the second code is a better approach and VALUE is the way to go in popularity. Still wondering if there is a difference in how they work... I don't want to continue coding unless I know the difference between the two and which two different code set is the best way to go for a developer.

[rc69]

As demonslay said, i've never seen an INSERT INTO statement use SET. I believe i accedentally tried that once (having mixed up UPDATE with INSERT) and got an error from MySQL.

The difference? Umm, ya. The best way i can put it is, it's the difference between capitol and capital. You just don't use one when referring to the other.

As a developer, stick with using the VALUES method with INSERT INTO, and SET with UPDATE. You can parouse through Chapter 13 of the MySQL manual to see if they mention any specifics though (note, the link provided is for version 4.1 of mysql, the one most likely to have the info you are looking for, if you want the most up-to-date info, click here).

[Dat]

rc69, on Aug 24 2007, 10:52 PM, said:

As demonslay said, i've never seen an INSERT INTO statement use SET. I believe i accedentally tried that once (having mixed up UPDATE with INSERT) and got an error from MySQL.

The difference? Umm, ya. The best way i can put it is, it's the difference between capitol and capital. You just don't use one when referring to the other.

As a developer, stick with using the VALUES method with INSERT INTO, and SET with UPDATE. You can parouse through Chapter 13 of the MySQL manual to see if they mention any specifics though (note, the link provided is for version 4.1 of mysql, the one most likely to have the info you are looking for, if you want the most up-to-date info, click here).

Thank, I think the best way to proceed with this now is just use the VALUE statement since it seems to be more popular. I wanted to know the difference if was easier to insert data or it inserted data differently. After reading the link you gave me it seemed to describe that it was more explicit.

[Mr. Matt]

I can't beleive you guys have never seen that done before, where I work we do this method all the time, for me it is so much better as I can see which variable is being inserted under each column quicker if your using big queries like the first example given.

For me its not down to better practice, its down to prefrenece, and for me its quicker and easier to use it, example if i need to modify an insert to an update which I am having to do a lot off atm with add/manage scripts, its 100x quicker to just change a few bits around then having to rewrite the query.

But like i said its down to personal prefrence, they both work in the same way and do the same thing.

Matt

[Balor]

Mr. Matt, on Aug 25 2007, 10:35 AM, said:

I can't beleive you guys have never seen that done before, where I work we do this method all the time, for me it is so much better as I can see which variable is being inserted under each column quicker if your using big queries like the first example given.

For me its not down to better practice, its down to prefrenece, and for me its quicker and easier to use it, example if i need to modify an insert to an update which I am having to do a lot off atm with add/manage scripts, its 100x quicker to just change a few bits around then having to rewrite the query.

But like i said its down to personal prefrence, they both work in the same way and do the same thing.

Matt

I can just approve this, I'm also using SET in inserts all the time and it's working very well for me. I personally also don't like adding always 2 things to it, this way I can easily update it. On big sql queries it seems also more comfortable to me.

[Dat]

Mr. Matt, on Aug 25 2007, 01:35 AM, said:

I can't beleive you guys have never seen that done before, where I work we do this method all the time, for me it is so much better as I can see which variable is being inserted under each column quicker if your using big queries like the first example given.

For me its not down to better practice, its down to prefrenece, and for me its quicker and easier to use it, example if i need to modify an insert to an update which I am having to do a lot off atm with add/manage scripts, its 100x quicker to just change a few bits around then having to rewrite the query.

But like i said its down to personal prefrence, they both work in the same way and do the same thing.

Matt

Thanks that really answered my question.