Optimizare Cod PHP

[bubbles]

Am urmatorul cod :

<?php
if (isset($_GET["pag"])) { $page  = $_GET["pag"]; } else { $page=1; }; 
$start_from = ($page-1) * 30; 
$result = mysqli_query($mysqli,"select * from channel ORDER BY name ASC LIMIT $start_from, 30");

while($row = mysqli_fetch_array($result)) { 
$catt=strtolower($row['name']);

$resultcat = mysqli_query($mysqli,"SELECT COUNT(category_embed) FROM video WHERE category_embed ='$catt'");
$rowcat = mysqli_fetch_row($resultcat); 
if ($rowcat[0]>=0) {

echo $row['name']." - ".$rowcat[0];
echo strtolower($row['name']);
          } 
} 

$result2 = mysqli_query($mysqli,"SELECT COUNT(name) FROM channel"); 
$row = mysqli_fetch_row($result2); 
$total_records = $row[0]; 
$total_pages = ceil($total_records / 30); 


echo "<ul id='pages'>";
for ($i=1; $i<=$total_pages; $i++) { 
if ($i==1) {$link="categories/";} else {$link="categories/pag/".$i."/";}
            echo "<li><a href='".$link."'>".$i."</a></li>"; 
}; 
echo "</ul>";
mysqli_close($mysqli);
?>

Am impresia ca timpul de executie al paginii este mare din cauza interogarii din acel while.

Cum il pot scrie altfel ?

[gafi]

Pai poti sa faci o singura interogare sql in loc de 2,

select * from channel ORDER BY name ASC LIMIT $start_from, 30

si

SELECT COUNT(category_embed) FROM video WHERE category_embed ='$catt'

Cat de bine stii mysql? In rest eu nu vad ce altceva poti face!

[GarryOne]

Sa faci interogari intr-un loop e cea mai mare prostie.

[bubbles]

GarryOn imi poti spune cum sa scriu o alta alternativa optimizata ?

[GarryOne]

Ceva de genu:

$all_cat = '';
foreach ($row as $value) {
	$all_cat .= strtolower($row['name']) . ',';
}
$all_cat = substr($all_cat, 0, -1); //scapam de ultima virgula
$resultcat = mysqli_query($mysqli,"SELECT COUNT(category_embed) FROM video WHERE category_embed IN '$all_catt'"); 

[Vlachs]

De ce nu il invatati sa elibereze resursele, programatorii lu peste.

Edit: poti sa faci un multiquery, vad ca folosesti mysqli deci ar fi posibil, ceva de genu asta

SELECT category_embed, COUNT(*) AS videos FROM video GROUP BY category_embed

pus pe cron(cache emulate)

Edited June 5, 2013 by Vlachs