problem of insert multiple values with WHERE NOT EXISTS

Question

I want to insert multiple records from the form into table named 'user' with the 'items' value submitted from the form do not exist in another table name 'order'. There is only 2 field in the 'order' table that is id and items.

Here is my code:

INSERT INTO user (id, username, email, address, items)
SELECT '$username1', '$email1', '$address1', '$items1'
UNION ALL
SELECT '$username2', '$email2', '$address2', '$items2'
UNION ALL
SELECT '$username3', '$email3', '$address3', '$items3'
FROM DUAL 
WHERE NOT EXISTS(SELECT item FROM order)

Lets say 'items' table contain 2 set it data:

  id        items
1         table01 
2         chair01

So when I insert data with:

john, john@hotmail.com, 12, Street, nail01 
jennifer, jennifer@yahoo.com, 13, Street, table01
peter, peter@live.com, 14, Street, spoon01
(defintely these data are keyin in the form)

Supposely the second record will not insert into 'user' table since in 'items' table already contain this item. But now the result is the 1st and 2nd data will insert into 'user' table while 3rd data is not. I try with many times with other code but is still not success.

Can anyone give me a suggestion? Thanks.

lukehillonline · Answer 1 · 2011-09-23T10:24:02.387

I am a little confused with what you are trying to do, correct me if I'm wrong but this is what I understand from your question:

You want to add data into a Users table from a form, but you only want to add user which have order an item that does not already exist in the items table?

------ This now confirmed ------

Right well this is how I would go about it.

so you are collecting the data from the form using the POST method.

$_POST['email'] and so on. You want to make sure that the data collected is 100% clean so I use a handly little cleanString function I created...

function cleanString($var)
{
    $var = strip_tags($var); //Removes all HTML tags
    $var = htmlentities($var); //Converts characters into HTML entities
    $var = stripslashes($var); //Removes any backslashes
    return mysql_real_escape_string($var); // Escapes all special characters
}

and would use it like this...

$email = cleanString($_POST['email']);
$item = cleanString($_POST['item']);

etc...

with all of your clean data you now want to check if the item exists from the order table. so do a query along these lines:

$query = "SELECT items FROM order WHERE items=".$item;
$result = mysql_query($query);

Then check if a result was found

if(mysql_num_rows($result) > 0)
{
    // Result found do not process...
}
else
{
    // Result not found, process...
    $query = "INSERT INTO user ...";
}

if you are doing multiple inserts in one go you can wrap this all in a while or foreach loop to process it all in one go. although considering it coming from a form I guess you will only have 1 input at a time?

hope this helps.

------- adding a while loop ------

so you are wanting to add multiple records in one go, this entirely depends on how you are collecting the data from the form, to give an exact answer I will need a bit more information about how you are collecting the data from the form. Do you have 3 of each field to add 3 people at once, or are you saving all the data to an array for processing later?

something along these lines will work if you are putting everything into an array

foreach ($array as $user)
{
    $item = $user['item']
    etc...

    $query = "SELECT items FROM order WHERE items=".$item;
    $result = mysql_query($query);

    if(mysql_num_rows($result) > 0)
    {
        // Result found do not process...
    }
    else
    {
        // Result not found, process...
        $query = "INSERT INTO user ...";
    }
}

This will take each user one by one, get all the data for that user, check if the item exists and then process if it does not exist.

You can also do it with a while loop, but this way is hard to explain without knowing how you are acquiring the data

i need to insert multiple records is that mean i need to write $query = "SELECT items FROM order WHERE items=".$item1 OR items=".item2 OR items=".item3; and so on? — temperance, Sep 23 '11 at 10:08
if you have multiple records to add then if you wrap the whole thing in a while loop then for each set of data it will perform a check and then either process or not process. - ill edit my answer to try and explain — lukehillonline, Sep 23 '11 at 10:14
actually what I want is mostly for multiple insertion from a form. Example like total records I want to insert is 5 but there is 1 item is found in the 'order' table so I need to skip that but still will insert the another 4 records that didnt match with the record in 'order' table.. For the mean while I need to inform that which record is inserted and which is not inserted.. Any ideas about this? — temperance, Sep 23 '11 at 10:17
that can easily be done within the if statement, if processed then you can add the username to an array or variable to be put out on the screen later on — lukehillonline, Sep 23 '11 at 10:25

Tom · Answer 2 · 2011-09-23T10:35:02.460

This seems like Oracle syntax... Is it?

INSERT INTO user (id, username, email, address, items)

You require id to be inserted but do not do this. That is okay if it is populated by a sequence and trigger, but don't require it to be inserted.

INSERT INTO user (username, email, address, items)
SELECT '$username1', '$email1', '$address1', '$items1'
UNION ALL
SELECT '$username2', '$email2', '$address2', '$items2'
UNION ALL
SELECT '$username3', '$email3', '$address3', '$items3'
FROM DUAL 
WHERE NOT EXISTS(SELECT item FROM order)

You need 'FROM DUAL' in each union-part
Your WHERE-clause only affects the 3rd select
It also makes not much sense: 'select this row if there are no rows in the table order'. You will need to specify your exclusion. I added a join (WHERE a.items = items), so now a row will only be inserted when there exists no row in the order table where the item already exists. It seems dodgy, you need to provide more info on this.

Probably you meant

INSERT INTO user (username, email, address, items)
SELECT username, email, address, items FROM (
   SELECT '$username1' username, '$email1' email, '$address1' address, '$items1' items
     FROM DUAL
   UNION ALL
   SELECT '$username2' username, '$email2' email, '$address2' address, '$items2' items
     FROM DUAL
   UNION ALL
   SELECT '$username3' username, '$email3' email, '$address3' address, '$items3' items
     FROM DUAL
) a
WHERE NOT EXISTS(SELECT 1 FROM order WHERE a.items = items)

If your table 'order' has a Unique key defined on the 'items' column, then yes, your insert will fail when you try to insert a new item. A better practice would be to first query your order-table to check if this item already exists, and if not, create it.

Like AdriftUniform says, clearer info is needed!

Im using for php not for oracle..So no need to use INSERT But SELECT ?what is 'a' stand for?Im newbie to php and mysql..Thanks for help! — temperance, Sep 23 '11 at 10:12
a is an alias for the select statement subquery, and necessary to reference it in the 'not exists'-clause. Oh, and you need insert of course, my select is simply the subset you would use for your insert statement. Ill edit. Although i don't think my answer is relevant anymore, after you cleared it up with AdriftUniform! — Tom, Sep 23 '11 at 10:33

problem of insert multiple values with WHERE NOT EXISTS

2 Answers2