PHP MySQL, IF EXISTS update ELSE insert, Dosn't seem to work

Question

I'm trying to get this code to update if the parameter exists, however, when I execute nothing happends (It does say done, and update products, but the update of products_description, dosn't seem to work. The code is here:

    $query =   "UPDATE products 
            SET products_reference='$varenummer', products_price='$pris'
            WHERE products_id='$id';";

            for ($x = 1; $x <= $amountOfLanguages; $x++) {
                $varenavn = $_POST["varenavn-" . $x];
                $korttekst = $_POST["korttekst-" . $x];
                $langtekst = $_POST["langtekst-" . $x];

                $query .=  "IF EXISTS (SELECT * FROM products_description WHERE products_id='$id' AND languages_id='$x')
                                UPDATE products_description
                                SET products_description_name='$varenavn', products_description_short_description='$korttekst', products_description_description='$langtekst'
                                WHERE products_id='$id'
                                AND languages_id='$x'
                            ELSE
                                INSERT INTO products_description (products_id, languages_id, products_description_name, products_description_short_description, products_description_description)
                                VALUES ('$id', '$x', '$varenavn', '$korttekst', '$langtekst');";
            } 

            if ($conn->multi_query($query) === TRUE) {
                //header("Location: ./");
                //die();
                echo "done";
            } else {
                echo "Error: " . $query . "<br>" . $conn->error;
            }

And I can't really use ON DUPLICATE, since I don't know the unique key, I only know "products_id" and "languages_id".

Thanks in advance.

You should really make use of ON DUPLICATE. What do you mean by "I don't know the unique key"?. — Lund, Jul 30 '15 at 12:09
Sidenote: The manual for multi_query http://php.net/manual/en/mysqli.multi-query.php shows the procedural style example as not containing a semi-colon in the last concatenated query. It may not completely fix your code, but you should try removing it `'$langtekst');";` to `'$langtekst')";` - Just in case. — Funk Forty Niner, Jul 30 '15 at 12:12
What I mean is that I (the cide) only know products_id and languages_id, and not the prodcuts_description_id, which is the primary key for this table, and I can't really make any of the others unique, since there is multiple languages, aswell as descriptions realted to that products_id. — Mathias_, Jul 30 '15 at 12:15

Professor Abronsius · Answer 1 · 2015-07-30T12:45:31.680

You can use, in mysql, a much neater and compact syntax - that uses 'on duplicate update' - for this sort of situation. The general principal of the query would be along the lines of the following:-

insert into `TABLE` ( `FIELD1`,`FIELD2`, `FIELD3` ) values ( 'VALUE1','VALUE2','VALUE3' )
on duplicate key
    update
        `FIELD1`='VALUE1',
        `FIELD2`='VALUE2',
        `FIELD1`='VALUE3';

Basing this on original query.
------------------------------

insert into `products_description` 
    (`products_id`, `languages_id`, `products_description_name`, `products_description_short_description`, `products_description_description` )
    values
    ( '$id', '$x', '$varenavn', '$korttekst', '$langtekst' )
    on duplicate key
    update
        `products_id`='$id',
        `languages_id`='$x';

Yeah, but how would I use ON DUPLICATE, when checking for two different parameters? I need to check if a record alreay exists where "products_id" and "languages_id" is equal to the one I'm trying to insert? — Mathias_, Jul 30 '15 at 12:23

PHP MySQL, IF EXISTS update ELSE insert, Dosn't seem to work

1 Answers1