NarKampen/dbFiles/Categories.php

<?php
/* Games.php */ 
	$hostname = 'localhost';
	$username = 'narKampen';
	$pass = 'IfRLzj2HJBXA9eei';
	$database = 'narKampen';
	
	$conn = new mysqli($hostname, $username, $pass, $database);
	if (!$conn) {
		die("Connection Failed. ". mysqli_connect_error());
	}
	mysqli_set_charset($conn,'utf8');
		
	$gameId = $_POST['gameId'];

	$categoryFilter = "";
	if ($gameId == -1) {
		$categoryFilter = "(SELECT id FROM category)";
	} else {
		$categoryFilter = "(SELECT categoryId FROM gameCategories WHERE gameId = $gameId)";
	}

	//$sql = "SELECT id, name, r, g, b ,a FROM category";
	$sql = "SELECT category.*, count(*) as count FROM questions INNER JOIN questionToCategory ON questions.id = questionToCategory.questionId INNER JOIN category ON questionToCategory.categoryId = category.id WHERE category.id IN $categoryFilter GROUP BY category.name";
	$result = $conn->query($sql);
	
	if ($result->num_rows > 0) {
		$returnArray = array();
		$i = 0;
		while ($data = $result->fetch_assoc()) {
			foreach ($data as $key => $value) {
				$returnArray[$i][$key] = $value;
			}
			$i++;
		}
		echo json_encode($returnArray);
		
	} else {
		echo "ERROR - No Categories found";
	}
	$conn->close();
	
?>