从会话用户名派生的外键ID中获取ID和行

Question

I am trying to get an ID and a row called info to be shown based of the username.

I have a users table:

TABLE users (
id       INTEGER PRIMARY KEY AUTOINCREMENT
                 NOT NULL,
username TEXT    NOT NULL
                 UNIQUE,
password TEXT    NOT NULL

and an infos table that has the userid as an FK:

TABLE infos (
infoid  INTEGER NOT NULL
                  PRIMARY KEY AUTOINCREMENT,
info    TEXT    NOT NULL,
productid INTEGER REFERENCES product (productid) 
                  NOT NULL,
userid    INTEGER REFERENCES users (id) 
                  NOT NULL

I have managed to get the username from the session and the userid:

// get username
$name = $_SESSION['username'];
$stmt = $db->prepare("SELECT id FROM users WHERE username = :name");
$stmt->execute(["name" => $name]);
$r = $stmt->fetch(); 

// get userid
$stmnt2 = $db->prepare("SELECT info FROM infos WHERE userid = :r");
$stmnt2->execute(["r" => $r['id']]);

I am struggling to get all the info id and info based on the userid. I have managed to get the info but it misses out the first entry and I can't get the infoid

    // get info based on userid
    if ($info = $stmnt2->fetch()) {
        echo '<p>Your Info:</p>';   
            while ($info = $stmnt2->fetch()) {
            echo "$info[info] </br>";
            }
   } else {
        echo "<p>No Info</p>";

Can someone tell me how I can get infoid and info to be shown thanks.

Answer 1

Replace

// get userid
$stmnt2 = $db->prepare("SELECT info FROM infos WHERE userid = :r");
$stmnt2->execute(["r" => $r['id']]);

with

// get userid
$stmnt2 = $db->prepare("SELECT info, infoid AS id FROM infos WHERE userid = :r");
$stmnt2->execute(["r" => $r['id']]);

and replace

// get info based on userid
if ($info = $stmnt2->fetch()) {
    echo '<p>Your Info:</p>';   
        while ($info = $stmnt2->fetch()) {
        echo "$info[info] </br>";
        }
} else {
    echo "<p>No Info</p>";
}

with

// get info based on userid

if ($info = $stmnt2->fetch()) {
    echo '<p>Your Info:</p>';   
    do {
        echo "$info[info] </br>"; //The info id is contained in the $info['id']
    } while ($info = $stmnt2->fetch());
} else {
    echo "<p>No Info</p>";
}

or

// get info based on userid

if ($stmnt2->num_rows()) {
    echo '<p>Your Info:</p>';   
    while ($info = $stmnt2->fetch()){
        echo "$info[info] </br>"; //The info id is contained in the $info['id']
    }
} else {
    echo "<p>No Info</p>";
}

I would suggest you the second way. Do not hesitate to ask me for any explanation.