daily auto update problem

[sqlnoob]

Not terribly sure where to ask this, but here goes...

 

WHAT I HAVE

 

Ok I have these 2 tables:

 

$sqlgang = "CREATE TABLE gangsters
(
GangsterID int(4) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(GangsterID),
Gangstername varchar(30) NOT NULL,
Password varchar(10) NOT NULL,
Gangname varchar(20) NOT NULL,
City varchar( NOT NULL,
Personality varchar(15) NOT NULL,
Finances int(9) NOT NULL,
Booze int(3) NOT NULL,
Counterfeit int(3) NOT NULL,
Status varchar(20) NOT NULL,
Notoriety int(12) NOT NULL,
Checkday int(3) NOT NULL,
Officecheck int(1) NOT NULL,
Recruitcheck int(1) NOT NULL,
Residencecheck int(1) NOT NULL,
Recruitcheck int(1) NOT NULL,
Mapurl varchar(40) NOT NULL,
Musiccheck int(1) NOT NULL,
Ipad varchar(16) NOT NULL
)";
mysql_query($sqlgang,$con);

$sqlstreets = "CREATE TABLE streets
(
BusinessID int(5) NOT NULL, 
PRIMARY KEY(BusinessID),
Businesscategory varchar(10) NOT NULL,
Businesstype varchar(20) NOT NULL,
Owner varchar(30) NOT NULL,
Extortionist varchar(30) NOT NULL,
Landvalue int(6) NOT NULL,
Businessclass int(1) NOT NULL,
Operationalstatus varchar(20) NOT NULL,
Statusday int(3) NOT NULL,
Illegal varchar(20) NOT NULL,
Income int(5) Default '0',
Neighbourhood varchar(30) NOT NULL,
Policeward int(2) NOT NULL,
Churchward int(2) NOT NULL
)";
mysql_query($sqlstreets,$con);

 

the Gangsters table and the streets table. Now the data in the Finances column of the gangsters table needs to be automatically updated each day. I know how to this manually each day for 1 record as shown by this:

 

//TIME CALCULATION
$gamestart = mktime(0, 0, 0, 11, 14, 2008);
$today = time();
$rawdifference = $today - $gamestart;
$thisday = floor($rawdifference / 84600);

//FETCHING TABLE TIME
$dayresult = mysql_query ("SELECT Checkday FROM gangsters WHERE Gangstername ='$you' AND Status <>'dead'");
$dayrow = mysql_fetch_array($dayresult);
$oldday = $dayrow['Checkday'];

//CHECKING DATE OF LAST UPDATE
if ($thisday > $oldday){

//FETCHING ILLEGAL INCOME
$incomeresult = mysql_query ("SUM(Income) AS Incomesum FROM streets WHERE Owner ='$you' AND Operationalstatus ='running'");
$incomerow = mysql_fetch_array($incomeresult);
$illegalincome = $incomerow['Incomesum'];

//FETCHING AND CALCULATING LEGAL INCOME
$bizzresult = mysql_query ("SUM(Landvalue) AS Bizzsum FROM streets WHERE Owner ='$you' AND Operationalstatus ='running' OR Operationalstatus ='open'");
$bizzrow = mysql_fetch_array($bizzresult);
$tabincome = $bizzrow['Bizzsum'];
$legalincome = $tabincome / 20;

//FETCHING OLD MONEY
$moneyresult = mysql_query ("SELECT Finances FROM gangsters WHERE Gangstername ='$you' AND Status <>'dead'");
$moneyrow = mysql_fetch_array($moneyresult);
$oldmoney = $moneyrow['Finances'];

//CALCULATING NEW MONEY
$newmoney = $oldmoney + $legalincome + $illegalincome;

//UPDATING NEW MONEY
mysql_query("UPDATE gangsters SET Finances ='$newmoney' WHERE Gangstername ='$you'");
}

 

MY PROBLEM

 

I want to be able to automatically update the Finances field for all the records in the gangsters table that need updating each day. However I have no clue as to how to do that. Any help in solvng my problem would be very much appreciated.

[sqlnoob]

I probably need to use a JOIN of some sort, but then I have all these WHERE clauses and a computation to consider. Plus it also needs to be updated and I'm not even sure wether that is possible.

 

I am a complete noob about these things, it would be my first time using such commands.

[fenway]

There are plenty of excellent tutorials on this site, and in this board's stickies.  As for the "daily" part, use a cron job.

[sqlnoob]

so cron job

 

ok thanks

 

i've read some tutorials about joins, but i dont see how it possible to do so here. Perhaps with a WHILE i can make it work and run through all the records.

[fenway]

You may not need them per se... you're just taking a few values and adding them ... subqueries can help you here.