Update a count in entire database

[jonki]

Hi,

I am stuck with something that is hopefully a small problem, I just don't have a clue how to do this.

I have a cron job which does my accounting for me at the end of every week. It was working fine but I've made a few changes which has highlighted a small problem.

I need to count

$query2 = "SELECT *, count(*) as 'numero' FROM `db` WHERE usuario = '$usuario' group by usuario";
$result2 = mysql_query($query2); // find value 
$data2 = mysql_fetch_array($result2); // get results into $data 

$numero = $data2['numero'];
if (empty($numero)){ $numero = "0"; }

but I need to do this for every row in the data base like I do for everything else:

$query = "update db set sem1 = sem4, max1 = sem4";
		$result = mysql_query($query); //run the query 

So basically I need some way to combine the above two statements so that I can set something like "max1 = $count" and it will do this for every row (even though for every row this count is different.)

I am almost there ...... how do I finish this?

Once again - I really appreciate all your help.

Jon

[jonki]

... by the way the two data bases in question are different databases - does this make this problem impossible?

If so I will have to work around it another way.

[Sxooter]

Ahh, what you want is some kind of subselect. I'm not sure MySQL can do this exact thing yet, but you want something like:

update db
set
sem1 = (select count() from db where usario='$usario'),
max1 = (select count() from db where usario='$usario')
where usario='$usario';

I'm not sure if their being in differnet databases will work with MySQL. For other databases that support schemas, you could put the two "databases" into one database, but in seperate schemas. the syntax for that would make the fields and tables look like this:

db1.usario
db2.db

etc...

[jonki]

Excellent - thanks a lot. I'll give it a try.

Jon