MySQL insert problem 1

[masterchef]

Hi All

This is my first posting so I hope I am in the correct area! The following code works perfect

INSERT INTO selected_product_parent_options (NAME) SELECT NAME from parent_options WHERE NAME NOT IN (SELECT NAME from selected_product_parent_options)

how ever if I try this
INSERT INTO selected_product_parent_options (F_ID, NAME) VALUES (2, SELECT NAME from parent_options WHERE NAME NOT IN (SELECT NAME from selected_product_parent_options))

I get errors, As you can see I am comparing two table columns and copying all the entry's in NAME form one table to another, this time around I also want to insert a value into F_ID. I have no idea what I am doing wrong, All and any help would be much appreciated.

 

[masterchef]

Update

If I try this I get no error but the second value put the select statement in as a text string not the result of the query

INSERT INTO selected_product_parent_options (F_ID, NAME) VALUES ('2', 'SELECT NAME from parent_options WHERE NAME NOT IN (SELECT NAME from selected_product_parent_options)')

 

[CognosChicago]

masterchef,

You have a single quote in your SQL telling the Insert Statement the Select is a string not more SQL. What was the error you received from your first post, that might help?

 

[CognosChicago]

You can also try this:

INSERT INTO selected_product_parent_options (F_ID, NAME) SELECT '2', NAME from parent_options WHERE NAME NOT IN (SELECT NAME from selected_product_parent_options)

I'm not sure if the F_ID is numeric or string, so you may need to make it 2 instead of '2', if it's numeric instead of string.

 

[masterchef]

I relised I need to extentd the above to get it to work the way I need for my site, I tried this based on the above( which worked great thanks!)

INSERT INTO selected_product_parent_options (F_ID, NAME) (SELECT '{GET.NxT_ID}', NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options AND WHERE F_ID NOT IN (SELECT F_ID FROM selected_product_parent_options))
)"

but I get error

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 'AND WHERE F_ID NOT IN (SELECT F_ID FROM selected_product_parent_.

Thanks

 

[CognosChicago]

Remove the AND before WHERE F_ID NOT IN (SELECT...

I think that might be the issue now.

 

[masterchef]

My god your good! many many thanks!!!

 

[masterchef]

Is all coding like this, fix one problem and another one pops up!

I did the code but I get a duplication of the F_ID which is taken form the url query string, I though by adding the AND if would make sure that both conditions together would make the insert, but as I have removed the AND the NAME is inserted along with the F_ID.

 

[masterchef]

This what I tried to get around it

INSERT INTO selected_product_parent_options (F_ID, NAME) SELECT '{GET.NxT_ID}', NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options WHERE F_ID selected_product_parent_options != '{GET.NxT_ID}')

 

[CognosChicago]

You may just have the ) in the wrong place to use the AND condition.

INSERT INTO selected_product_parent_options (F_ID, NAME) (SELECT '{GET.NxT_ID}', NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options) AND WHERE F_ID NOT IN (SELECT F_ID FROM selected_product_parent_options))

Try this and see what happens.

 

[CognosChicago]

Sorry, forgot to answer your other question, yes, coding can be like this, fix one thing, find another. Code is rarely perfect, just look at Microsoft and their products. They are always fixing things. It's difficult to test for every situation, only those you can think of at the time. SQL code has its own way to doing things and even different database vendors have their own syntax. It can be frustrating and yet very fun.

 

[masterchef]

No it did work i got this
Error:
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 'WHERE F_ID NOT IN (SELECT F_ID FROM selected_product_parent_opti.

what i was trying to do was check if both the F_ID matched the $_GET and if Name was in one table but not this one,

The last working code added the NAME perfect but if I refreshed the page the same entries would go back in. The whole point of the script is to also see if F_ID matches the $_GET value and if NAME is in Parent table but not in selected. So simply put if one exists with out the other the insert works but if both exist in a row it does not insert it.

Is this clear or I just confusing myself!, Many Thanks for sticking with this mate.

 

[masterchef]

tried this
INSERT INTO selected_product_parent_options (F_ID, NAME) (SELECT '{GET.NxT_ID}', NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options WHERE '{GET.NxT_ID}' NOT IN (SELECT F_ID FROM selected_product_parent_options)))

but I still get duplicate entires

 

[CognosChicago]

I think you are close. Is GET.NxT_ID a variable being passed to your SQL?

Try this version:

INSERT INTO selected_product_parent_options (F_ID, NAME)
(SELECT '{GET.NxT_ID}', NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options)
AND WHERE '{GET.NxT_ID}' NOT IN (SELECT F_ID FROM selected_product_parent_options))

 

[masterchef]

no didnt work got this error

Error:
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 'WHERE '2' NOT IN (SELECT F_ID FROM selected_product_parent_optio.

I feel so close to it!

 

[CognosChicago]

Is F_ID a character or numeric field? If it's numeric try removing the quotes around the {GET.NxT_ID}

 

[masterchef]

got this error, and yes F_ID is a numeric field

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 'WHERE 2 NOT IN (SELECT F_ID FROM selected_product_parent_options.

 

[CognosChicago]

I hope this is it:

INSERT INTO selected_product_parent_options (F_ID, NAME)
(SELECT '{GET.NxT_ID}', NAME
FROM parent_options
WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options)
AND '{GET.NxT_ID}' NOT IN (SELECT F_ID FROM selected_product_parent_options))

I think we had one to many WHERE's in the SQL statement.

 

[masterchef]

well good news is no error but this time it will only insert if there is no other name match in the table, it need to be a combination of the both, if the F_ID match and the NAME matches on the same record.

the F_id come from the $_GET and the NAME which is not in another table.

 

[CognosChicago]

Okay then try OR instead of AND before the GET.NxT_ID This way if Name is not in selected is should insert or if GET.NxT_ID is not in selected is should insert. This might be the one.