inserting all entries stored in an array (for statement)

Question

I have all of my keys stored in the array $addkeys (the total amount of keys always changes). The var_dump($addkeys) outputs:

Arrayarray(9) { [0]=> string(17) "11111-11111-11111" [1]=> string(17) "22222-22222-22222" [2]=> string(17) "33333-33333-33333" [3]=> string(17) "44444-44444-44444" [4]=> string(17) "55555-55555-55555" [5]=> string(17) "66666-66666-66666" [6]=> string(17) "77777-77777-77777" [7]=> string(17) "88888-88888-88888" [8]=> string(17) "99999-99999-99999" }

I want to insert each of them into the database if they don't already exist. I understand how to check for a single existing key in a string and do an INSERT if it doesn't already exist, but the incremental loop confuses me. Any help would be appreciated. I understand people might recommend using PDO for the mysql queries, but please disregard.

My code:

for ($i = 0; $i < count($addkeys); $i++) {

// check each key in $addkeys to see if it exists already in the database to prevent a duplicate entry

$result = mysql_query("SELECT code FROM codes WHERE code = '$addkeys'")
or die(mysql_error());
$row = mysql_fetch_array($result, MYSQL_NUM);
$addkeys = $row[0];

if(!isset($addkeys)) {
// entry doesn't exist insert the key
$result = mysql_query("INSERT INTO codes (code, title) VALUES ('$addkeys', 'sample title')")
or die(mysql_error());

}
}

is code a keyed column in the db?

Professor Abronsius
– Professor Abronsius

2016-01-22 07:33:57 +00:00
Commented Jan 22, 2016 at 7:33 — Professor Abronsius
– Professor Abronsius, Commented Jan 22, 2016 at 7:33
You don't need to check first

Strawberry
– Strawberry

2016-01-22 07:40:35 +00:00
Commented Jan 22, 2016 at 7:40 — Strawberry
– Strawberry, Commented Jan 22, 2016 at 7:40

Saty · Accepted Answer · 2016-01-22 07:51:10Z

1

You have to use mysql_num_rows to check data already store in your database or not. and you are use direct array addkeys in your query you have to use it like addkeys[$i]

Your code would be

for ($i = 0; $i < count($addkeys); $i++) {

// check each key in $addkeys to see if it exists already in the database to prevent a duplicate entry

    $result = mysql_query("SELECT code FROM codes WHERE code = '$addkeys[$i]]'") or die(mysql_error());
    $rows = mysql_num_rows($result);

    if ($rows < 1) {
// entry doesn't exist insert the key
        $insert = mysql_query("INSERT INTO codes (code, title) VALUES ('$addkeys[$i]]', 'sample title')") or die(mysql_error());
    }
}

Note:- mysql is depricated instead use mysqli OR PDO

edited Jan 22, 2016 at 7:51

answered Jan 22, 2016 at 7:36

Saty

22.5k7 gold badges35 silver badges52 bronze badges

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

6 Comments

michelle Over a year ago

Thanks. I really need to learn more about loops. Do you have a resource I could read?

Mark Manning Over a year ago

Could that be made into a single SQL query? Like "insert into codes (code,title) values('$addkeys[$i]], 'sample title') where (select code from codes where code ='$addkeys[$i]) = 0" ???

Strawberry Over a year ago

What if someone inserts a key while you're checking? And never execute a query inside a loop.

Mark Manning Over a year ago

That is more likely to happen with TWO SQL queries than one. Usually a database will auto-lock a record when it gets an action on it. But the example given allows the database to unlock the record after the SELECT and before the INSERT.

Saty Over a year ago

@MarkManning have you check the syntax of query that you have suggeted!!

|

Professor Abronsius · Accepted Answer · 2016-01-22 07:42:59Z

1

if you have a keyed column you could use the on duplicate key update syntax

<?php
    foreach( $addkeys as $key ){
        $title='sample title';

        $result = mysql_query("INSERT INTO codes (`code`, `title`) VALUES ('{$key}', '{$title}')
            on duplicate key update `code`={$key}, `title`='{$title}'");    
    }
?>

answered Jan 22, 2016 at 7:42

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

Collectives™ on Stack Overflow

inserting all entries stored in an array (for statement)

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related