if not there insert else update....

[siriusdave]

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