can someone have a look at the bit of code for me...
what it dose is get each user and there prediction and match with the score form the matchdata
and update the prediction table with point where there user id is
also update standings table or insert the info in if they are not in there
the only thing is when the month changes it does not insert new data in the standing table
standing table is layed out like this
DROP TABLE IF EXISTS sf_standings;
CREATE TABLE IF NOT EXISTS sf_standings (
lid int(11) NOT NULL default '0',
userid int(11) NOT NULL default '0',
month varchar(10) NOT NULL default '',
pld int(10) unsigned default NULL,
won int(10) unsigned default NULL,
drawn int(10) unsigned default NULL,
lost int(10) unsigned default NULL,
gfor smallint(5) unsigned default NULL,
gagainst smallint(5) unsigned default NULL,
diff smallint(6) default NULL,
points int(10) unsigned default NULL,
PRIMARY KEY (lid,userid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
need the script to look in this standing table for the userid and month
if the userid and month match the ones that we are looking for then update else insert a new row
ie ..look for userid 1 month Jul
if there is a row that match that update it
else insert info userid 1 month Jul ect
hope you get what i mean...
tahnks for any help..
<?php
// Redirect non-admins
if ($user['status'] != '1' || $user['group'] != '2') {
header ('Location: '. $settings['siteurl'] .'/admin/login.php');
exit();
}
function writepoints($matchid){
global $settings,$tbl_prefix;
$Month = date('M');
$user_query = mysql_query("SELECT * FROM ".$tbl_prefix."users");
while($user_query_row = mysql_fetch_assoc($user_query)) {
$userid[] = array(
'userid' => $user_query_row['userid']
);
}
foreach ($userid as $userid) {
//Get macth
$match_query = mysql_query("SELECT * FROM ".$tbl_prefix."matchdata WHERE matchid ='".$matchid."'");
while($match_row = mysql_fetch_assoc($match_query)) {
$homescore = $match_row['homescore'];
$awayscore = $match_row['awayscore'];
$bonuspoints = $match_row['bonuspoints'];
//Get users Predictions
$users_Pred_query = mysql_query("SELECT * FROM ".$tbl_prefix."predictiondata WHERE pmatchid = '".$matchid."' && userid='".$userid['userid']."'");
while($users_Pred_row = mysql_fetch_assoc($users_Pred_query)) {
$phomescore = $users_Pred_row['phomescore'];
$pawayscore =$users_Pred_row['pawayscore'];
//standings
$stand_query = mysql_query("SELECT * FROM ".$tbl_prefix."standings WHERE userid='".$userid['userid']."' && month='".$Month."'");
//////////////////////////
//if not in standings... update predictiondata and insert it in to standing
///////////////////////////
if(mysql_num_rows($stand_query) == 0){
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if ($homescore == $phomescore && $awayscore == $pawayscore){
$winpoints = 3 + $bonuspoints;
$pld = 1;
$won = 1 ;
$drawn = 0;
$lost = 0;
$points = $winpoints;
//update predictiondata
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$winpoints."' WHERE pmatchid = '".$matchid."' && userid='".$userid['userid']."'");
//////////////////////
//instert in to standing
mysql_query("INSERT INTO ".$tbl_prefix."standings (`lid` , `userid` , `month` , `pld` , `won` , `drawn` , `lost` , `gfor`, `gagainst` , `diff` , `points` )
VALUES ('0', '".$userid['userid']."', '".$Month."', '".$pld."', '".$won."' , '".$drawn."' , '".$lost."', NULL , NULL , NULL ,'".$points."')");
/////////////////////////
}elseif ($phomescore === $pawayscore && $homescore === $awayscore){
$drawpoints = 1;
$pld = 1;
$won = 0;
$drawn = 1;
$lost = 0;
$points = $drawpoints;
//update predictiondata
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$drawpoints."' WHERE pmatchid = '".$matchid."'&& userid='".$userid['userid']."'");
//////////////////////
//instert in to standing
mysql_query("INSERT INTO ".$tbl_prefix."standings (`lid` , `userid` , `month` , `pld` , `won` , `drawn` , `lost` , `gfor`, `gagainst` , `diff` , `points` )
VALUES ('0', '".$userid['userid']."', '".$Month."', '".$pld."', '".$won."' , '".$drawn."' , '".$lost."', NULL , NULL , NULL ,'".$points."')");
////////////////////////
}elseif ($phomescore > $pawayscore && $homescore > $awayscore){
$drawpoints =1;
$pld = 1;
$won = 0;
$drawn = 1;
$lost = 0;
$points = $drawpoints;
//update predictiondata
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$drawpoints."' WHERE pmatchid = '".$matchid."'&& userid='".$userid['userid']."'");
//////////////////////
//instert in to standing
mysql_query("INSERT INTO ".$tbl_prefix."standings (`lid` , `userid` , `month` , `pld` , `won` , `drawn` , `lost` , `gfor`, `gagainst` , `diff` , `points` )
VALUES ('0', '".$userid['userid']."', '".$Month."', '".$pld."', '".$won."' , '".$drawn."' , '".$lost."', NULL , NULL , NULL ,'".$points."')");
/////////////////////////
}
elseif ($pawayscore > $phomescore && $awayscore > $homescore ){
$drawpoints =1;
$pld = 1;
$won = 0;
$drawn = 1;
$lost =0;
$points = $drawpoints;
//update predictiondata
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$drawpoints."' WHERE pmatchid = '".$matchid."'&& userid='".$userid['userid']."'");
//////////////////////
//instert in to standing
mysql_query("INSERT INTO ".$tbl_prefix."standings (`lid` , `userid` , `month` , `pld` , `won` , `drawn` , `lost` , `gfor`, `gagainst` , `diff` , `points` )
VALUES ('0', '".$userid['userid']."', '".$Month."', '".$pld."', '".$won."' , '".$drawn."' , '".$lost."', NULL , NULL , NULL ,'".$points."')");
/////////////////////////
}else {
$points = 0;
$pld = 1;
$won = 0;
$drawn =0;
$lost = 1;
$points = $points;
//update predictiondata
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$points."' WHERE pmatchid = '".$matchid."'&& userid='".$userid['userid']."'");
//////////////////////
//instert in to standing
mysql_query("INSERT INTO ".$tbl_prefix."standings (`lid` , `userid` , `month` , `pld` , `won` , `drawn` , `lost` , `gfor`, `gagainst` , `diff` , `points` )
VALUES ('0', '".$userid['userid']."', '".$Month."', '".$pld."', '".$won."' , '".$drawn."' , '".$lost."', NULL , NULL , NULL ,'".$points."')");
}
/////////////////////////////////////////
}else{
///////////////////////////////////////////////////////////////
//if in standings... update predictiondata and update standing
/////////////////////////////////////////////////
while($stand_query_row = mysql_fetch_assoc($stand_query)) {
$spld = $stand_query_row['pld'];
$swon = $stand_query_row['won'];
$sdrawn = $stand_query_row['drawn'];
$slost = $stand_query_row['lost'];
$spoints = $stand_query_row['points'];
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if ($homescore == $phomescore && $awayscore == $pawayscore){
$winpoints = 3 + $bonuspoints;
$pld = 1 + $spld;
$won = 1 + $swon;
$drawn = $sdrawn;
$lost = $slost;
$points = $winpoints + $spoints;
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$winpoints."' WHERE pmatchid = '".$matchid."' && userid='".$userid['userid']."'");
mysql_query("UPDATE ".$tbl_prefix."standings SET pld ='".$pld."',won ='".$won."',drawn ='".$drawn."',lost='".$lost."',points = '".$points."' WHERE userid='".$userid['userid']."' && month='".$Month."'");
}elseif ($phomescore === $pawayscore && $homescore === $awayscore){
$drawpoints =1;
$pld = 1 + $spld;
$won = $swon;
$drawn = 1 + $sdrawn;
$lost = $slost;
$points = $drawpoints + $spoints;
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$drawpoints."' WHERE pmatchid = '".$matchid."'&& userid='".$userid['userid']."'");
mysql_query("UPDATE ".$tbl_prefix."standings SET pld ='".$pld."',won ='".$won."',drawn ='".$drawn."',lost='".$lost."',points = '".$points."' WHERE userid='".$userid['userid']."' && month='".$Month."'");
}elseif ($phomescore > $pawayscore && $homescore > $awayscore){
$drawpoints =1;
$pld = 1 + $spld;
$won = $swon;
$drawn = 1 + $sdrawn;
$lost = $slost;
$points = $drawpoints + $spoints;
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$drawpoints."' WHERE pmatchid = '".$matchid."'&& userid='".$userid['userid']."'");
mysql_query("UPDATE ".$tbl_prefix."standings SET pld ='".$pld."',won ='".$won."',drawn ='".$drawn."',lost='".$lost."',points = '".$points."' WHERE userid='".$userid['userid']."' && month='".$Month."'");
}
elseif ($pawayscore > $phomescore && $awayscore > $homescore ){
$drawpoints =1;
$pld = 1 + $spld;
$won = $swon;
$drawn = 1 + $sdrawn;
$lost = $slost;
$points = $drawpoints + $spoints;
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$drawpoints."' WHERE pmatchid = '".$matchid."'&& userid='".$userid['userid']."'");
mysql_query("UPDATE ".$tbl_prefix."standings SET pld ='".$pld."',won ='".$won."',drawn ='".$drawn."',lost='".$lost."',points = '".$points."' WHERE userid='".$userid['userid']."' && month='".$Month."'");
}else {
$points = 0;
$pld = 1 + $spld;
$won = $swon;
$drawn =$sdrawn;
$lost = 1 + $slost;
$points = $points + $spoints;
mysql_query("UPDATE ".$tbl_prefix."predictiondata SET points = '".$points."' WHERE pmatchid = '".$matchid."'&& userid='".$userid['userid']."'");
mysql_query("UPDATE ".$tbl_prefix."standings SET pld ='".$pld."',won ='".$won."',drawn ='".$drawn."',lost='".$lost."',points = '".$points."' WHERE userid='".$userid['userid']."' && month='".$Month."'");
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
}//standing end
}//predtions end
}//matcth end
}//foreach end
}//if standing end
}//fuction end
?>
