0

I want to Insert the values only if they are not already exits. My table is called wp_all_importand my values called pid, price, price_old, shopand link.

Can anybody please tell me whats wrong here? Because he inserts the values nevertheless they exits already...

INSERT INTO `wp_all_import` ('$pid', '$price', '$price_old', '$link', '$shop') 
        SELECT 'pid', 'price', 'price_old', 'shop', 'link' FROM `wp_all_import` 
        WHERE NOT EXISTS (SELECT * FROM `wp_all_import` 
        WHERE (pid = '$rpid') AND (price = '$rprice') AND (price_old = '$rprice_old') AND (shop = '$rshop') AND (link = '$rlink'))

Greetings and Thank You!

Jan
  • 257
  • 1
  • 3
  • 12
  • Aren't you sure the problem isn't that you are using single quote `'` instead of double quotes `"`? It is taken as a literal with single quotes. – zed Feb 12 '17 at 12:35
  • Plus why are you using different variables for the same field, as in `$pid` and `$rpid`? – zed Feb 12 '17 at 12:36
  • Now I see that. The variables should all be with an r before the name... But this is still not fixing my problem... – Jan Feb 12 '17 at 13:16
  • 1. do you want to insert from select or values (`$id`)? you can't do both. 2. you can't `INSERT ... WHERE`. It does not make sense. You insert into a table, and you either specify id's, or an auto-increment decides it. 3. `WHERE NOT EXISTS` is a condition for searches, for inserts you would use `ON DUPLICATE UPDATE` or `ON DUPLICATE` nothing. 4.OMG sql injection, please learn about parameterized queries, right now, before coding anything else. If you have code in production you owe it to your users to fix that before adding any new feature. – Félix Gagnon-Grenier Feb 12 '17 at 15:31
  • 6. do you have error reporting? that query is wrong, it will not even run. You should be receiving error messages. If not, enable error reporting. If you do receive, please read them and address what they say. – Félix Gagnon-Grenier Feb 12 '17 at 15:37

1 Answers1

0

i didn't really understood your code but i think this is what you are looking for:

i considered that variables (starting with $ sign) represents the values you want to insert

INSERT INTO wp_all_import(pid, price, price_old, shop, link)
SELECT * FROM (SELECT $pid, $price, $price_old, $shop, $link) AS tmp
WHERE NOT EXISTS (
    SELECT * FROM wp_all_import 
    WHERE pid= $pid AND price = $price 
    AND price_old = $price_old AND link = $link
    AND shop = $shop) LIMIT 1;
Yahfoufi
  • 1,826
  • 1
  • 17
  • 36
  • What is name by select? – Jan Feb 12 '17 at 13:09
  • @Jan it was a mistake updated my answer – Yahfoufi Feb 12 '17 at 13:19
  • 1
    Okay, Thank you. I will try it. But my variables are called with an r before. So I change that and will test it :D – Jan Feb 12 '17 at 13:24
  • Saddly it is not working... `$sql = "INSERT INTO wp_all_import(pid, price, price_old, shop, link) SELECT * FROM (SELECT '$pid', '$price', '$price_old', '$shop', '$link') AS tmp WHERE NOT EXISTS ( SELECT * FROM wp_all_import WHERE pid = '$pid' AND price = '$price' AND price_old = '$price_old' AND link = '$link' AND shop = '$shop') LIMIT 1";` – Jan Feb 12 '17 at 14:03