Is this correct to write sql query inside loop

Question

Assume that there are around 200,000 records in a database. Here do-while may execute up to 200,000 times. It means it will call the DB 200,000 times.

My Question: Is this the correct way to do so? Is there a better way?

function get_new_key() {
    do {
        $new_key = 'some_xyz';
    } while ( function_call_to_check_newKey_exists_in_db );

    return $new_key;
}

why you using loop to check new key exists in db? what is the real time scenario of your logic ? — Suniti Yadav, Apr 17 '18 at 06:27
Not really, it's highly inefficient; but without more information about what you're really trying to do, it's hard to suggest what approach you should take.... e.g. where does new_key come from? — Mark Baker, Apr 17 '18 at 06:27
It's almost never efficient to call db actions in loop. Maybe use `UUID` to create ID's? http://php.net/manual/en/function.uniqid.php — Justinas, Apr 17 '18 at 06:30
here new key means: $new_key = hash('sha256', time() . mt_rand()); (PHP CODE) — Koorma Ashok, Apr 17 '18 at 06:56
If your key column in your MySQL table is set to unique you would get a duplicate error, and you could handle it like `if (mysqli_errno() == 1062) { // generate new key }` and I hope you're using something like https://stackoverflow.com/a/31460273/342740 to generate your random key or a secure yet unique method like that. How many time you have to regenerate the key will depend on how good/unique/secure your algo to generate the key is. — Prix, Apr 17 '18 at 06:57

score 0 · Answer 1 · answered Apr 17 '18 at 06:31

Option 1) Use surrogate key. Natural keys has its uses but surrogate keys have many benefits, flexibiliy for one. Som sql-servers may generate non-sequential auto-generated surrogate keys.

Option 2) Use http://php.net/manual/en/function.uniqid.php and trust a try- and catch scenario.

Overall, since you're pumping 200.000 records(?), you may be better of with placing them in a text-file and use an import function at the db.

score 0 · Answer 2 · answered Apr 17 '18 at 06:44

You might do well to determine how the new_key is generated. Without this information there's little helping you.

If it is, for example, an incremental number, then it would be better to choose MAX(columnkey)+1 (with the table locked to prevent conflicts), or better still, declare it PRIMARY KEY AUTO_INCREMENT and insert the NULL value for the key. This will set the key automatically. Recover its value with LAST_INSERT_ID():

INSERT INTO ... (all columns except key) VALUES (all values)
SELECT LAST_INSERT_ID() AS newkey;

Or you could use a timestamp of sufficient granularity.

If you want to avoid "holes" in the numbering (not necessarily a bad practice, but please have a better reason to do so than "it looks nicer"!), you can LEFT JOIN the table to itself on condition that the right-hand table ID is one greater than the left-hand side. Those rows where the right side is NULL indicate that their counterpart ID is available (again, lock the tables - in this case, with MySQL, twice):

-- You need an index on mykey
SELECT a.mykey+1 AS available 
    FROM mytable AS a 
    LEFT JOIN mytable AS b ON (a.mykey+1 = b.mykey)
    WHERE b.mykey IS NULL
    ORDER BY a.mykey LIMIT 20

Is this correct to write sql query inside loop

2 Answers2