MastodonStartpage/web/admin/instancesh.php

<?php

header('Content-Type: application/json; charset=utf-8');

require('include/glob.php');
require('include/muoribene.php');
require('include/sessionstart.php');

$cols=array(

	'Instances.LocalityID'=>array('select'=>'SELECT Localities.ID AS Lid, CONCAT(Locality,\' (\',Province,\', \',Region,\', \',State,\') (\',COUNT(Localities.ID),\')\') AS Txt FROM Instances LEFT JOIN Localities ON Localities.ID=Instances.LocalityID LEFT JOIN Provinces ON Provinces.ID=Localities.ProvinceID LEFT JOIN Regions ON Regions.ID=Provinces.RegionID LEFT JOIN States ON States.ID=Regions.StateID WHERE Instances.LocalityID IS NOT NULL GROUP BY Lid ORDER BY Localities.Locality ASC, Provinces.Province ASC, Regions.Region ASC, States.State ASC','optcol'=>'Lid','txtcol'=>'Txt'),

	'Provinces.ID'=>array('select'=>"SELECT Provinces.ID AS Pid, CONCAT(Province,' (',Region,', ',State,') (',COUNT(Localities.ID),')') AS Txt FROM Instances LEFT JOIN Localities ON Localities.ID=Instances.LocalityID LEFT JOIN Provinces ON Provinces.ID=Localities.ProvinceID LEFT JOIN Regions ON Regions.ID=Provinces.RegionID LEFT JOIN States ON States.ID=Regions.StateID WHERE Instances.LocalityID IS NOT NULL GROUP BY Pid ORDER BY Provinces.Province ASC, Regions.Region ASC, States.State ASC",'optcol'=>'Pid','txtcol'=>'Txt'),

	'Regions.ID'=>array('select'=>"SELECT Regions.ID AS Rid, CONCAT(Region,' (',State,') (',COUNT(Regions.ID),')') AS Txt FROM Instances LEFT JOIN Localities ON Localities.ID=Instances.LocalityID LEFT JOIN Provinces ON Provinces.ID=Localities.ProvinceID LEFT JOIN Regions ON Regions.ID=Provinces.RegionID LEFT JOIN States ON States.ID=Regions.StateID WHERE Instances.LocalityID IS NOT NULL GROUP BY Rid ORDER BY Regions.Region ASC, States.State ASC",'optcol'=>'Rid','txtcol'=>'Txt'),

	'States.ID'=>array('select'=>"SELECT States.ID AS Sid, CONCAT(State,' (',COUNT(States.ID),')') AS Txt FROM Instances LEFT JOIN Localities ON Localities.ID=Instances.LocalityID LEFT JOIN Provinces ON Provinces.ID=Localities.ProvinceID LEFT JOIN Regions ON Regions.ID=Provinces.RegionID LEFT JOIN States ON States.ID=Regions.StateID WHERE Instances.LocalityID IS NOT NULL GROUP BY Sid ORDER BY States.State ASC",'optcol'=>'Sid','txtcol'=>'Txt'),

	'InstLangs.LangID'=>array('select'=>'SELECT Languages.ID AS Lid, CONCAT(Code, \' [\', NameIT, \'] (\', COUNT(Languages.ID), \')\') AS Txt FROM InstLangs LEFT JOIN Languages ON Languages.ID=LangID GROUP BY Languages.ID ORDER BY Code ASC','optcol'=>'Lid','txtcol'=>'Txt'),

	'InstLangs.LangID.First'=>array('select'=>'SELECT Languages.ID AS Lid, CONCAT(Code, \' [\', NameIT, \'] (\', COUNT(Languages.ID), \')\') AS Txt FROM InstLangs LEFT JOIN Languages ON Languages.ID=LangID WHERE InstLangs.Pos=1 GROUP BY Languages.ID ORDER BY Code ASC','optcol'=>'Lid','txtcol'=>'Txt'),

	'InstOurLangs.OurLangID'=>array('select'=>'SELECT Languages.ID AS Lid, CONCAT(Code, \' [\', NameIT, \'] (\', COUNT(Languages.ID), \')\') AS Txt FROM InstOurLangs LEFT JOIN Languages ON Languages.ID=OurLangID GROUP BY Languages.ID ORDER BY Code ASC','optcol'=>'Lid','txtcol'=>'Txt'),

	'InstOurLangs.OurLangID.First'=>array('select'=>'SELECT Languages.ID AS Lid, CONCAT(Code, \' [\', NameIT, \'] (\', COUNT(Languages.ID), \')\') AS Txt FROM InstOurLangs LEFT JOIN Languages ON Languages.ID=OurLangID WHERE InstOurLangs.Pos=1 GROUP BY Languages.ID ORDER BY Code ASC','optcol'=>'Lid','txtcol'=>'Txt'),

	'InstFinancing.FinID'=>array('select'=>'SELECT Financing.ID AS Fid, CONCAT(Type,\' (\',COUNT(Financing.ID),\')\') AS Txt FROM InstFinancing LEFT JOIN Financing ON Financing.ID=FinID GROUP BY Financing.ID ORDER BY Type ASC','optcol'=>'Fid','txtcol'=>'Txt'),

	'InstFinancing.FinID.First'=>array('select'=>'SELECT Financing.ID AS Fid, CONCAT(Type,\' (\',COUNT(Financing.ID),\')\') AS Txt FROM InstFinancing LEFT JOIN Financing ON Financing.ID=FinID WHERE InstFinancing.Pos=1 GROUP BY Financing.ID ORDER BY Type ASC ','optcol'=>'Fid','txtcol'=>'Txt'),

	'InstPolicies.PolID'=>array('select'=>'SELECT Policies.ID AS Pid, CONCAT(Name,\' (\',COUNT(Policies.ID),\')\') AS Txt FROM InstPolicies LEFT JOIN Policies ON Policies.ID=PolID GROUP BY Policies.ID ORDER BY Name ASC','optcol'=>'Pid','txtcol'=>'Txt'),

	'InstPolicies.PolID.First'=>array('select'=>'SELECT Policies.ID AS Pid, CONCAT(Name,\' (\',COUNT(Policies.ID),\')\') AS Txt FROM InstPolicies LEFT JOIN Policies ON Policies.ID=PolID WHERE InstPolicies.Pos=1 GROUP BY Policies.ID ORDER BY Name ASC','optcol'=>'Pid','txtcol'=>'Txt'),

	'InstTags.TagID'=>array('select'=>'SELECT Tags.ID AS Tid, CONCAT(Name,\' (\',COUNT(Tags.ID),\')\') AS Txt FROM InstTags LEFT JOIN Tags ON Tags.ID=TagID GROUP BY (Tags.ID) ORDER BY Name ASC','optcol'=>'Tid','txtcol'=>'Txt'),

	'InstTags.TagID.First'=>array('select'=>'SELECT Tags.ID AS Tid, CONCAT(Name,\' (\',COUNT(Tags.ID),\')\') AS Txt FROM InstTags LEFT JOIN Tags ON Tags.ID=TagID WHERE InstTags.Pos=1 GROUP BY (Tags.ID) ORDER BY Name ASC','optcol'=>'Tid','txtcol'=>'Txt'),
);

if (array_key_exists('key',$_GET) && array_key_exists($_GET['key'],$cols)) {
	require('include/myconn.php');
	$res=mysqli_query($link,$cols[$_GET['key']]['select'])
		or muoribene(mysqli_error($link),true);
	mysqli_close($link);
	$buf=array();
	while ($row=mysqli_fetch_assoc($res))
		$buf[]=array($row[$cols[$_GET['key']]['optcol']],$row[$cols[$_GET['key']]['txtcol']]);
	echo(json_encode($buf));
}

exit(0);

?>