Page 1 of 1

PHP Using SQL Query Search Results... Help

Posted: Mon Apr 26, 2010 12:07 pm
by dave7802
Hi,

I have a little script that uses a public API to gather some information from a 3rd party site (A Gaming Site and the results are your stats for the game)

the issue i have with the script is i have to add a new username every time i want a new user on the Leader board,
So an idea was to have the script run and pick up the user names of the people from a PHPBB Database,
This was any person who add details into there profile for this, would be added onto the leader board,

Here is the Full Script!

Code: Select all

<html>
<head>
<meta http-equiv="refresh" content="1200">
<title>The Mess Hall LeaderBoard!</title>
<?php include("style.css"); ?>
</head>
<body>
<!-- THIS IS THE HEADER -->
<div align="right">
    <table width=100% class="headerMain">
        <tr>
            <td width="100%" valign='center' align='center'>

            </td>
        </tr>
    </table>
<div>
<hr />
<?php 


	ini_set ('display_errors', 0);
	error_reporting (E_ALL & ~E_NOTICE);


$rankpoints=array(
0 => 0,
1 => 6500,
2 => 11000,
3 => 18500,
4 => 28000,
5 => 40000,
6 => 53000,
7 => 68000,
8 => 84000,
9 => 100000,
10 => 120000,
11 => 138000,
12 => 158000,
13 => 179000,
14 => 200000,
15 => 224000,
16 => 247000,
17 => 272000,
18 => 297000,
19 => 323000,
20 => 350000,
21 => 377000,
22 => 405000,
23 => 437000,
24 => 472000,
25 => 537000,
26 => 620000,
27 => 720000,
28 => 832000,
29 => 956000,
30 => 1092000,
31 => 1240000,
32 => 1400000,
33 => 1550000,
34 => 1730000,
35 => 1900000,
36 => 2100000,
37 => 2300000,
38 => 2530000,
39 => 2700000,
40 => 2928000,
41 => 3142000,
42 => 3378000,
43 => 3604000,
44 => 3852000,
45 => 4090000,
46 => 4350000,
47 => 4600000,
48 => 4872000,
49 => 5134000,
50 => 5400000);




//grab the stats!
$url = 'http://api.bfbcs.com/api/ps3';
$postdata = 'players='.$sars_members.'&fields=all';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postdata);
$data = curl_exec($ch);
curl_close($ch);
$data = json_decode($data,true);

if(isset($_GET['debug']))
{
	echo '<div align="left"><pre>';
	print_r($data);
	echo '</pre></div>';
	echo '<br /><br />';
}

function countdown($lastupdate) {

	$next_update = $lastupdate; 

	$diffDate = $next_update;

	if ($diffDate < 0) $diffDate = 0;

	$hours = floor($diffDate / 60 / 60);
	$diffDate = ($diffDate - ($hours*60*60));
	$minutes = floor($diffDate/60);
	$diffDate = $diffDate - ($minutes*60);
	$seconds = floor($diffDate);

	return $hours."h ".$minutes."m";

}
//loop through the data and store stats in an array
$stats = array();

$update = 0;
for($x=0;$x<count($data['players']);$x++)
{
    $level = $data['players'][$x]['level'];
	$name = $data['players'][$x]['name'];
	$rank = $data['players'][$x]['rank'];
	$score = $data['players'][$x]['score'];
	$kills = $data['players'][$x]['kills'];
	$deaths = $data['players'][$x]['deaths'];
	$time = $data['players'][$x]['time'];
	$queue = $data['players'][$x]['queue'];
	$raw_time = sec2hms($data['players'][$x]['time']);
	$SPH = round($score/intval(intval($time)/3600),0);
	$ratio = round($kills/$deaths,2);
	$veteran = $data['players'][$x]['veteran'];
	$gameslost = $data['players'][$x]['general']['losses'];
	$gameswon = $data['players'][$x]['general']['games'] - $gameslost;
	$wlRatio = round($gameswon/$gameslost, 2);
	$dogtags = $data['players'][$x]['general']['dogt'];
	$acepins = $data['players'][$x]['pins'][18]['count'];
	$nextupdate = countdown($data['players'][$x]['date_lastupdate']);
	
	$last_updated = strtotime($data['players'][$x]['date_lastupdate']); 
	
	 //add 6 hours to the last updated timestamp 
	$next_update_calc = strtotime("+6 hours", $last_updated);  

	//convert the future timestamp into human readable format 
	$next_update = date('d/m/Y H:i',$next_update_calc);

	//$lastupdate = left($last_updated, 8);

	
	$pins = 0;
	$insignia = 0;
	for($i=0; $i<count($data['players'][$x]['pins']);$i++) {
		$count = $data['players'][$x]['pins'][$i]['count'];
		$pins = $count + $pins;
	}
	for($i=0;$i<count($data['players'][$x]['insiginias']);$i++) {
		$count = $data['players'][$x]['insiginias'][$i]['count'];
		$insignia = $count + $insignia;
	}	
	$bron = 0;
	$silv = 0;
	$gold = 0;
	$plat = 0;
	
	foreach ($data['players'][$x]['weapons'] as $wep) {
		$bron += $wep['stars']['bron'];
		$silv += $wep['stars']['silv'];
		$gold += $wep['stars']['gold'];
		$plat += $wep['stars']['plat'];
	}
	foreach ($data['players'][$x]['vehicles'] as $wep) {
		$bron += $wep['stars']['bron'];
		$silv += $wep['stars']['silv'];
		$gold += $wep['stars']['gold'];
		$plat += $wep['stars']['plat'];
	}
	foreach ($data['players'][$x]['gadgets'] as $wep) {
		$bron += $wep['stars']['bron'];
		$silv += $wep['stars']['silv'];
		$gold += $wep['stars']['gold'];
		$plat += $wep['stars']['plat'];
	}

	//calculate which class they use
	$assault = $data['players'][$x]['general']['sc_assault'];
	$recon = $data['players'][$x]['general']['sc_recon'];
	$demo = $data['players'][$x]['general']['sc_demo'];
	$support = $data['players'][$x]['general']['sc_support'];
	
	$the_classes = array();
	$the_classes[0] = array('name'=>'assault','score'=>$assault);
	$the_classes[1] = array('name'=>'recon','score'=>$recon);
	$the_classes[2] = array('name'=>'demo','score'=>$demo);
	$the_classes[3] = array('name'=>'support','score'=>$support);
	
	//sort the classes array by score
	usort($the_classes,'compare_stats2');
	
	$most_used = $the_classes[0]['name'];
	
	$NextRank=intval($rank+1);

	
	// Search next rank
	if( empty($NextRank) )
	$nextRankID = $rank;
	else {
		if($rankpoints[$rank] == $rankpoints[($rank + 1)])
		{
			$nextRankID = 0;
			for ($m=($rank + 1);$m<(count($rankpoints)-1);$m++)
			{
				if( ($rankpoints[$m] > $rankpoints[$rank]) && ($nextRankID == 0) ) $nextRankID = $m;
			}
		}
		else	$nextRankID = ($rank + 1);
	    }
	
	if($rank < 10) { $rank = "0".$rank; }
	
	if ($data['players'][$x]['queue'] != 0) {
		$update++;
	}

	$diff = time() - strtotime($data['players'][$x]['date_lastupdate']);
	
    if($data['players'][$x]['queue'] != "0") {
		$next_update = "<b><span title=\"Place in Queue: " . $data['players'][$x]['queue'] . "\" style=\"border-bottom: 1px dotted #f00;\">In Queue</span></b>";
	} else {
		if ($diff > 21600) {
			$next_update = "<i><span title=\"Last Update: " . countdown($diff) . "\" style=\"border-bottom: 1px dotted #f00;\">Unsure</span></i>";    
		} else {
			$next_update = countdown($diff);    
		}
	}
	
	$stats[$x] = array('level'=>$level,'name'=>$name,'rank'=>$rank,'score'=>$score,'kills'=>$kills,'deaths'=>$deaths,'ratio'=>$ratio,'time'=>$raw_time,'most_used'=>$most_used,'veteran'=>$veteran,'gameswon'=>$gameswon,'gameslost'=>$gameslost,'wlratio'=>$wlRatio, 'score_rank'=> $score - $rankpoints[$nextRankID-1],'diff_score'=> $rankpoints[$nextRankID] - $rankpoints[$nextRankID-1],'nextrankid'=> $nextRankID,'nextrankscore' => $rankpoints[$nextRankID], 'dogtags' => $dogtags, 'SPH' => $SPH, 'pins' => $pins, 'bronze' => $bron, 'silver' => $silv, 'gold' => $gold, 'plat' => $plat, 'insignia' => $insignia, 'nextupdate' => $next_update, 'lastupdate' => $last_update, 'queue' => $queue, 'acepins' => $acepins);	
}

function sec2hms ($sec, $padHours = false) 
{
	$hms = "";
	$days= intval(intval($sec) / 86400);
	$hms .= $days. 'd ';
	$hours = intval(($sec / 3600) % 24); 
	$hms .= ($padHours) 
		  ? str_pad($hours, 2, "0", STR_PAD_LEFT). ':'
		  : $hours. 'h '; 
	$minutes = intval(($sec / 60) % 60); 
	//$hms .= str_pad($minutes, 2, "0", STR_PAD_LEFT). 'm ';
	$seconds = intval($sec % 60); 
	//$hms .= str_pad($seconds, 2, "0", STR_PAD_LEFT).'s';
	return $hms;
}


function compare_stats2($x,$y)
{
	if($x['score'] == $y['score'])
	{
		return 0;
	}
	elseif($x['score'] < $y['score'])
	{
		return 1;
	}
	else
	{
		return -1;
	}
}

function compare_stats($x,$y, $k = 1)
{
	if ($k == 1) {
		if(isset($_GET['sort'])) {
			$sort = $_GET['sort'];
		} else {
			$sort = 'score';
		}
	} else {
		$sort = 'score';
	}
	
	if(isset($_GET['sortby'])) {
		$sortby = $_GET['sortby'];
	} else {
		$sortby = 1;
	}
	
	if($x[$sort] == $y[$sort])
	{
		return 0;
	}
	elseif($x[$sort] < $y[$sort])
	{
		return $sortby;
	}
	else
	{
		return -$sortby;
	}
}

//sort the stats array by score
usort($stats,'compare_stats');

	if(isset($_GET['sortby'])) {
		$sortby = $_GET['sortby'];
	} else {
		$sortby = 1;
	}
	?> 
	<table class="maintable" width="100%" border="0" cellpadding="0">
		<tr align='center'>
            <td align='center'><b>Skill Level</b><br /></a></td>
            <td align='center'><b>Most Used</b><br /></a></td>
			<td align='center'><b>Name</b><br /></a></td>
			<td align='center'><b>Rank</b><br /></a></td>
            <td align='center'><b>Progress</b><br /></a></td>
			<td align='center'><b>Score</b><br /></a></td>
			<td align='center'><b>K/D Ratio</b><br /></a></td>
			<td align='center'><b>W/L Ratio</b><br /></a></td>
			<td align='center'><b>Time</b><br /></a></td>
			<td align='center'><b>Dog Tags</b><br /></a></td>
			<td align='center'><b>Platin</b><br /></a></td>
			<td align='center'><b>Ace Pins</b><br /></a></td>
            <td align='center'><b>Total Pins</b><br /></a></td>
            <td align='center'><b>Last Update</b></a></td>
		</tr>

	<?php
	
	//sort($test['0']);
	foreach ($stats as $player) {

        echo "</td><td align='center'>".$player['level'];
        echo "</td><td align='center'>";
		echo "<img src='./images/".$player['most_used'].".png'/>";
        echo "</td><td align='left'><a href='http://bfbcs.com/stats_ps3/". $player['name']."'>";
                echo $player['name'];
        echo "</a>";
        echo "</td><td align='center'>";
		$rank = $player['rank'];
		if ($rank == "0") {$rank = "01"; $player['diff_score'] = 6500;}
		if ($player['score_rank'] == 0) {$player['score_rank'] = 1;}
		echo '<img src="images/small/'.$rank.'.png" />';
		echo '</td><td align="center" valign="center">';
		$max = $player['diff_score'];
		$current = $player['score_rank'];
		?><img src="status.php?max=<?php echo $player['diff_score']; ?>&current=<?php echo $player['score_rank']; ?>&color=FFFFFF&bgcolor=000000" /><?php
		echo "</td><td align='center'>";
		echo $player['score'];
		echo "</td><td align='center'>";
			$kdRatio = $player['ratio'];
			if($kdRatio >= 1) {
				echo "<font color='green'>$kdRatio</font>";
			} else {
				echo "<font color='red'>$kdRatio</font>";
			}
		echo "</td><td align='center'>";
			$wlRatio = $player['wlratio'];
			if($wlRatio >= 1) {
				echo "<font color='green'>$wlRatio</font>";
			} else {
				echo "<font color='red'>$wlRatio</font>";
			}
		echo "</td><td align='center'>";
		echo $player['time'];
		echo "</td><td align='center'>";
		echo $player['dogtags'];
		echo "</td><td align='center'>";
		echo $player['plat'];
		echo "</td><td align='center'>";
		echo $player['acepins'];
		echo "</td><td align='center'>";
        echo $player['pins'];
        echo "</td><td align='center'>";
        echo $player['nextupdate'];    
		//echo $player['nextupdate'];
		echo "</td></tr>";
	}
	
?>	</table>

<?php



//echo "<font align = 'left'><pre>";
//print_r($data);
//echo "</pre></font>";


?>

</body>
</html>
That Script when ran gives the output of this:

Now can you see in the script there is a section that lists the user names:
//prep the members
$sars_members =

//grab the stats!
$url = 'http://api.bfbcs.com/api/ps3';
$postdata = 'players='.$sars_members.'&fields=all';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postdata);
$data = curl_exec($ch);
curl_close($ch);
$data = json_decode($data,true);
What i was trying to do was add a couple of section in to connect to the MySQL Database and Pull Data from some fields,
So i created a PHP File called Database that contains the MySQL connection information.

Then Added a couple of connect sections into the script:

Code: Select all


require_once('./database.php');

// Connect to the base database.
if (!$phpbb_db = @mysql_connect($phpbb['host'], $phpbb['user'], $phpbb['password'])) {
    exit("Unable to connect to the server '{$phpbb['host']}'.<br>Error: " . mysql_error());
}

// Select the desired base database.
if (!@mysql_select_db($phpbb['database'], $phpbb_db)) {
    exit("The database {$phpbb['database']} does not exists.");
Then i wanted to gather the Usernames from the Table:
so i added in:
$query = "SELECT * FROM `{$phpbb['table_prefix']}profile_fields_data`";
$result = mysql_query($query, $phpbb_db);
if (!$result) {
echo mysql_error($phpbb_db);
exit;
}

while ($profileDetails = mysql_fetch_assoc($result)) {
echo "<br>{$profileDetails['pf_psn_id']} ";
}

The i edited the following:
//grab the stats!
$url = 'http://api.bfbcs.com/api/ps3';
$postdata = 'players='.$profileDetails.'&fields=all';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postdata);
$data = curl_exec($ch);
curl_close($ch);
$data = json_decode($data,true);
The Red is what i changed, So my Understanding is it would collect the data from the Query,
When i look at the page it it shows the Usernames the query picked up, so i know it is searching and collecting the correct infomration,

I just know i am trying to use the searched data incorrectly:
Like this line here
$postdata = 'players='.$profileDetails.'&fields=all';
Is what i am trying to change to collect the information from the database

Here is the Script after i made my changes!

Code: Select all

<?php
//login.php

// Include the require files.
require("./httpclient/http.php");
require_once('./database.php');

// Connect to the base database.
if (!$phpbb_db = @mysql_connect($phpbb['host'], $phpbb['user'], $phpbb['password'])) {
    exit("Unable to connect to the server '{$phpbb['host']}'.<br>Error: " . mysql_error());
}

// Select the desired base database.
if (!@mysql_select_db($phpbb['database'], $phpbb_db)) {
    exit("The database {$phpbb['database']} does not exists.");
}

set_time_limit(0);
$http=new http_class;
$http->timeout=0;
$http->data_timeout=0;
$http->debug=1;
$http->html_debug=1;
$http->follow_redirect = 1;
$http->user_agent = 'Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.11) Gecko/2009060308 Ubuntu/9.04 (jaunty) Firefox/3.0.11';
?>
<html>
<head>
<meta http-equiv="refresh" content="1200">
<title>The Mess Hall LeaderBoard!</title>
<?php include("style.css"); ?>
</head>
<body>
<!-- THIS IS THE HEADER -->
<div align="right">
    <table width=100% class="headerMain">
        <tr>
            <td width="100%" valign='center' align='center'>
              
            </td>
        </tr>
    </table>
<div>
<hr />
<?php 


	ini_set ('display_errors', 0);
	error_reporting (E_ALL & ~E_NOTICE);


$rankpoints=array(
0 => 0,
1 => 6500,
2 => 11000,
3 => 18500,
4 => 28000,
5 => 40000,
6 => 53000,
7 => 68000,
8 => 84000,
9 => 100000,
10 => 120000,
11 => 138000,
12 => 158000,
13 => 179000,
14 => 200000,
15 => 224000,
16 => 247000,
17 => 272000,
18 => 297000,
19 => 323000,
20 => 350000,
21 => 377000,
22 => 405000,
23 => 437000,
24 => 472000,
25 => 537000,
26 => 620000,
27 => 720000,
28 => 832000,
29 => 956000,
30 => 1092000,
31 => 1240000,
32 => 1400000,
33 => 1550000,
34 => 1730000,
35 => 1900000,
36 => 2100000,
37 => 2300000,
38 => 2530000,
39 => 2700000,
40 => 2928000,
41 => 3142000,
42 => 3378000,
43 => 3604000,
44 => 3852000,
45 => 4090000,
46 => 4350000,
47 => 4600000,
48 => 4872000,
49 => 5134000,
50 => 5400000);

$query = "SELECT * FROM `{$phpbb['table_prefix']}profile_fields_data`";
$result = mysql_query($query, $phpbb_db);
if (!$result) {
 echo mysql_error($phpbb_db);
 exit;
}

while ($profileDetails = mysql_fetch_assoc($result)) {
    echo "<br>{$profileDetails['pf_psn_id']} ";
        }



//grab the stats!
$url = 'http://api.bfbcs.com/api/ps3';
$postdata = 'players='.$profileDetails.'&fields=all';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postdata);
$data = curl_exec($ch);
curl_close($ch);
$data = json_decode($data,true);

if(isset($_GET['debug']))
{
	echo '<div align="left"><pre>';
	print_r($data);
	echo '</pre></div>';
	echo '<br /><br />';
}

function countdown($lastupdate) {

	$next_update = $lastupdate; 

	$diffDate = $next_update;

	if ($diffDate < 0) $diffDate = 0;

	$hours = floor($diffDate / 60 / 60);
	$diffDate = ($diffDate - ($hours*60*60));
	$minutes = floor($diffDate/60);
	$diffDate = $diffDate - ($minutes*60);
	$seconds = floor($diffDate);

	return $hours."h ".$minutes."m";

}
//loop through the data and store stats in an array
$stats = array();

$update = 0;
for($x=0;$x<count($data['players']);$x++)
{
    $level = $data['players'][$x]['level'];
	$name = $data['players'][$x]['name'];
	$rank = $data['players'][$x]['rank'];
	$score = $data['players'][$x]['score'];
	$kills = $data['players'][$x]['kills'];
	$deaths = $data['players'][$x]['deaths'];
	$time = $data['players'][$x]['time'];
	$queue = $data['players'][$x]['queue'];
	$raw_time = sec2hms($data['players'][$x]['time']);
	$SPH = round($score/intval(intval($time)/3600),0);
	$ratio = round($kills/$deaths,2);
	$veteran = $data['players'][$x]['veteran'];
	$gameslost = $data['players'][$x]['general']['losses'];
	$gameswon = $data['players'][$x]['general']['games'] - $gameslost;
	$wlRatio = round($gameswon/$gameslost, 2);
	$dogtags = $data['players'][$x]['general']['dogt'];
	$acepins = $data['players'][$x]['pins'][18]['count'];
	$nextupdate = countdown($data['players'][$x]['date_lastupdate']);
	
	$last_updated = strtotime($data['players'][$x]['date_lastupdate']); 
	
	 //add 6 hours to the last updated timestamp 
	$next_update_calc = strtotime("+6 hours", $last_updated);  

	//convert the future timestamp into human readable format 
	$next_update = date('d/m/Y H:i',$next_update_calc);

	//$lastupdate = left($last_updated, 8);

	
	$pins = 0;
	$insignia = 0;
	for($i=0; $i<count($data['players'][$x]['pins']);$i++) {
		$count = $data['players'][$x]['pins'][$i]['count'];
		$pins = $count + $pins;
	}
	for($i=0;$i<count($data['players'][$x]['insiginias']);$i++) {
		$count = $data['players'][$x]['insiginias'][$i]['count'];
		$insignia = $count + $insignia;
	}	
	$bron = 0;
	$silv = 0;
	$gold = 0;
	$plat = 0;
	


	//calculate which class they use
	$assault = $data['players'][$x]['general']['sc_assault'];
	$recon = $data['players'][$x]['general']['sc_recon'];
	$demo = $data['players'][$x]['general']['sc_demo'];
	$support = $data['players'][$x]['general']['sc_support'];
	
	$the_classes = array();
	$the_classes[0] = array('name'=>'assault','score'=>$assault);
	$the_classes[1] = array('name'=>'recon','score'=>$recon);
	$the_classes[2] = array('name'=>'demo','score'=>$demo);
	$the_classes[3] = array('name'=>'support','score'=>$support);
	
	//sort the classes array by score
	usort($the_classes,'compare_stats2');
	
	$most_used = $the_classes[0]['name'];
	
	$NextRank=intval($rank+1);

	
	// Search next rank
	if( empty($NextRank) )
	$nextRankID = $rank;
	else {
		if($rankpoints[$rank] == $rankpoints[($rank + 1)])
		{
			$nextRankID = 0;
			for ($m=($rank + 1);$m<(count($rankpoints)-1);$m++)
			{
				if( ($rankpoints[$m] > $rankpoints[$rank]) && ($nextRankID == 0) ) $nextRankID = $m;
			}
		}
		else	$nextRankID = ($rank + 1);
	    }
	
	if($rank < 10) { $rank = "0".$rank; }
	
	if ($data['players'][$x]['queue'] != 0) {
		$update++;
	}

	$diff = time() - strtotime($data['players'][$x]['date_lastupdate']);
	
    if($data['players'][$x]['queue'] != "0") {
		$next_update = "<b><span title=\"Place in Queue: " . $data['players'][$x]['queue'] . "\" style=\"border-bottom: 1px dotted #f00;\">In Queue</span></b>";
	} else {
		if ($diff > 21600) {
			$next_update = "<i><span title=\"Last Update: " . countdown($diff) . "\" style=\"border-bottom: 1px dotted #f00;\">Unsure</span></i>";    
		} else {
			$next_update = countdown($diff);    
		}
	}
	
	$stats[$x] = array('level'=>$level,'name'=>$name,'rank'=>$rank,'score'=>$score,'kills'=>$kills,'deaths'=>$deaths,'ratio'=>$ratio,'time'=>$raw_time,'most_used'=>$most_used,'veteran'=>$veteran,'gameswon'=>$gameswon,'gameslost'=>$gameslost,'wlratio'=>$wlRatio, 'score_rank'=> $score - $rankpoints[$nextRankID-1],'diff_score'=> $rankpoints[$nextRankID] - $rankpoints[$nextRankID-1],'nextrankid'=> $nextRankID,'nextrankscore' => $rankpoints[$nextRankID], 'dogtags' => $dogtags, 'SPH' => $SPH, 'pins' => $pins, 'bronze' => $bron, 'silver' => $silv, 'gold' => $gold, 'plat' => $plat, 'insignia' => $insignia, 'nextupdate' => $next_update, 'lastupdate' => $last_update, 'queue' => $queue, 'acepins' => $acepins);	
}

function sec2hms ($sec, $padHours = false) 
{
	$hms = "";
	$days= intval(intval($sec) / 86400);
	$hms .= $days. 'd ';
	$hours = intval(($sec / 3600) % 24); 
	$hms .= ($padHours) 
		  ? str_pad($hours, 2, "0", STR_PAD_LEFT). ':'
		  : $hours. 'h '; 
	$minutes = intval(($sec / 60) % 60); 
	//$hms .= str_pad($minutes, 2, "0", STR_PAD_LEFT). 'm ';
	$seconds = intval($sec % 60); 
	//$hms .= str_pad($seconds, 2, "0", STR_PAD_LEFT).'s';
	return $hms;
}


function compare_stats2($x,$y)
{
	if($x['score'] == $y['score'])
	{
		return 0;
	}
	elseif($x['score'] < $y['score'])
	{
		return 1;
	}
	else
	{
		return -1;
	}
}

function compare_stats($x,$y, $k = 1)
{
	if ($k == 1) {
		if(isset($_GET['sort'])) {
			$sort = $_GET['sort'];
		} else {
			$sort = 'score';
		}
	} else {
		$sort = 'score';
	}
	
	if(isset($_GET['sortby'])) {
		$sortby = $_GET['sortby'];
	} else {
		$sortby = 1;
	}
	
	if($x[$sort] == $y[$sort])
	{
		return 0;
	}
	elseif($x[$sort] < $y[$sort])
	{
		return $sortby;
	}
	else
	{
		return -$sortby;
	}
}

//sort the stats array by score
usort($stats,'compare_stats');

	if(isset($_GET['sortby'])) {
		$sortby = $_GET['sortby'];
	} else {
		$sortby = 1;
	}
	?> 
	<table class="maintable" width="100%" border="0" cellpadding="0">
		<tr align='center'>
            <td align='center'><b>Skill Level</b><br /></a></td>
            <td align='center'><b>Most Used</b><br /></a></td>
			<td align='center'><b>Name</b><br /></a></td>
			<td align='center'><b>Rank</b><br /></a></td>
            <td align='center'><b>Progress</b><br /></a></td>
			<td align='center'><b>Score</b><br /></a></td>
			<td align='center'><b>K/D Ratio</b><br /></a></td>
			<td align='center'><b>W/L Ratio</b><br /></a></td>
			<td align='center'><b>Time</b><br /></a></td>
			<td align='center'><b>Dog Tags</b><br /></a></td>
			<td align='center'><b>Platin</b><br /></a></td>
			<td align='center'><b>Ace Pins</b><br /></a></td>
            <td align='center'><b>Total Pins</b><br /></a></td>
            <td align='center'><b>Last Update</b></a></td>
		</tr>

	<?php
	
	//sort($test['0']);
	foreach ($stats as $player) {

        echo "</td><td align='center'>".$player['level'];
        echo "</td><td align='center'>";
		echo "<img src='./images/".$player['most_used'].".png'/>";
        echo "</td><td align='left'><a href='http://bfbcs.com/stats_ps3/". $player['name']."'>";
                echo $player['name'];
        echo "</a>";
        echo "</td><td align='center'>";
		$rank = $player['rank'];
		if ($rank == "0") {$rank = "01"; $player['diff_score'] = 6500;}
		if ($player['score_rank'] == 0) {$player['score_rank'] = 1;}
		echo '<img src="images/small/'.$rank.'.png" />';
		echo '</td><td align="center" valign="center">';
		$max = $player['diff_score'];
		$current = $player['score_rank'];
		?><img src="status.php?max=<?php echo $player['diff_score']; ?>&current=<?php echo $player['score_rank']; ?>&color=FFFFFF&bgcolor=000000" /><?php
		echo "</td><td align='center'>";
		echo $player['score'];
		echo "</td><td align='center'>";
			$kdRatio = $player['ratio'];
			if($kdRatio >= 1) {
				echo "<font color='green'>$kdRatio</font>";
			} else {
				echo "<font color='red'>$kdRatio</font>";
			}
		echo "</td><td align='center'>";
			$wlRatio = $player['wlratio'];
			if($wlRatio >= 1) {
				echo "<font color='green'>$wlRatio</font>";
			} else {
				echo "<font color='red'>$wlRatio</font>";
			}
		echo "</td><td align='center'>";
		echo $player['time'];
		echo "</td><td align='center'>";
		echo $player['dogtags'];
		echo "</td><td align='center'>";
		echo $player['plat'];
		echo "</td><td align='center'>";
		echo $player['acepins'];
		echo "</td><td align='center'>";
        echo $player['pins'];
        echo "</td><td align='center'>";
        echo $player['nextupdate'];    
		//echo $player['nextupdate'];
		echo "</td></tr>";
	}
	
?>	</table>

<?php



//echo "<font align = 'left'><pre>";
//print_r($data);
//echo "</pre></font>";


?>

</body>
</html>
Hope this makes things a little more clear

Re: PHP Using SQL Query Search Results... Help

Posted: Mon Apr 26, 2010 3:01 pm
by Jonah Bron
Can you clarify what you're trying to do, and what the problem is? I don't quite understand what meant by "use the results the export my script".

Re: PHP Using SQL Query Search Results... Help

Posted: Tue Apr 27, 2010 4:13 am
by dave7802
Hi Jonah Bron

Sorry my bad, I edited my First post, i hope it clears things up a little, and makes things a little more clear,

Thanks :-)

Re: PHP Using SQL Query Search Results... Help

Posted: Tue Apr 27, 2010 9:04 am
by katierosy
I have a little script that uses a public API to gather some results,

But instead of me updating the script every time we have a new member to the website, i am trying to link the
username list to the database but failing very badly

You may look into this part of the code,you are attaching $final_$sars_members variable to the post data.
and If you already know that pf_psn_id fields in the table has the usernames of the players.

Code: Select all

<?php
	while($profileDetails = mysql_fetch_assoc($result)){          
	  $sars_members.=$profileDetails['pf_psn_id'].'&';	          
	}

	$final_$sars_members =  substr($sars_members,0,-1);
	$postdata = 'players='.$final_$sars_members.'&fields=all';
?>

Re: PHP Using SQL Query Search Results... Help

Posted: Tue Apr 27, 2010 9:49 am
by dave7802
Thanks very much :-)

I don't understand the 0,-1 though...