php - Incorrect Value Being Retrieved from the Database -

situation: created cron job. add orders entered customers api. set run after every 1 minute.

problem: sometimes, when there many pending orders, second cron job being called before first 1 gets ended. resulting in duplicate orders sent through api.

solution: created table named cron_jobs in database. has 2 columns, id , status. when cron job runs, checks whether status 0 or not. if status 0, updates status 1. completes process , marks status again 0. if status of job 1 (it means running), , hit again, operation terminated.

unexpected trouble: solution seems logically correct. when implemented it, results shocking. when job run, status updated. when invoked again, database still returns status 0 , keeps on executing. have used sleep method have enough time investigate issue. database returns incorrect value can see using phpmyadmin.

please no think might have committed stupid mistakes. second instance of file fetching incorrect value before first instance invoked. have @ code:

<?php $output = mysql_fetch_assoc(mysql_query("select status jobs id='1'")); if($output['status'] == '0') {     mysql_fetch_assoc(mysql_query("update jobs set status='1' id = '1'"));     mysql_fetch_assoc(mysql_query("update jobs set invoked = invoked+1 id = '1'"));     echo 'executed'; } else {     echo 'error: cron job in process. operation terminated!';     die(); }  /*i perform lengthy tasks here*/      /*sleep function called check whether instance of program works while 1 in progress */     sleep(60);      /*task complete. marking 0 status instance allowed work on.*/      mysql_fetch_assoc(mysql_query("update jobs set status='0' id = '1'")); ?> 

observation 1: works if run second instance in browser or computer.

observation 2: tried investigate whether being called several times or not. made column in table "no_of_times_invoked". , found code failed when used same browser.

your select query uses table cron_jobs. every other query uses jobs. believe select query fails, causing mysql_query() return false.

$output = mysql_fetch_assoc(mysql_query("select status cron_jobs id='1'")); 

because mysql_query() returns false here, mysql_fetch_assoc() returns false.

if($output['status'] == '0') 

$output false. in php, when using ==, type conversion can take place. in case, $output['status'] == '0' evaluates true. (note $output['status'] null, null == '0' evaluates false. joys of type juggling , loose comparisons.)

replace query with

select `status` jobs id = '1' 

and should run ok.

i recommend add more error checking return values of mysql_*() functions (use $returnvalue === false) or, better, switch pdo.

---

updated after discussion in comments

it seems problem you're testing browser. @ least firefox 23.0.1 (which used testing) not send second request until first request complete. explain behavior you're seeing. new browser or computer not able re-use connection in case works.

if launch script command line twice (five seconds apart), works correctly: second run outputs error: cron job in process. operation terminated!.

note php complain invalid mysql_fetch_assoc() have around each mysql_query("update ...") function call:

warning: mysql_fetch_assoc() expects parameter 1 resource, boolean given in /test/test.php on line 7