Radionica programiranje 34.

Potrebno predznanje

SQL upit: Svi gradovi u kojima žive naši polaznici
=====================================
<?php
$link = mysqli_connect("localhost", "root", "", "mariva");
$sql = "SELECT DISTINCT grad FROM polaznici_tecaja";
$result = mysqli_query($link, $sql);
While ($row = mysqli_fetch_assoc($result))
{
print $row["grad"] . "<br>";
}
?>

Objašnjenje
DISTINCT = "Design View/Properties/Unique value" u Accessu

SQL upit: Polaznici i njihovi tečajevi
============================================

<?php
$link = mysqli_connect("localhost", "root", "", "mariva");
$sql = "SELECT DISTINCT prezime, ime, naziv_tecaja
FROM tecajevi
INNER JOIN ((polaznici_tecaja
INNER JOIN racuni
ON polaznici_tecaja.id_polaznika = racuni.id_polaznika)
INNER JOIN stavke_racuna
ON racuni.id_racuna = stavke_racuna.id_racuna)
ON tecajevi.id_tecaja = stavke_racuna.id_tecaja
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["naziv_tecaja"] . "<br>";
}
?>

SQL upit: Lista polaznika koji su platili (equi-join str. 120 u knjizi Access 2000)
==============================================

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

SQL upit: Lista polaznika koji su platili (left-join str. 121 u knjizi Access 2000)
==========================================================

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

SQL upit: Lista polaznika koji su platili (right-join str. 121 u knjizi Access 2000)
==========================================================

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

SQL upit: Mentori (self-join str. 123 u knjizi Access 2000)
==========================================================

<?php
$link = mysqli_connect("localhost", "root", "", "mariva");
$sql = "SELECT profesori.prezime, profesori.ime, profesori_1.prezime
AS prezime_mentora, profesori_1.ime
AS ime_mentora
FROM profesori
LEFT JOIN profesori
AS profesori_1
ON profesori.izvjestava_osobu = profesori_1.id_profesora";
$result = mysqli_query($link, $sql);
While ($row = mysqli_fetch_assoc($result))
{
print $row["prezime"] . " " . $row["ime"] .
$row["prezime_mentora"] . " " . $row["ime_mentora"] . "<br>";
}
?>