Shorten SQL Query

iceraider

Hi the second loop in this query has to run through MYSQL like 28,000 times. Is there anyway to make the two Query Statements into 1.

	$q = $db->Query('SELECT batch FROM push GROUP BY batch');
	while($r = $db->Fetch($q))
	{
		$q2 = $db->Query('SELECT COUNT(*) as count,spend_date FROM push WHERE batch=\''.$r['batch'].'\' GROUP BY spend_date');
		while($r2 = $db->Fetch($q2))
		{
			if($r2['count']>'1')
			{
				$db->Query('UPDATE push SET spend_date=\''.$r2['spend_date'].'\' WHERE batch=\''.$r['batch'].'\'');
			}
		}
	}

Here is the problem

Say there is many entries with the same BATCH number... but some of them might have different SPEND_DATE, what I need to do is have each batch number have the same SPEND_DATE.

Cheers,
Andrew

pohopo

I believe this should work...

$query = "SELECT count(*) as count, batch, spend_date FROM push GROUP BY batch, spend_date";
$q2 = $db->Query($query);
while($r2 = $db->Fetch($q2))
{
	if($r2['count']>'1')
	{
		$db->Query('UPDATE push SET spend_date=''.$r2['spend_date'].'' WHERE batch=''.$r2['batch'].''');
	}
}

iceraider

Actually my code i gave you doesnt even work and yours is off too.

This is a example of what I need done.. Say I have a house with 54 rooms (BATCH), and in each room there is a number of kids (SPEND_DATE), now I say each kid speaks a different language. I want to make each kid speak the same language as the kids in THEIR OWN ROOM (BATCH).

For example room 1(batch) has 50 kids. 10 Kids speak chinese, and 40 speak american.

So I make all 50 speak american.

Roger_Ramjet

Hey Dude! What language is American exactly?

Roger_Ramjet

OK. Was half way through answering this and then realised that we don't have enough information. Post the table schemas and state the operation that you want to perform. Make your question exact and specific, don't mess around with metaphores, they only confuse the issue.

What I can say is that the logic of your whole routine is seriously flawed - but then you already know that.