Mutley Posted May 6, 2007 Share Posted May 6, 2007 I have a cron that updates a field to "yes" where another field says "1", example: user_id | validate | approve 1 | 1 | yes 2 | 0 | 3 | 0 | 4 | 1 | yes At the moment I run it every hour and it updates just the first one, so UPDATE table SET `approve`= 'yes' WHERE validate = '1'; How do I do it to ALL of them with validate=1? Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/ Share on other sites More sharing options...
JakeTheSnake3.0 Posted May 6, 2007 Share Posted May 6, 2007 take out the quotes from 'approve' and see what happens Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-246772 Share on other sites More sharing options...
Mutley Posted May 6, 2007 Author Share Posted May 6, 2007 So that query should update all of them? Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-246773 Share on other sites More sharing options...
Mutley Posted May 6, 2007 Author Share Posted May 6, 2007 At the moment it only runs the query once, so only updates one row, not all of them according to the WHERE statement. Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-246809 Share on other sites More sharing options...
JakeTheSnake3.0 Posted May 6, 2007 Share Posted May 6, 2007 Can't really say...the syntax for the sql query looks correct... "UPDATE table SET approve='yes' WHERE validate='1'"; ...I removed the spaces in between the =.... Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-246814 Share on other sites More sharing options...
Mutley Posted May 7, 2007 Author Share Posted May 7, 2007 No, you misunderstanding. That only updates ONE row, I need it to update everyone where validate='1'. Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247140 Share on other sites More sharing options...
Mutley Posted May 7, 2007 Author Share Posted May 7, 2007 ??? Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247201 Share on other sites More sharing options...
Mutley Posted May 7, 2007 Author Share Posted May 7, 2007 Do I need to do a FOREACH or WHILE thing to achieve this? Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247347 Share on other sites More sharing options...
papaface Posted May 7, 2007 Share Posted May 7, 2007 No, your syntax should work fine. I think. Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247351 Share on other sites More sharing options...
Mutley Posted May 7, 2007 Author Share Posted May 7, 2007 Hmm, maybe because I'm using SELECTs then, here's my code: $result = mysql_query("SELECT user_id, job_id FROM jobs WHERE done = '0'"); while($row = mysql_fetch_array( $result )) { $user_id = $row['user_id']; $job = $row['job_id']; } if($job == '1') { $wages = '5'; } else { $wages = '0'; } $result = mysql_query("SELECT money FROM users WHERE user_id = '$user_id'"); while($row = mysql_fetch_array( $result )) { $money = $row['money']; } $earnings = $money + $wages; $sql = "UPDATE users SET money = '$earnings' WHERE user_id = '$user_id'"; mysql_query($sql); $sql = "UPDATE jobs SET done = '1' WHERE job_id = '1' AND user_id = '$user_id'"; mysql_query($sql); Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247364 Share on other sites More sharing options...
papaface Posted May 7, 2007 Share Posted May 7, 2007 $sql = "UPDATE users SET money = '$earnings' WHERE user_id = '$user_id'"; mysql_query($sql); $sql = "UPDATE jobs SET done = '1' WHERE job_id = '1' AND user_id = '$user_id'"; mysql_query($sql); Are you sure your $sql variable isn't being over-written. Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247367 Share on other sites More sharing options...
Mutley Posted May 7, 2007 Author Share Posted May 7, 2007 Changed it to $sql1 on the second query, still does it (1 row). Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247377 Share on other sites More sharing options...
MadTechie Posted May 7, 2007 Share Posted May 7, 2007 <?php $result = mysql_query("SELECT user_id, job_id FROM jobs WHERE done = '0'"); while($row = mysql_fetch_array( $result )) { $user_id = $row['user_id']; //<--So this will only be the last result $job = $row['job_id']; } ///<--Ermm shouldn't this be \/ if($job == '1') { $wages = '5'; } else { $wages = '0'; } $result = mysql_query("SELECT money FROM users WHERE user_id = '$user_id'"); while($row = mysql_fetch_array( $result )) { $money = $row['money']; } $earnings = $money + $wages; $sql = "UPDATE users SET money = '$earnings' WHERE user_id = '$user_id'"; mysql_query($sql); $sql = "UPDATE jobs SET done = '1' WHERE job_id = '1' AND user_id = '$user_id'"; mysql_query($sql); //HERE ?> Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247383 Share on other sites More sharing options...
Mutley Posted May 7, 2007 Author Share Posted May 7, 2007 Since I defined the varialbes I didn't know I had to end the bracket at the bottom? How do I make it so it loops the results then, instead of selecting the one user_id? Thanks guys. Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247386 Share on other sites More sharing options...
MadTechie Posted May 7, 2007 Share Posted May 7, 2007 as i said above move the } it should work Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247391 Share on other sites More sharing options...
Mutley Posted May 7, 2007 Author Share Posted May 7, 2007 It only does 1 still. Hmm. Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247396 Share on other sites More sharing options...
MadTechie Posted May 7, 2007 Share Posted May 7, 2007 try <?php $result = mysql_query("SELECT user_id, job_id FROM jobs WHERE done = '0'"); while($row = mysql_fetch_array( $result )) { $user_id = $row['user_id']; $job = $row['job_id']; if($job == '1') { $wages = '5'; } else { $wages = '0'; } $result = mysql_query("SELECT money FROM users WHERE user_id = '$user_id'"); while($row = mysql_fetch_array( $result )) { $money = $row['money']; $earnings = $money + $wages; $sql = "UPDATE users SET money = '$earnings' WHERE user_id = '$user_id'"; mysql_query($sql); $sql = "UPDATE jobs SET done = '1' WHERE job_id = '1' AND user_id = '$user_id'"; mysql_query($sql); } } ?> Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247402 Share on other sites More sharing options...
Mutley Posted May 7, 2007 Author Share Posted May 7, 2007 Nope. It updates the first record it finds only. Probably because it's only selecting one user_id? Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247425 Share on other sites More sharing options...
MadTechie Posted May 7, 2007 Share Posted May 7, 2007 lol my bad <?php $result1 = mysql_query("SELECT user_id, job_id FROM jobs WHERE done = '0'"); while($row1 = mysql_fetch_array( $result1 )) { $user_id = $row1['user_id']; $job = $row1['job_id']; if($job == '1') { $wages = '5'; } else { $wages = '0'; } $result2 = mysql_query("SELECT money FROM users WHERE user_id = '$user_id'"); while($row2 = mysql_fetch_array( $result2 )) { $money = $row2['money']; $earnings = $money + $wages; $sql = "UPDATE users SET money = '$earnings' WHERE user_id = '$user_id'"; mysql_query($sql); $sql = "UPDATE jobs SET done = '1' WHERE job_id = '1' AND user_id = '$user_id'"; mysql_query($sql); } } ?> Link to comment https://forums.phpfreaks.com/topic/50274-solved-looping-a-query/#findComment-247443 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.