Please some help with sql query

[pinklady]

Hi all,

I wonder if you can help me 🙂 I am trying to display some info from the database, but it is not working.

I am trying to show the country from each member on the arcade highscores page, but my code is not working. I was hoping one of you can help me with the code. 🙂

I'm trying to get the country from the themes table from variable "country" and the "value" for the country they have selected.

Like on this image:
Database themes:

I need to put this code in the file ArcadePlay.php and this underneath is what i got so far, but isn't working. In red is the code i've added. I attached my ArcadePlay.php in case someone can help me and need more info from the file.

[size=12pt]Code:[/size]

// How many scores there are
$query = "
	SELECT count(*) AS sc
	FROM  {$db_prefix}arcade_scores
	WHERE id_game = $game[id]";
	$scoreCount = Arcade_Get_Row($query);

 if (isset($game['score_type']) && $game['score_type'] == 3) {$game['score_type'] = 0;}

$score_sort = $game['score_type'] == 0 ? 'DESC' : 'ASC';

// Actual query	
$query = "
	SELECT
		a.id_score, a.score, a.end_time AS time, a.game_duration AS duration, a.comment,
		a.position, a.score_status, IFNULL(mem.id_member, 0) AS id_member, IFNULL(mem.{$smfChanges['member_name']}, '') AS member_name, [color=red][b]c.ID_MEMBER, c.variable, c.value[/b][/color]
	FROM  {$db_prefix}arcade_scores AS a
	LEFT JOIN {$db_prefix}members AS mem ON (mem.id_member = a.id_member)
	[color=red][b]LEFT JOIN {$db_prefix}themes AS c ON (c.ID_MEMBER = a.id_member )[/b][/color]
	WHERE id_game = $game[id]
	ORDER BY position
	LIMIT $_REQUEST[start], $scoresPerPage";

$context['arcade']['scores'] = array();
$context['arcade']['game']	= $game;

$request = Arcade_Get_Array($query);

	foreach($request as $score)
	{
	censorText($score['comment']);
	$own = $id_member == $score['id_member'];

	$context['arcade']['scores'][] = array(
		'id' => $score['id_score'],
		'own' => $own, 
		'memberLink' => !empty($score['member_name']) ? '<a href="' . $scripturl . '?action=profile;u=' . $score['id_member'] . '">' . $score['member_name'] . '</a>' : $txt['arcade_guest'],
		'score' => round($score['score'],3),
		'time' => timeformat($score['time']),
		'duration' => $score['duration'],
		'position' => $score['position'],
		'score_status' => $score['score_status'],
		'comment' => parse_bbc(!empty($score['comment']) ? $score['comment'] : $txt['arcade_no_comment']),
		'raw_comment' => $score['comment'],
		'highlight' => $newScore ? $score['id_score'] == $newScore_id : false,
		'can_edit' =>  $newScore ? false : $own ? ($context['arcade']['can_comment_own'] || $context['arcade']['can_comment_any']) : $context['arcade']['can_comment_any'], 
		'edit' => isset($_REQUEST['edit']) && isset($_REQUEST['score']) && (int) $_REQUEST['score'] == $score['id_score'],
		[color=red][b]'country' => $score['value'],[/b][/color]

Greets,

Pinklady 🙂

[BIOSTALL]

I think you need to change your last line from:

'country' => $score['value'],

to:

'country' => $score['value']);

Hope that helps,

BIOSTALL