need some quick help..

[bimmer528]

hi all. What im trying to do is this. 1st check to see if the name exists already in the db. if so, were going to update. If not, were going to add a new one.

// we are already in a while loop 


while($s_score =mysql_fetch_array($result)) { 

$name= $s_score['Name'];
	$totalscore= round($score1 + $score10 + $score2 + $score11 + $score3 + $score12 ,2);

$query="SELECT Name FROM Players_Rank WHERE Name = '$name' ";
mysql_query($query);


if (!$name) {
$insertsql="INSERT INTO Players_Rank (Name,Data) VALUES ('$name','$totalscore')";
mysql_query($insertsql);

} else {

$updatesql="UPDATE Players_Rank set Data = '$totalscore' WHERE Name= '$name' ";
mysql_query($updatesql);
} // end if else.

} // end loop

just getting 0's with this code.

[sarahk]

easy mistake. We've all done it.

// we are already in a while loop 

while($s_score =mysql_fetch_array($result)) 
{ 
  $name= $s_score['Name'];
  $totalscore= round($score1 + $score10 + $score2 + $score11 + $score3 + $score12 ,2);

  $query="SELECT `Name` FROM Players_Rank WHERE `Name` = '{$name}' ";
  $newResult = mysql_query($query);
  if($row=mysql_fetch_array($newResult)
  {
    $nameSql="UPDATE Players_Rank set `Data` = '$totalscore' WHERE `Name`= '{$name}' ";
  }
  else
  {    

    $nameSql="INSERT INTO Players_Rank (Name,Data) VALUES ('$name','$totalscore')";
  } // end if else.
  $nameResult = mysql_query($nameSql);
);
} // end loop

[bimmer528]

thx sarahk!

Still not inserting though and not sure it will update as well. Can you have a closer look? Been stumped for hours on this.

while($s_score =mysql_fetch_array($result)) { 


if ($s_score['mapaverage'] > 112) {
$score10= 1000;
} else {
$score10=0;
}
if ($s_score['fragavg'] > 20) {
$score11= 1000;
} else {
$score11=0;
}
if ($s_score['RoundAvg'] > 90) {
$score12= 1000;
} else {
$score12=0;
}


$name= $s_score['Name']; 
$totalscore= round($score1 + $score10 + $score2 + $score11 + $score3 + $score12 ,2); 

  $query="SELECT `Name` FROM Players_Rank WHERE `Name` = '{$name}' "; 
 $NewResult= $xoopsDB->query($query);

  if($row=mysql_fetch_array($newResult)) { 

$nameSql="UPDATE Players_Rank set `Data` = '$totalscore' WHERE `Name`= '{$name}' "; 

  } else {     

$nameSql="INSERT INTO Players_Rank (Name,Data) VALUES ('$name','$totalscore')"; 

  } // end if else. 

$NameResult= $xoopsDB->query($nameSql);

}

[laserlight]

Set error reporting to E_ALL, either by changing php.ini, or by using ini_set()

I think you could have some typos, the notices that PHP will report will help you fix them.

[bimmer528]

no errors.

I just realized it. Its not inserting or updating because the query its not pulling any info.

while($s_score =mysql_fetch_array($result)) {

//here we were doing some variables and doing the update, insert

Without the insert or update, we can pull information. Result = 6000 With that added, we cannot. Result=0

}

[bimmer528]

i got it going np but heres the problem.

My intent for this script is to do this.

1. Check to see if the a record has changed.
2. If Data has changed and the player exists already, we are going to update.
3. If there is no player, we are adding a new one.

What it's doing is repeating the insert. ie.. each refresh say put's 100 names in the db. After each refresh, its putting the same people back in there in new rows. I just need those existing players updated and if there is a new player, we add the new one only.

<?php 

$sql ="SELECT Name, 
MapAvg / 10 AS mapaverage, 
Kills / Maps AS fragavg, 
RoundAvg, 
Knifed + KnifeKills AS totalknifed, date 
FROM Players WHERE date= '$thismonth' or date = '$lastmonth' "; 
$result = mysql_query($sql); 

while($s_score =mysql_fetch_array($result)) { 

// Set the points for all players who do have stats 
$score1=$s_score['mapaverage'] * 100; 
$score2=$s_score['fragavg'] * 10; 
$score3=$s_score['RoundAvg'] * 10; 
/////////////////////////////////////////////////// 


   if ($s_score['mapaverage'] > 112) { 
   $score10= 1000; 
   } else { 
   $score10=0; 
   } 
   if ($s_score['fragavg'] > 20) { 
   $score11= 1000; 
   } else { 
   $score11=0; 
   } 
   if ($s_score['RoundAvg'] > 90) { 
   $score12= 1000; 
   } else { 
   $score12=0; 
   } 

$name = $s_score['Name']; 
$totalscore= round($score1 + $score10 + $score2 + $score11 + $score3 + $score12 ,2); 




$query="SELECT Players.date, Players_Rank.Name FROM Players, Players_Rank WHERE `Players_Rank.Name` = '$name' 
AND Players.date= '$thismonth' or date = '$lastmonth'"or die("could not select"); 
$newResult = mysql_query($query); 

if ($row=mysql_fetch_array($newResult)) { 

$nameSql="UPDATE Players_Rank set `Data` = '$totalscore' WHERE `Name`= '$name' " or die("could not update"); 

} else {      

$nameSql="INSERT INTO Players_Rank (Name,Data) VALUES ('$name','$totalscore')" or die("could not insert"); 

} // end if else. 

$nameResult = mysql_query($nameSql); 

} 

?> 

[sarahk]

if you don't follow Laserlights suggestion then I recommend you need to change

$result = mysql_query($sql);

to

$result = mysql_query($sql) or die(mysql_error() . ' - ' . $sql);

ie add the error reporting. You can cut and paste the text into phpMyAdmin and test it there

[bimmer528]

not getting errors :/ the php code is telling what the db should do wrong. See, its inserting the same data in new rows for each page refresh. Its not checking to see if the data is the same or differant. If differant, we update. If it doesnt exist, were adding a new one.

[ultraslacker]

mysql REPLACE does the same thing - INSERT if not exist, otherwise UPDATE.

[sarahk]

$query="SELECT Players.date, Players_Rank.Name FROM Players, Players_Rank WHERE `Players_Rank.Name` = '$name' 
AND Players.date= '$thismonth' or date = '$lastmonth'"or die("could not select");  

This code is just checking to see if there is a match for the player for the month. If there is a record, but it is older, it will add a new record.