Optimization my Query [MySQL + PHP ]

Optimization my Query [MySQL + PHP ]
0

#1

Hi guys,
I have statistic page on my website which I want to optimize.I’m just make it that work’s,but it’s not good and I know it.

I need to have all statistic information in an array.This is example:
I need to count for all “teams” how much they have “zones”.And after it set values on $data_chart6 (Keep on mind that I don’t use all Team ID… I don’t use ID 1,6,8,12,13 )

$mm = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘2’”);
$bdt = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘3’”);
$gsf = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘4’”);
$bal = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘5’”);
$lcn = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘7’”);
$rm = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘9’”);
$be = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘10’”);
$lms = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘11’”);
$tem = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘14’”);
$yak = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘15’”);
$gdf = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘16’”);
$bc = $Db->getRow(“SELECT COUNT(ID) AS total FROM zone Where Team = ‘17’”);

$data_chart6[0] = $mm[‘total’];
$data_chart6[1] = $bdt[‘total’];
$data_chart6[2] = $gsf[‘total’];
$data_chart6[3] = $bal[‘total’];
$data_chart6[4] = $lcn[‘total’];
$data_chart6[5] = $rm[‘total’];
$data_chart6[6] = $be[‘total’];
$data_chart6[7] = $lms[‘total’];
$data_chart6[8] = $tem[‘total’];
$data_chart6[9] = $yak[‘total’];
$data_chart6[10] = $gdf[‘total’];
$data_chart6[11] = $bc[‘total’];

How I can make this better?Thanks

GetRow and GetList function which I use(Here I use just GetRow)

public function getRow($sql = '')
{
if ($sql) {
if (!$this->execute($sql)) {
return false;
}
}
$row = null;
if ($this->_resc) {
$row = mysql_fetch_assoc($this->_resc);
}
return $row;
}

public function getList($sql = '', $type = 'arr')
{
if ($sql) {
if (!$this->execute($sql)) {
return false;
}
}
$rows = null;
if ($this->_resc) {
while ($row = ($type == 'obj') ? mysql_fetch_object($this->_resc) : mysql_fetch_array($this->_resc)) {
$rows[] = $row;
}
}
return $rows;
}

#2

Read about how to use GROUP BY and HAVING to create a single query which will return each ID with applicable count. Then all your results will be in a single fetch.


#3

Thanks.I’m read now about it and I think that this is good query for me:
SELECT COUNT(ID), Team FROM zone GROUP BY Team ORDER BY COUNT(Team) DESC

I get same results like from my 10 query :slight_smile: .
My only problem now is because I don’t know how to “store” it in array…
(I use Chart.js plugin so ID need to be same all time…)

$data_chart6[0] = …need to be always for same Team(In my case team 2)
$data_chart6[1] = in my case need to be for team 3 etc,etc…


#4

If you put an echo $row before the return statement of your function getRow, what does it look like?


#5

You should use sql “IN” operator. This will help you get started.


#6

Maybe try:

return array_values($row);

#7

Thank you.I’m make Child table so that i don’t need to use exact ID etc…

From big code in first post i’m get now just this: ((Loading page is faster for 10 +/- seconds))

$zone = $Db->getList("SELECT COUNT(zone.ID) as ukupno, Team, ZoneName.Ime,ZoneName.Boja FROM zone INNER JOIN ZoneName ON zone.Team = ZoneName.ID GROUP BY Team ORDER BY COUNT(Team) DESC");
$e = 0;
foreach($zone as $zona) {
	$data_chart6[$e] = $zona['ukupno'];
	$e ++;
}

Is this good now? Or there is maybe better way about foreach,$e etc…


#8

I was thinking based on your original post that $e would need to be very specific team numbers and not just an index, but if $e is fine as an index, then you can write get rid of the $e = 0; line and just use:

foreach($zone as $e=>$zona) {
	$data_chart6[$e] = $zona['ukupno'];
}

#9

Yeah,that works :slight_smile:
Thank you very much.