[SOLVED] Looping a query

[Mutley]

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?

[JakeTheSnake3.0]

take out the quotes from 'approve' and see what happens

[Mutley]

So that query should update all of them?

[Mutley]

At the moment it only runs the query once, so only updates one row, not all of them according to the WHERE statement.

[JakeTheSnake3.0]

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 =....

[Mutley]

No, you misunderstanding. That only updates ONE row, I need it to update everyone where validate='1'.

[Mutley]

???

[Mutley]

Do I need to do a FOREACH or WHILE thing to achieve this?

[papaface]

No, your syntax should work fine. I think.

[Mutley]

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);

[papaface]

$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.

[Mutley]

Changed it to $sql1 on the second query, still does it (1 row).

[MadTechie]

<?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
?>

[Mutley]

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.

[MadTechie]

as i said above move the } it should work

[Mutley]

It only does 1 still. Hmm.

[MadTechie]

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);
	}
}
?>

[Mutley]

Nope. 

 

It updates the first record it finds only. Probably because it's only selecting one user_id?

[MadTechie]

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);
	}
}
?>