Keskustelu: Nettisivujen teko: Tietokantakysely viimeisemmästä kirjautumisesta

Osaisiko joku auttaa minkälaisella kyselyllä saisi käyttäjänimet ja avatar-kuvat listattua viimeisen kirjautumisen mukaan ja post-taulusta status sarake pitää olla aktiivinen jolloin käyttäjät näkyvät?

Tässä vähän havainnollistan tietokantaa.

CREATE TABLE useraccount (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username TEXT NOT NULL,
    avatar text NOT NULL,
    last_visit datetime NOT NULL
) ENGINE=InnoDB;

CREATE TABLE post (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    userId INT NOT NULL,
    status TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    FOREIGN KEY (userId) REFERENCES useraccount (id)
) ENGINE=InnoDB;


INSERT INTO `useraccount` (`id`, `username`, `avatar`, `last_visit`) VALUES
(1, 'Antti', 'avatar9.jpg', '2019-02-03 11:34:15'),
(2, 'Abc', 'avatar5.jpg', '2019-02-03 11:26:09');

INSERT INTO `post` (`id`, `userId`, `status`, `title`, `content`) VALUES
(1, '1', 'active', 'mielipiteitä', 'höpöhöpö, höpönlöpön'),
(2, '2', 'inactive', 'mielipiteitä', 'höpöhöpö, höpönlöpön');

Onnistuuko tällä koodilla kyselyn suorittaa?

<?php
// muodostetaan yhteys tietokantaan
try {
    $yhteys = new PDO("mysql:host=localhost;dbname=testit", "antti", "abc");
} catch (PDOException $e) {
    die("VIRHE: " . $e->getMessage());
}
// virheenkäsittely: virheet aiheuttavat poikkeuksen
$yhteys->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// merkistö: käytetään latin1-merkistöä; toinen yleinen vaihtoehto on utf8.
$yhteys->exec("SET NAMES latin1");

// valmistetaan kysely
$kysely = $yhteys->prepare("SELECT * FROM tuotteet");
// suoritetaan kysely
$kysely->execute();

// näytetään kyselyn tulokset taulukossa
echo "<table>";
// käsitellään tulostaulun rivit yksi kerrallaan
while ($rivi = $kysely->fetch()) {
    // $rivi["nimi"] sisältää nimen
    // $rivi["hinta"] sisältää hinnan
    echo "<tr>";
    echo "<td>" . htmlspecialchars($rivi["nimi"]) . "</td>";
    echo "<td>" . htmlspecialchars($rivi["hinta"]) . "</td>";
    echo "</tr>";
}
echo "</table>";
?>

Vähän voisi kokeilla itsekin debugata näitä. Vai mitäpä luulet, että palauttaako "tuotteet" -taulusta hakeminen mitään "useraccount" -taulusta? Eli ensimmäinen vihje, vaihda kyselyksi "SELECT * FROM useraccout" ja katso mitä se palauttaa ja käytä vähän aikaasi ratkaisujen miettimiseen. Toinen vihje, älä copypastea valmista koodia ikinä, jollei sinulla ole aavistustakaan mitä se tekee.

Lisäksi: Mitä "post" -taulun active/inactive tarkoittaa? Millä perusteella jokin viesti on aktiivinen/inaktiivinen?

Tarkoitatko ehkä jotain tällaista kyselyä:

SELECT DISTINCT username, avatar
FROM useraccount
JOIN post ON post.userId = useraccount.id AND post.status = 'active'
WHERE last_visit > NOW() - INTERVAL 7 DAY
ORDER BY last_visit DESC

Tämän voi laittaa oppaasta kopioimaasi PHP-koodiin SELECT-kyselyksi. Silmukassa pitää tietenkin silloin nimen ja hinnan sijasta tulostaa käyttäjänimi ja avatar. Lisäksi koodiin pitää laittaa oikeat tiedot tietokantayhteyttä varten (käyttäjänimi, salasana, tietokannan nimi). Jos näissä kohdissa on jotain epäselvää, kannattaa varmaan opetella oppaista PHP-ohjelmoinnin ja SQL:n perusteet.

Metabolix kirjoitti:

Tarkoitatko ehkä jotain tällaista kyselyä:

SELECT DISTINCT username, avatar
FROM useraccount
JOIN post ON post.userId = useraccount.id AND post.status = 'active'
WHERE last_visit > NOW() - INTERVAL 7 DAY
ORDER BY last_visit DESC

Kiitos Metabolix avustasi. Antamasi kysely toimi, mutta en saanut useraccount taulusta id. Yritin lisätä sitä: "SELECT DISTINCT id, username, avatar
FROM useraccount......". Näin kun tein niin aukeaa vain valkoinen sivu.

Onko myös jotenkin mahdollista saada samaan kyselyyn post-taulusta esim. title liitettyä username ja avatar lisäksi?

(Post-taulun nimi oli vain esimerkki kun en keksinyt parempaakaan.)

Kyselyssä id voi tarkoittaa useraccountin tai postin id:tä, joten kyselyyn pitää kirjoittaa erikseen useraccount.id, tai sinänsä voisit hakea myös yksinkertaisemmin merkinnällä useraccount.* kaikki sarakkeet.

Kiitos vielä avustasi. Eilen jo sain id:n toimimaan.