mysql - SQL/PHP Conditional SELECT and UPDATE -

i'm trying return 5 unused values codes column of table below , set used in related row 1.

|--- codes ---| | used | | fiomqvu71l  | |   0  | | 4tw0lwlwnk  | |   0  | | sjzlb2shzr  | |   0  | | utwjrtcgh4  | |   0  | | tlwowygz5r  | |   0  | | byehzymwjg  | |   0  | | xfbmgzdgir  | |   0  | 

i've managed code working output 5 random values codes used = 0

<?php $sql = "select codes code_table used =0 order rand() limit 5"; $records = mysql_query($sql, $connection); while($rows = mysql_fetch_array($records)){ echo "code: " . $rows['codes'] . "<br />"; ?> 

but i'm lost how update used value each output codes. of attempts have updated every single instance of used 1 rather instances associated 5 codes

you store rows in temporary table. note not entirely concurrency safe. if query comes between insert , update, might grab same rows.

create temporary table if not exists tmp_codes (codes varchar(50));  -- 5 new rows insert tmp_codes         (codes) select  codes    code_table   used = 0 order         rand() limit   5;  -- update 5 rows update  code_table set     used = 1   codes in         (         select  codes            tmp_codes         );  -- return application select  codes    tmp_codes;  drop table tmp_codes; 

example @ sql fiddle.