Keskustelu: Nettisivujen teko: listauksen tulostus

Osaisko joku pikkasen tökkästä minua oikeaan suuntaan.
Ongelma on seuraavanlainen: Haen kannasta paikkakunnat missä
toimintaa. Tarkoitus olisi siten että tulostaisin kaikki
paikkakunnat mitä kannassa on, mutta niissä missä toimintaa
Niin niihin tuli linkki toiseen sivulle missä kerrottaisiin'
Mitä toimintaa sillä paikkakunnalla olisi. Kyselyssä myös
laskenut että monta eri toimintaa paikkakunnalla on, ja tulostan sen listaukseen myös.

Tässä on myös sellanen juttu, että toiminnalla voi olla tietty ehto: Toiminta voi olla jonossa, hyltätään tai julkaistaan. Ja kun lasketaan kuika monta toimintaa paikkakunnalla on, niin lasketaan ne toiminnat mitkä on tilassa "julkaistaan".
Osasinkohan kertoa tarpeeksi vaikeasti :)

Tuossa on mitä olen tähän asti saanut aikaiseksi.

<?
// tuossa sql kyssäri
$sql="SELECT paikkakunta.paikkakunta_id, paikkakunta.paikkakunta,
Count(toiminta.toiminta_id) AS maara, julkaisu.julkaisu
FROM paikkakunta LEFT OUTER JOIN toiminta ON
paikkakunta.paikkakunta_id = toiminta.paikkakunta_id
LEFT OUTER JOIN julkaisu ON
toiminta.julkaisu_id = julkaisu.julkaisu_id
GROUP BY paikkakunta.paikkakunta_id,
paikkakunta.paikkakunta, julkaisu.julkaisu";

 // tästä alkaa kyselyn tulostus
$results = query($conn,$sql);
while (OCIFetchInto($results, &$results_out, OCI_ASSOC)){

if ($results_out['MAARA']> 0 && $results_out['JULKAISU']== 'julkaistaan')
{
echo "<A HREF=\"toiminta_valinta_paikkakunta.php?id=". $results_out['PAIKKAKUNTA_ID']
 ."\"TARGET='toiminnat'> " . results_out['PAIKKAKUNTA'] ."
</A>(" . $results_out['MAARA'] .")<br>";
}else{
echo "". $results_out['PAIKKAKUNTA'] ."<br>";
  }
}

?>

tuo yläpuolinen koodi tulostaa näin:

lahti(2)
lahti
helsinki(4)
helsinki
jyväskylä

Eli lahdessa on kaksi toimintaa jotka tilassa julkaistaan, mutta lahdessa myös toiminta joka tilassa jonoon. Sama juttu Helsingin osalla.
Pitäisi saada että tulostaa vain kerran tietyn kaupungin.

Jos sinulla taulu paikkakunnat tyyliin

id | nimi      | onkoTapahtumia | tapahtumat
--------------------------------------------

1    Espoo       ON               Konsertti
2    Helsinki    EI
3    Lahti       ON               Kokous

Sitten haet kaikki paikkakunnat käyt ne läpi vaikka forrilla. Jokaisessa kohdassa tarkistat onkoTapahtumiä kentää ON vai EI. Jos se on niin tulostetaan linkki esim. nayta_tapahtumat.php?id=1 sivulle. Tuossa tuo id on siis paikkakunnat id. Muuten tulostetaan vain tavallinen teksti.

Sitten tällä nayta_tapahtumat.php:ssa haet paikkakunnan tiedot saadun id:n perusteella.

Eli tyyliin

**paikkakunnat.php
<?php

//..tässä yhdistät tietokantaan jne....

$tulokset = mysql_query("SELECT * FROM paikkakunnat");

for($i = 0; $i < mysql_num_rows($tulokset); $i++)
{
   $onkoTapahtumia = mysql_result($tulokset, $i, "onkoTapahtumia");
   $id = mysql_result($tulokset, $i, "id");
   $nimi = mysql_result($tulokset,  $i, "nimi");

  if($onkoTapahtumia)
    echo "<a href=\"nayta_tapahtuma.php?id=$id\">$nimi</a><br />";
  else
    echo "$nimi<br />";
}

**nayta_tapahtuma.php

//.. tietokanta jne...

$id = $_GET['id'];

$tapahtuma = mysql_query("SELECT * FROM paikkakunnat WHERE id = '$id'");

if(mysql_num_rows($tapahtuma)>0)
{
   $id = mysql_result($tahtuma, 0, "id");
   $nimi = mysql_result($tapahtuma, 0, "nimi");
   $tapahtumat = mysql_result($tapahtuma, 0, "tapahtumat");
   $onkoTapahtumia = mysql_result($tapahtuma, 0, "onkoTapahtumia");

   if($onkoTapahtumia)
   {
      //tulosta tapahtuman tiedot jne...
   } else
   {
     echo "Virheellinen id";
   }
} else
{
  echo "Virheellinen id";
}

?>

Tuossa ei ole tietenkään huomioitu tietoturvaa jne...

Kyllä itse tekisin niin että paikkakunnan tiedot ja tapahtumat olisivat eri tauluissa (paikkakunnalla voi olla monta tapahtumaa, ja sama(nlainen) tapahtuma voi olla monella paikkakunnalla) ja sitten viitetaulu paikkakunta_tapahtuma
jossa olisi tiedot tyyliin

paikkakuntaID | tapahtumaID
---------------------------
1 | 1
1 | 2
2 | 1
2 | 3
3 | 2

Ja jotain tuohon tyyliin taitaa kysyjän tietokannassa ollakin.

Normalisointi kannattaa aina :)

Sitten hoitaisin asian niin että, hakisin paikkakunnat ja viitetauluista niihin liittyvät tapahtumat. Paikkakunnan tulostaminen vain kerran onnistuu varmasti DISTINCT määreellä, tyyliin:

$query = "SELECT DISTINCT paikkakuntaid, tapahtumaid FROM paikkakunta_tapahtuma WHERE paikkakuntaid = '$paikkakuntaID'";

Niin, taulut minulla on seuraavanlaiset:
Paikkakunnat tapahtumat julkaisu
---------------------------------------------
paikkakunta_id tapahtuma_id julkaisu_id
paikkakunta tapauhtuma julkaisu
paikkakunta_id
julkaisu_id

Ja tarkoitus olisi siis se että haen kannassa olevat kaikki paikkakunnat,
ja jos niillä tapahtumia joissa julkaisu on 'julkaistaan',
niin sen paikkakunan nimestä tulee linkki toiseen tiedostoon missä
lueteltuna mitä tapahtumia siellä on.
Sekä myös lasketaan kaikki tapahtumat joissa julkaisu= julkaistaan.

Oli sen verran mielenkiintoinen ongelma, että piti oikein väkisin vääntää ratkaisu...

Tauluni ovat seuraavanlaiset:

paikkakunta:
paikkakuntaID |paikkakunta
--------------------------
1             |Helsinki
2             |Jyväskylä
....

tapahtuma:
tapahtumaID |tapahtuma  |julkaistaan
------------------------------------
1           |jutajaiset |1
2           |rytäjäiset |2
...
arvo julkaistaan on numeroarvo seuraavanlaisin määrityksen (1=julkaistaan, 2=hylätään, 3=jonoon)

paikkakunta_tapahtuma
paikkakuntaID |tapahtumaID
--------------------------
1             |1
1             |2
2             |2
...

Monimutkaiset sql-kyselyt eivät todellakaan ole alaani, joten piti tehdä vähän rautalankakoodia:

<?php
$query = "SELECT * FROM paikkakunta";
$result = mysql_query($query);
while($rivi = mysql_fetch_array($result)) {
	hae_paikkakunnan_tapahtumat($rivi['paikkakuntaID'],$rivi['paikkakunta']);
}
function hae_paikkakunnan_tapahtumat($paikkakuntaID,$paikkakunta) {
	//haetaan tapahtumat, jotka julkaistaan
	$query = "SELECT * FROM tapahtuma WHERE julkaistaan = 1";
	$result = mysql_query($query) or die (mysql_error());
	echo $paikkakunta. ": ";
	$count = 0;
	while($rivi = mysql_fetch_array($result)) {
		hae_maara($rivi['tapahtumaID'],$paikkakuntaID,$count);
	}
	echo $count . "<br>";
}
function hae_maara($tapahtumaID,$paikkakuntaID,&$count) {
	$query = "SELECT paikkakuntaID FROM paikkakunta_tapahtuma WHERE paikkakuntaID = $paikkakuntaID AND tapahtumaID = $tapahtumaID";
	$result = mysql_query($query) or die (mysql_error());

	while ($rivi = mysql_fetch_array($result)) {
		$count++;
	}
}
?>

Pienen testauksen perusteella pitäisi toimia. Tulostaa paikkakunnan ja siihen liittyvien julkaistavien tapahtumien määrän. Linkkihärvelit voi tuohon helposti koodailla...

Tuo ei ole mikään hirveän suositeltava tapa ajaa SQL-kyselyitä loopissa (ajattele tauluun kaikki suomen kunnat, ja parisataa tapahtumaa => ~500 x 200 = 100 000 kyselyä), saatikka sitten kahdessa sisäkkäisessä loopissa. En jaksanut koko threadia lukea läpi, mutta veikkaan, että tuon saa hoidettua ihan yhdellä kyselylläkin.

Mikähän tietokanta mahtaa olla kyseessä? Onko kysely pakko groupata julkaisun perusteella? Miksei kyselyyn voi laittaa ehtoa WHERE julkaisu.julkaisu = 'julkaistaan' ? Niin ja myös INNER JOINin käyttö saattaa auttaa vai onko tuohon joku syy käyttää LEFT OUTER JOINia?

Edit: 500 x 200 on 100 000 :)

kannassa kyse on oracle. Jossa siis taulut ovat:
<l>tapahtuma</l>
----------
tapahtuma_id
tapahtuma
julkaisu_id
paikkakunta_id

<l>Julkaisu</l>
--------------
julkaisu_id
julkaisu

<l>paikkakunta</l>
-----------------
paikkakunta_id
paikkakunta

Yksinkertaisesti vielä: Tarkoitus on tulostaa kaikki paikkakunnat allekkain, mikä sinänsä ei olo ongelma. Mutta pitää ottaa huomioon että
missä paikkakunnalla on toimintaa, niin lasketaan COUNT:lla monta toimintaa ko. paikkakunnalla on,
sekä vielä tehdään paikkakunnasta linkki toiselle sivulle.
Eli lopputulos pitäisi olla jotakuin:

Helsinki (2)
Lahti (1)
Kouvola
Jyväskylä
Tampere (3)
...
...

Eli kysely täytyy groupata myös julkaisun perusteella? Mulla ei kokemusta löydy Oraclesta.

Juu ja ei taida INNER JOIN auttaa, sillä haluat tulostaa kaikki paikkakunnat, vaikka niillä ei olisikaan tapahtumia.

Kieltämättä mielenkiintoinen probleema. Täytyy perehtyä tuossa jossain vaiheessa paremmalla ajalla.

Tuolla alapuolisessä härvekkeellä toimii melkein :)
Eli tuo listaa kaikki paikkakunnat, ja missä tapahtuma, niin tulee linkki.
Ainut ongelma on se että tuo tekee kaikista paikkakunnista linkin
jos sillä paikkakunnalla on tapahtuma joka vaikka on julkaisu = jonoon, vaikka ainostaan niistä pitää tulla linkki missä julkaisu= 'julkaistaan'.
ARGH...

<?
$sql="SELECT paikkakunta.paikkakunta_id, paikkakunta.paikkakunta,
Count(tapahtuma.tapahtuma_id) AS maara
FROM paikkakunta LEFT OUTER JOIN tapahtuma ON
paikkakunta.paikkakunta_id = tapahtuma.paikkakunta_id
GROUP BY paikkakunta.paikkakunta_id, paikkakunta.paikkakunta";

$results = query($conn,$sql);
while (OCIFetchInto($results, &$results_out, OCI_ASSOC)){
 if ($results_out['MAARA']> 0 ){
echo "<A HREF=\"tapahtuma_valinta_paikkakunta.php?id=". $results_out['PAIKKAKUNTA_ID'] .
"\" TARGET='tapahtumat'> " . $results_out['PAIKKAKUNTA'] ."</A>(" . $results_out['MAARA'] .")<br>";
		}else {
	echo "". $results_out['PAIKKAKUNTA'] ."<br>";
  }
}
OCIFreeStatement($results);
OCILogoff($conn);

?>

Seuraava lause näyttäisi tulostavan oikean määrän tapahtumia, siis vain ne joissa julkaisu = 'julkaistaan', mutta kaupunkeja, joissa ei ole julkaistavia tapahtumia, ei tulosteta. Varmaan voit erikseen käydä paikkakunnat läpi ja tulostaa määrän alla olevasta kyselystä.

SELECT paikkakunta.paikkakunta_id, paikkakunta.paikkakunta,
Count(tapahtuma.tapahtumaID) AS maara
FROM paikkakunta LEFT OUTER JOIN tapahtuma ON
paikkakunta.paikkakunta_id = tapahtuma.paikkakuntaID
WHERE julkaisu = 'julkaistaan'
GROUP BY paikkakunta.paikkakunta_id, paikkakunta.paikkakunta

Sinänsä voisi tehdä niin että tekisi kaksi sql -lausetta.
Toisessa hakisi paikkakunnat missä tapahtumia (joissa julkaisu = julkaistaan),
sekä laskisi monta tapahtumaa kullakin paikkakunnalla on.

Sekä toisen sql -lauseen, johon hakisi paikkakunnat joissa ei ole yhtään tapahtumaa!

Mutta luulisi että tähän olisi hyvä ratkaisu jos saisi yhteen sql -lauseeseen kaikki

Voipi olla että saa tungettua yhteenkin lauseeseen. Kahdella kyselyllä voisi onnistua tähän tyyliin (ei testattu)

<?php

$sql = "SELECT paikkakunta.paikkakunta_id, paikkakunta.paikkakunta,
Count(tapahtuma.tapahtumaID) AS maara
FROM paikkakunta LEFT OUTER JOIN tapahtuma ON
paikkakunta.paikkakunta_id = tapahtuma.paikkakuntaID
WHERE julkaisu = 'julkaistaan'
GROUP BY paikkakunta.paikkakunta_id, paikkakunta.paikkakunta";

$tapahtumat = mysql_query($sql);

$query = "SELECT * FROM paikkakunta";
$paikkakunnat = mysql_query($query);

while($rivi = mysql_fetch_array($paikkakunnat)) {
	//paikkakunnan nimi
	echo "$rivi['paikkakunta']: ";
	// jos on tapahtumia jotka julkaistaan, tulostetaan
	if($paikkakunnat['paikkakunta_id'] == $tapahtumat['paikkakunta_id']) {
		echo $tapahtumat['maara'];	// + linkkihärvelit
	}
}
?>

Testasin pikaisesti ja näyttäisi toimivan:

SELECT
   paikkakunta.paikkakunta_id,
   paikkakunta.paikkakunta,
   COUNT(tapahtuma.tapahtuma_id) AS maara,
   julkaisu.julkaisu
FROM
   paikkakunta
LEFT JOIN
  tapahtuma ON paikkakunta.paikkakunta_id = tapahtuma.paikkakunta_id
RIGHT JOIN
   julkaisu ON tapahtuma.julkaisu_id = julkaisu.julkaisu_id
WHERE
   julkaisu.julkaisu = 'julkaistu'
GROUP BY
   paikkakunta.paikkakunta_id

Tuloste:

1  Hyvinkää   1  julkaistu
2  Helsinki   0  julkaistu
3  Tampere    0  julkaistu
4  Turku      0  julkaistu

Eli RIGHT JOININ takia kaikki näyttäisivät olevan 'julkaistuja', mutta eipä meitä kiinnosta onko olematon tapahtuma julkaistu vai ei :)

Kannattaa debugata noita kyselyitä jossain Oraclen omassa työkalussa ja sitten kun saat tuloksen sen näköiseksi, että osaat sitä PHP:llä parsia, niin siirryt vasta sinne varsinaisen softan puolelle.

ei toimi tuokaan halutulla tavalla.

tää on yhtä nysväämistä :)
Pitää ottaa joulurauha, joulun jälkeen taas kokeilisi jos vaikka aivot toimisi paremmin!

Ei muuta kun jouluja, ja kiitos jo saaduista neuvoista!