PHP Assosiative array Insert into if data doesnt exist in table

Question

I have an assosiative array in PHP which is inserting data into a table

foreach($array as $key => $value){
        $query = "INSERT INTO live_list (file_id, date) SELECT ('$key', '$value') FROM dual WHERE NOT EXISTS (SELECT * FROM live_list WHERE file_id = '$key')";
        mysqli_query($link, $query) or die('Query failed: ' . mysqli_error($link));
    }

However as I have moved from an indexed array to an assosiative array, I can't figure out how to insert this data ONLY if the data in my array does not exist in my table.

Query failed: Operand should contain 1 column(s)

Now I am recieving this error

Any help will be great!

I would use mysqli_fetch_assoc( ) to read the data stored in the database and see if it matches with what I intend to insert. If it matches, then don't do the insertion. — user3111737
– user3111737, Commented Mar 11, 2014 at 11:46

harryg · Accepted Answer · 2014-03-11 12:38:39Z

1

Why the use of a sub-query?

Assuming file_id is a unique field (i.e. no duplicates in the whole table), make it a unique index on the table if it isn't already:

ALTER TABLE live_list
ADD UNIQUE (file_id)

and change your query to

INSERT INTO live_list (file_id, date) 
 VALUES ('$key', '$value') 
 ON DUPLICATE KEY UPDATE date = date;

This means that it will simple "update" the value of date to what it already is if it encounters a duplicate key.

Although you should look to bind your parameters to protect against sql injection.

edited Mar 11, 2014 at 12:38

answered Mar 11, 2014 at 12:30

harryg

24.2k47 gold badges135 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Joe Over a year ago

I already had this, but I need to check if the key value exists in my table, if it does I dont want to insert it.

Joe Over a year ago

Thanks I had to change it to a unique field to get this working as you said

harryg Over a year ago

Glad it worked. Do look into prepared statements/bind parameters as I mentioned or your program could be vulnerable. Regardless it is good practise.

Joe Over a year ago

Thanks I will look into this however, I had sorted my values by most recent date, now I have added Unique, it scrambles my file ids in a random order, even though before I insert the records they are sorted using uasort function

harryg Over a year ago

It doesn't matter what order you insert your data in the table. MySQL will sort by the first column in a SELECT query unless you specify a ORDER BY column_name ASC/DESC.

user3332631 · Accepted Answer · 2014-03-11 11:58:45Z

0

You could do this:

INSERT INTO live_list (file_id, date) 
  SELECT '$key', '$value'
  FROM dual
  WHERE NOT EXISTS (SELECT * FROM live_list WHERE file_id = '$key')

DUAL is a dummy table. The logic is that if the sub query does not exist, the dummy table returns one row with the desiered input.

answered Mar 11, 2014 at 11:58

user3332631

4141 gold badge4 silver badges12 bronze badges

5 Comments

Joe Over a year ago

I tried this and I got a parse error Parse error: syntax error, unexpected 'mysqli_query' (T_STRING)

Joe Over a year ago

$query = "INSERT INTO live_list (file_id, date) SELECT ('$key', '$value') FROM dual WHERE NOT EXISTS (SELECT * FROM live_list WHERE file_id = '$key')"

Joe Over a year ago

Ahh no semi colon haha.

Joe Over a year ago

Now I have Query failed: Operand should contain 1 column(s)

user3332631 Over a year ago

Maybe you have two identical file_id? Try adding a limit to the sub query: INSERT INTO live_list (file_id, date) SELECT '$key', '$value' FROM dual WHERE NOT EXISTS (SELECT * FROM live_list WHERE file_id = '$key' LIMIT 1)

Collectives™ on Stack Overflow

PHP Assosiative array Insert into if data doesnt exist in table

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related