Keskustelu: Nettisivujen teko: MySQL: virhe LEFT JOIN -kyselyssä

Kyseinen koodi on ollut toiminnassa, mutta left join lisäyksen jälkeen valittaa että ei toimi..

<?php
  $samDB = mysql_connect( "***********", "*****", "************" );
  mysql_select_db( "samdb", $samDB);

$res = mysql_query( " SELECT *
                      FROM songlist
                      LEFT OUTER JOIN categorylist
                      ON songlist.songID = categorylist.songID
                      WHERE (songtype='S')
                      AND (status=0)
                      ORDER BY artist, album, trackno",
                    $samDB );

$result = array();

while ($x = mysql_fetch_assoc($res)) {
  $result[] = $x;
}
mysql_free_result($res);
?>

Ette sattuisi näkemään siinä jotain vikaa..

Valitus siis oli:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/stagerad/public_html/hallinto/kaplista.php on line 25

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/stagerad/public_html/hallinto/kaplista.php on line 28

ja siellä sijaitsee tämä osa:

<?
while ($x = mysql_fetch_assoc($res)) {
  $result[] = $x;
}
mysql_free_result($res);
?>

Kiitos.

(Mod. huom: Kun kerran tiedät, missä virhe tulee, niin turhaan laitat kaikkia sen jälkeisiä rivejä.)

MySQL-debuggauksen alkeet: tee aina oma kyselyfunktio, joka ilmoittaa virheistä, ja käytä mysql_error-funktiota virheilmoituksen selvittämiseen.

<?php
function mysql_query_dbg($q, $l = null) {
  $r = isset($l) ? mysql_query($q, $l) : mysql_query($q);
  if (!$r) {
    $q = htmlspecialchars($q);
    $e = htmlspecialchars(mysql_error());
    die("<h1>Virhe kyselyssä!</h1><p>$q</p><p>$e</p>");
  }
  return $r;
}
?>

Kiitos vastauksesta .. sainkin toimimaan paremmin jo ..
enään valittaa että Column 'ID' in field list is ambiguous .. joka viittanee songlist.ID seen. (joka oli kirjoitettuna songlist.songID)

tässäpä tämä uusin versio.

<?
$res = mysql_query( " SELECT ID, artist, album, title, trackno
                      FROM songlist
                      LEFT OUTER JOIN categorylist
                      ON songlist.ID = categorylist.songID
                      WHERE songtype LIKE 'S' AND status LIKE '0'
                      ORDER BY artist, album, trackno"
                   ) or die('Error: ' . mysql_error());
?>

valittaakohan nyt sitä että kun kummassakin taulussa on kenntä ID. vai mistä tuo virhe ilmaantuu?

kokeile muuttaa

<?
$res = mysql_query( " SELECT ID, artist, album, title, trackno
                      FROM songlist
                      LEFT OUTER JOIN categorylist
                      ON songlist.ID = categorylist.songID
                      WHERE songtype LIKE 'S' AND status LIKE '0'
                      ORDER BY artist, album, trackno"
                   ) or die('Error: ' . mysql_error());
?>

näin

<?
$res = mysql_query( " SELECT songlist.ID, songlist.artist, songlist.album, songlist.title, songlist.trackno
                      FROM songlist
                      LEFT OUTER JOIN categorylist
                      ON songlist.ID = categorylist.songID
                      WHERE songlist.songtype LIKE 'S' AND songlist.status LIKE '0'
                      ORDER BY songlist.artist, songlist.album, songlist.trackno"
                   ) or die('Error: ' . mysql_error());
?>

jotta tietokantapalvelin tietää kumman taulun kenttiä tarkoitat

Noniin .. tuohan auttoi.

Sitten seuraavaan ongelmaan :D

seuraavassa koodissa listataan kaikki artistin niitten albumit ja biisit.
tarkoituksena oli saada niiden jälkeen ruksilaatikko joka olisi ruksittu jos kappale olisi main soittolistassa jonka id on 4 ja ei ruksittu jos se ei ole.

Nyt kuitenkin kaikki kappaleet ovat mukamas main soittolistalla. Alla koodi

<?
$result = array();

while ($x = mysql_fetch_assoc($res)) {
  $result[] = $x;
}
mysql_free_result($res);

echo "<h2>STAGERADIO KAPPALELISTA</h2>";
echo "<table>";

$artist = $album = null;
foreach ($result as $row) {
  if ($artist != $row["artist"]) {
    $artist = $row["artist"];
    $album = null;
    echo "<tr><td id'artist' colspan='4'>$artist\n</td></tr>";
  }
  if ($album != $row["album"]) {
    $album = $row["album"];
    echo "<tr><td></td><td id='album' colspan='3'>$album</td></tr>\n";
  }
  $song = $row["title"];
  $track = $row["trackno"];
  echo "<tr><td></td><td></td><td id='track'>$track - $song</td><td id='ruksit'>main ";
    if ($row["categoryID"] = 4) {
    echo "<input type='checkbox' name='sop' value='sop' checked='yes'>";
    }
    else {
    echo "<input type='checkbox' name='sop' value='sop' checked='no'>";
    }
  echo "hitti <input type='checkbox' name='kat' value='kat'>muu1 <input type='checkbox' name='sop' value='sop'>muu2 <input type='checkbox' name='kat' value='kat'>soittov&auml;li <input type='text' name='svali' size='4' id='vali'/></td></tr>\n";
}
?>

Käytät checked-attribuuttia väärin. Jos laatikkoa ei ole valittu, checked-attribuuttia ei kuulu laittaa ollenkaan, ja jos taas on, kuuluu kirjoittaa checked="checked". Mitään yes-no-arvoja ei ole.

<input type="checkbox">
<input type="checkbox" checked="checked">

Kiitokset vastauksesta.
Jossain esimerkissä kun katselin niin oli merkattu että checked="yes" .. tiedä sitten miksi.

Mutta tossa kun pistelin lasta nukkumaan niin rupesin miettimään että onkohan minulla edes oikea lähtökohta koko koodin pätkäni rakentamiseen. Tarkoitus olisi saada kappale lista ja jokaisen kappaleen kohdalle ruksit että missä soittolistoissa se on. Siis yksi kappale saattaa olla monessa soittolistassa.

Tietokannat ovat tehty näin . (ja huom! niitä ei voi mennä muuttamaan)

songlist
-ID (sama kuin categorylist.songID)
-artist
-title
-album
-trackno

categorylist
-ID (henk.koht. id)
-songID (sama kuin songlist.ID)
-categoryID (kategorian ID)
-sortID (en tiedä mikä virka tällä on)

sitten on vielä category taulu mikä sisältää categoryID:n nimen ja jotain muuta infoa, mutta tarvittaessa sieltä voi sitten hakea sen nimen ainakin. Eli.

category
-ID
-name
-parentID
-levelindex
-itemindex

Miten hakua pitäisi lähteä hakemaan oikea oppisesti?

Kiitos

Erityisesti nettisivujen ja -ohjelmoinnin suhteen pitää olla erittäin lähdekriittinen, koska tällä alalla "oppaita" ja muuta materiaalia kirjoittavat harvinaisen paljon myös ne, joilla ei ole asioista sen kummempaa hajua, kuin että he ovat testanneet koodiaan kerran tai pari omalla suosikkiselaimellaan. Kannattaa siis lukea sivuja, jotka kertovat standardeista ja niitä noudattavista selaimista. Eksoottisempiin ongelmiin (tai tietyn selaimen bugien kiertämiseen) voi sitten hakea tietoa hieman alemminkin laatukriteerein, jos hyvää ratkaisua ei tunnu olevan.

Haetko suunnilleen tällaista rakennetta?

kappale     rock rap  70's 90's
Oi Beibe         X         X
Kakerock    X         X

Tässä tapauksessa listaa ensin kaikki kategoriat suoraan kategoriataulusta ja hae sitten kappaleet vaikkapa tällaisella kyselyllä (MySQL!):

SELECT
  *,
  (
    SELECT
      GROUP_CONCAT(DISTINCT ID ORDER BY ID SEPARATOR ",")
    FROM
      categorylist
    WHERE
      categorylist.songID = songlist.ID
  ) AS 'categorylist'
FROM songlist

Voit sitten muuttaa kategorialistan uuteen muotoon PHP:llä:

<?php
// Oletetaan, että
// $songlist sisältää yo. kyselyn tulokset, ja
// $category sisältää kategoriahaun tulokset niin, että ID on avaimena.
$songs = array();
foreach ($songlist as $song) {
  // Puretaan kategorialista taulukoksi, jossa category.ID on avaimena
  $song["categorylist"] = array_flip(explode(",", $song["categorylist"]));
  // Merkataan puuttuviin kategorioihin false ja löytyineisiin true
  foreach ($category as $id => $c) {
    $song["categorylist"][$id] = !isset($song["categorylist"][$id]);
  }
  $songs[$song["ID"]] = $song;
}
unset($songlist);

/* Nyt kappaleiden pitäisi olla tällaisessa muodossa:
$songs = array(
  song.ID => array(
    "ID" => song.ID,
    "title" => song.title,
    // ...
    "categorylist" => array(
      category.ID => true|false,
      category.ID => true|false,
      // ...
    )
  ),
  // ...
);
*/
?>

Tästä saatkin jo helposti tulostettua kappaleet taulukoksi.

Metabolix kirjoitti:

Haetko suunnilleen tällaista rakennetta?

kappale     rock rap  70's 90's
Oi Beibe         X         X
Kakerock    X         X

Kyllä aivain oikein. Ja tietysti pitäisi tulostaa myös kappaleet jotka ei ole millään soittolistalla.
Tarkoituksena olisi siis luoda täydellinen lista kappaleista jota voi tarpeen tullen suodattaa artistin mukaan ja joka kertoisi että onko kappaleet jollain soittolistalla.
tarkoitus olisi myös saada lista toimimaan niin että jos ruksailen jonkun kappaleen esimerkiksi vaikka kasari soittolistalle niin se tallentuisi tietokantaan. Mielummin vielä niin että voi valita useamman kerrallaan ;)

Tiedän iso pala purtavana, ainakin aloittelijalle, mutta pala palalta ne talotkin rakennetaan. :D

Ja olisi tietysti kiva jos se tulostus olisi muotoa

              main  70's
artisti1
--albumi1
----kappale1   x     x
----kappale2         x
----kappale3
----kappale4   x
--albumi2
----kappale1         x
---- ... ...
artisti2
.......

Kappaleita on tuhansia joten jos pelkät kappaleen nimet tulostuu, niin kovin hyvää kuvaa ei välttämättä tulostu päähän että mikähän kappale se oikein on :)

No mikä nyt on ongelmasi? Annoin jo kyselyn ja koodin, jolla saat nuo soittolistarastit koottua näppärästi, joten sinulle ei jäänyt tehtäväksi enää kuin tulostus, johon olen myös muistaakseni aiemmassa keskustelussa antanut sopivan silmukan. Kategoriarastit tulostat joka kappaleelle silmukalla, joka käy läpi kaikki kategoriat.

Juu en tässä mitään vielä oikeastaan olekkaan .. en ole vielä kerennyt kirjoittamaasi koodiin tutustumaan, mutta enköhän illalla pääse tutkimaan sitäkin.

Suur kiitokset avustasi. Palaan asiaan jos ilmenee ongelmia koodin suhteen.

** Edit

Noniin .. kokeilin tässä pistää tuota koodiasi käytäntöön, mutta ajauduin hetimiten ongelmaan:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/stagerad/public_html/hallinto/kaplist.php on line 41
Error:

Eli antamasi haku näytti tuollaisen virheen ja kuinka ollakkaan, vaikka laitoin or die('Error: ' . mysql_error()); niin kuten huomataan niin virhekoodi ei tulostu ollenkaan. En sitten tiedä että miten vikaa pitäisi lähteä korjaamaan.

Ideoita?

Käytät kyselyissä, jotain $yhteys muuttujaa johon ole johon et ole määrittänyt aikaisemmin tai siellä on jotain ihan muuta kun linkin muodostamis tiedot. Tai vaihtoehtoisesti annat väärässä järjestyksessä parametrit mysql_queryille tai jotain muuta hazardia. Voi myös olla, että kysely ei palauta mitään ja näin ollen result setti on tyhjä ja sitten kun yrität hakea result setistä tietoja esim. funktioilla mysql_fetch_row() tai mysql_fetch_assoc(), niin tulee tuo varoitus, koska sitä result settiä ei ole / se on tyhjä.

Johtusko siitä, tuo ettei tule mysql_errorilla mitään, että tuo ei ole error vaan varoitus niinkuin tuossa edessä lukeekin "Warning".

vika oli niinkin pieni kuin korjasin tämän:

GROUP_CONCAT(DISTINCT ID ORDER BY ID SEPARATOR ",")

tällä:

GROUP_CONCAT(DISTINCT ID ORDER BY ID SEPARATOR ',')

tällä hetkellä painin toisenlaisen ongelman kanssa.
Warning: Invalid argument supplied for foreach() in /home/stagerad/public_html/hallinto/kaplist.php on line 87
sieltä löytyy:

$songs = array();
foreach ($songlist as $song) {
  // Puretaan kategorialista taulukoksi, jossa category.ID on avaimena
  $song["categorylist"] = array_flip(explode(",", $song["categorylist"]));
  // Merkataan puuttuviin kategorioihin false ja löytyineisiin true
  foreach ($category as $id => $c) {
    $song["categorylist"][$id] = !isset($song["categorylist"][$id]);
  }
  $songs[$song["ID"]] = $song;
}
unset($songlist);

en oikein tunne foreachin toimintaa joten korjaus yritykseni ovat olleen aikamoista hakuammuntaa tähän asti. Miten pystyisin saamaan parempaa vikakoodia virheestä?

Et ole ilmeisesti hakenut muuttujiin $songlist ja $category niitä asioita, jotka mainitsin, että niihin pitäisi laittaa. Muuttujaan $songlist pitää siis laittaa tuon pitkän haun tulokset (mysql_fetch_associlla haettuna, kuten olet aiemminkin tehnyt) ja muuttujaan $category vastaavasti kategoriat (SELECT * FROM category).

Tämä ilmeisesti tarkoittaa että tavaraa tulee liikaakin?
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 71 bytes) in /home/stagerad/public_html/hallinto/kaplist.php on line 92

Sitä se tarkoittaa. Kokeilepa hakea valmiiksi pelkästään kategoriat ja korvata koodini ulompi foreach-silmukka silmukalla, joka hakee rivejä puskuroimatta niitä:

<?php
$songlist = mysql_unbuffered_query("...");
if (!$songlist) {
  die("Virhe kyselyssä! ".htmlspecialchars(mysql_error()));
}
#foreach ($songlist as $song) {
while ($song = mysql_fetch_assoc($songlist)) {
  // ...
}
mysql_free_result($songlist);
?>

Tällä tavalla muistia säästyy, kun PHP käsittelee vain yhtä haettua kappaletta kerrallaan. Toki määrä voi silti olla ylivoimainen muistissa pidettäväksi.

Kannattaa harkita kappalemäärän rajoittamista jo ihan verkkosivunkin käytettävyyden vuoksi. (Jos haluat hienostella, koodaa niille vaikka sivutussysteemi.) SQL-kyselyssä rajoittaminen tapahtuu LIMIT-sanalla.

Tuota tuota .. rupee lähtee mopo käsistä tämän koodini kanssa.. jotenkin tuntuu että nyt on jossain menty sekasin. Kokeilin tuota jälkimmäistä ohjettasi mutta en päässyt kyllä puuta pidemmälle. Pistän tähän nyt koko tekstin pätkän niin näet virheen ehkä helpommin.

ja valittaa että Invalid argument supplied for foreach() // rivillä on // foreach ($songlist as $song) {

<?
// Haetaan categorylist

$cat = mysql_query( " SELECT *
                      FROM categorylist
                      LIMIT 100 "
                   ) or die('Error: ' . mysql_error());

$category = array();

while ($x = mysql_fetch_assoc($cat)) {
  $category[] = $x;
}
mysql_free_result($cat);

// Haetaan songlist

$son = mysql_query( " SELECT
                       *,
                        (
                          SELECT
                            GROUP_CONCAT(DISTINCT ID ORDER BY ID SEPARATOR ',')
                          FROM
                            categorylist
                          WHERE
                            categorylist.songID = songlist.ID
                         )
                       AS 'categorylist'
                       FROM songlist
                       LIMIT 100"
                   ) or die('Error: ' . mysql_error());

$songlist = array();

while ($y = mysql_fetch_assoc($son)) {
  $songlist[] = $y;
}
mysql_free_result($son);

// Tulostellaan

echo "<h2>STAGERADIO KAPPALELISTA</h2>";
echo "<table>";

$artist = $album = null;
foreach ($songlist as $row) {
  if ($artist != $row["artist"]) {
    $artist = $row["artist"];
    $album = null;
    echo "<tr><td id'artist' colspan='4'>$artist\n</td></tr>";
  }
  if ($album != $row["album"]) {
    $album = $row["album"];
    echo "<tr><td></td><td id='album' colspan='3'>$album</td></tr>\n";
  }

// Oletetaan, että
// $songlist sisältää yo. kyselyn tulokset, ja
// $category sisältää kategoriahaun tulokset niin, että ID on avaimena.

$songs = array();
foreach ($songlist as $song) {
  // Puretaan kategorialista taulukoksi, jossa category.ID on avaimena
  $song["categorylist"] = array_flip(explode(",", $song["categorylist"]));
  // Merkataan puuttuviin kategorioihin false ja löytyineisiin true
  foreach ($category as $id => $c) {
    $song["categorylist"][$id] = !isset($song["categorylist"][$id]);
  }
  $songs[$song["ID"]] = $song;
}

unset($songlist);
}
?>

niin ja tosiaan .. kappaleita on tällä hetkellä yli neljä tuhatta .. joten on siinä tulostamista varsinkin kun songlist taulussa on jotain 20 eri kenttää :) Oli tarkoitus pistää rajoitus esim. niin että pitää ensin valita artisti jota lähetään tutkimaan / muokkaamaan. kunhan tuon nyt saa sijotettua jossain vaiheessa. :) Se on onneksi hieman yksinkertaisempaa joten sen varmasti saan ilman apujakin tehtyä. Rajoitin haun sataan näin toistaiseksi kunnes pistän formin kehiin.

No on kyllä aika monta asiaa hakusessa. Osin olen kyllä itsekin antanut epämääräisiä vinkkejä. Toisaalta suosittelen myös Ohjelmointiputkan PHP-oppaan tarkempaa lukemista.

Kannasta pitää alussa hakea kategoriat taulusta category, ei categorylist. Haun tulokset pitää laittaa taulukkoon niin, että avaimena on ID:

<?php
while ($x = mysql_fetch_assoc($cat)) {
  $category[$x["ID"]] = $x;
}
?>

Nyt siis kategoriat ovat taulukossa ID:n perusteella, jolloin sieltä löytyy helposti oikea kategoria ja käyttämäni foreach-silmukka toimii oikein. Tätä taulukointitapaa kannattaa soveltaa usein muutenkin.

Kappalehausta puuttuu nyt näköjään järjestys. Voit lisätä siihen sen samaisen ORDERin kuin alkuperäisessäkin koodissasi.

Lisäksi kyselyssäni on näköjään pieni virhe epäloogisen nimeämisen vuoksi: categorylist-taulusta pitäisi ID:n sijaan hakea tietenkin categoryID, eli suluissa olevan SELECT-kyselyn molemmat ID-kohdat pitää vaihtaa categoryID:ksi.

Antamani lisäkoodi kuuluu ennen tulostuskoodia eli heti tietokantahakujen jälkeen, ja sen jälkeen tiedot ovat $songs-muuttujassa. Saamasi virheilmoitus johtuu siitä, että olet laittanut koodin väärään paikkaan (tulostussilmukan sisään), jolloin se tuhoaa $songlist-muuttujan väärään aikaan (ensimmäisellä kierroksella) eikä kyseistä muuttujaa enää seuraavalla yrityksellä löydy.

Koodissani on sattunut pieni epäloogisuus: !isset pitäisi olla isset, eli negaatio (huutomerkki) pitäisi ottaa pois. Nythän checked-arvot menisivät juuri väärin.

Tulostuskoodista taas pitäisi tulla suunnilleen tällainen:

<?php
// Otetaan kategorioiden määrä ja ID:t talteen
$cat_count = count($category);
$category_id_arr = array_keys($category);

echo "<table>\n";

// Tulostetaan otsikkorivi: artisti, albumi, kappale, 60's, 70's, ...
echo "<thead><tr><th>artisti</th><th>albumi</th><th>kappale</th>";
foreach ($category as $c) {
  echo "<th>{$c["name"]}</th>";
}
echo "</tr></thead>\n";

// Tulostetaan kappaleet:
echo "<tbody>\n";
$artist = $album = null;
foreach ($songs as $row) {
  // Tarvittaessa uusi artisti
  if ($artist != $row["artist"]) {
    $artist = $row["artist"];
    $album = null;
    // HUOM! class eikä id, koska sama id saa olla sivulla vain kerran!
    // <td colspan='$cat_count'></td> vie tilaa kategoriasolujen verran.
    echo "<tr><td class='artist' colspan='3'>$artist</td><td colspan='$cat_count'></td></tr>";
  }
  // Tarvittaessa uusi levy
  if ($album != $row["album"]) {
    $album = $row["album"];
    echo "<tr><td></td><td class='album' colspan='2'>$album</td><td colspan='$cat_count'></td></tr>\n";
  }
  // Kappaleen tiedot
  echo "<tr><td></td><td></td><td class='song'>{$row["title"]}</td>";
  // Kappaleen kategorialaatikot
  foreach ($category_id_arr as $cat_id) {
    $checked = $row["categorylist"][$cat_id];
    if ($checked) $checked = "checked='checked'"; else $checked = "";
    echo "<td><input type='checkbox' name='check[{$row["ID"]}][$cat_id]' $checked></td>";
  }
  echo "</tr>\n";
}
echo "</tbody>\n";
echo "</table>\n";
?>

Näillä muutoksilla koodi toimii, eli tee jokainen ihan rauhassa ja oikeaan paikkaan. ;)

Noniin .. sain koodin toimimaan hienosti!

Suur kiitos ja kumarrus.

Vielä jos uskallan kysästä että miten pitäisi lähteä oikea oppisesti sitten lisäämään tekemäni muutokset takaisin tietokantaan? :)

Jos tulostan tuon listan suoraan formiksi ja sitten lähetän sen sivulle joka tallentaa sen tietokantaan niin onko se millään tapaa fiksu / oikea lähestymistapa?

Haa.. Mielenkiintoinen virhe ilmaantui.. FireFox ei suostu tulostamaan rivejä ollenkaan .. mikähän tässä sitten voisi olla syynä?