Milestones formula

[xXREDXIIIXx]

So I have this script but apparently it is killing my shared hosting CPU usage. This is for Trophies for my own website.

 

This foreach is repeated twice one is for Total number of trophies and the other is for the total number of platinum's.

 

 

Basically I get the current total of Trophies / Platinum's

 

Then I use the array to check if my total is higher than the current number in array if it is I then need to check if it is already in the database. then either ignore or insert it it.

 

$gt_row is for me to pull the game_code and the xmb_order of the trophy so it can be accessed easier at a later time.

 

If anyone can suggest a better way that be great.

## MILESTONES		
$tarray = array("1","100","250","500","1000","2000","3000","4000","5000","6000","7000","8000","9000","10000");
	
## GET total Trophies and Platinums
$row = mysqli_fetch_array(mysqli_query($link, "SELECT trophies_t,trophies_p FROM gamers_profiles WHERE gamer_id = 'xxredxiiixx'"));
$count_trophies = $row['trophies_t']; $count_platinum = $row['trophies_p'];
	
## Check if total Trophies > then milestone
foreach($tarray as $trophy){		
    if($count_trophies > $trophy){			
	## Search milestones for existing 
	$m_row = mysqli_fetch_array(mysqli_query($link, "SELECT COUNT(award) AS numrows FROM milestones WHERE award = '$trophy' && award_type = 'TROPHIES'"));
			
        if($m_row['numrows'] == 0){
	    ## Get Game Code and XMB
	    $offset = ($trophy - 1);			
	    $gt_row = mysqli_fetch_array(mysqli_query($link, "SELECT * FROM gamers_trophies WHERE gamer_id = 'xxredxiiixx' ORDER BY date_earned ASC LIMIT $offset , 1"));
	     $code = $gt_row['game_code']; $xmb = $gt_row['xmb_order']; $date = $gt_row['date_earned'];			
	    mysqli_query($link, "INSERT INTO milestones (game_code, xmb_order, date_earned, award_type, award) VALUES ('$code','$xmb','$date','TROPHIES','$trophy')");
        }
    }
}

Edited May 11, 2014 by xXREDXIIIXx

[trq]

You need to find yourself some tutorials on a 'Join in SQL'. Executing queries within loops is terribly inefficient.

[QuickOldCar]

I concur with trq, that's just way too many queries being done.

 

On shared hosting, any usage is too much usage.

 

Using mysqli_fetch_array() will return 2 sets of data, numeric and associative array.

 

mysqli_fetch_assoc() would return half the amount of data.

 

Can also do it as...

$result = $mysqli->query($query);

$row = $result->fetch_array(MYSQLI_ASSOC);