Radionica programiranja 37.

Potrebno predznanje.
Sql upit: Koliko je računa izdano svakom polazniku

================================================

<?php
$link = mysqli_connect("localhost", "root", "", "mariva");
$sql = "SELECT prezime, ime, Count(racuni.id_racuna) AS CountOfID_racuna
FROM polaznici_tecaja
INNER JOIN racuni
ON polaznici_tecaja.id_polaznika = racuni.id_polaznika
GROUP BY prezime, ime
ORDER BY prezime, ime";
$result = mysqli_query($link, $sql);
While ($row = mysqli_fetch_assoc($result))
{
print $row["prezime"] . " " . $row["ime"] . " " . $row["CountOfID_racuna"] . "<br>";
}
?>

SQL upit: Kolike je ukupno isznose_racuna uplatio pojedini polaznik
===========================================================

<?php

$link = mysqli_connect("localhost", "root", "", "mariva");

$sql = "SELECT prezime, ime, Sum(iznos_racuna) 

AS SumOfIznos_racuna

FROM polaznici_tecaja 

INNER JOIN racuni 

ON polaznici_tecaja.id_polaznika = racuni.id_polaznika

GROUP BY prezime, ime

ORDER BY prezime, ime";

$result = mysqli_query($link, $sql);

While ($row = mysqli_fetch_assoc($result))

{

print $row["prezime"] . " " . $row["ime"] . " " . $row["SumOfIznos_racuna"] . "<br>";

}

?>

ili LEFT JOIN:

<?php

$link = mysqli_connect("localhost", "root", "", "mariva");

$sql = "SELECT prezime, ime, Sum(iznos_racuna) 

AS SumOfIznos_racuna

FROM polaznici_tecaja 

LEFT JOIN racuni 

ON polaznici_tecaja.id_polaznika = racuni.id_polaznika

GROUP BY prezime, ime

ORDER BY prezime, ime";

$result = mysqli_query($link, $sql);

While ($row = mysqli_fetch_assoc($result))

{

print $row["prezime"] . " " . $row["ime"] . " " . $row["SumOfIznos_racuna"] . "<br>";

}

?>

SQL upit: Koliko učenika ima u svakom razredu
==========================================================

<?php
$link = mysqli_connect("localhost", "root", "", "mariva");
$sql = "SELECT razredi.id_razreda, Count(id_polaznika)
AS CountOfID_polaznika
FROM razredi
INNER JOIN uvjerenja
ON razredi.id_razreda = uvjerenja.id_razreda
GROUP BY razredi.id_razreda";
$result = mysqli_query($link, $sql);
While ($row = mysqli_fetch_assoc($result))
{
print $row["id_razreda"] . " " . $row["CountOfID_polaznika"] . "<br>";
}
?>

SQL upit: Svi učenici prof. Zdenke i njihovi telefoni
=======================================================

<?php
$link = mysqli_connect("localhost", "root", "", "mariva");
$sql = "SELECT polaznici_tecaja.prezime, polaznici_tecaja.ime, polaznici_tecaja.telefon
FROM profesori
INNER JOIN (razredi
INNER JOIN (polaznici_tecaja
INNER JOIN uvjerenja
ON polaznici_tecaja.id_polaznika = uvjerenja.id_polaznika)
ON razredi.id_razreda = uvjerenja.id_razreda)
ON profesori.id_profesora = razredi.id_profesora
WHERE profesori.ime = 'Zdenka'
ORDER BY polaznici_tecaja.prezime, polaznici_tecaja.ime";
$result = mysqli_query($link, $sql);
While ($row = mysqli_fetch_assoc($result))
{
print $row["prezime"] . " " . $row["ime"] . " " .
  $row["telefon"] . "<br>";
}
?>