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

问题描述：

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.

我正在尝试获取一个ID和一个名为info的行，以便根据用户名显示。 p>

我有一个用户表： p>

  TABLE用户（
id INTEGER PRIMARY KEY AUTOINCREMENT 
 NOT NULL，
username TEXT NOT NULL 
 UNIQUE，  
password TEXT NOT NULL 
  code>  pre> 
 
 以及将用户ID作为FK的信息表： p> 
 
 
 表的相关信息 （
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 
  code>  
 
 我设法从会话中获取用户名和用户ID： p> 
 
 
  //获取用户名
 $ name = $ _SESSION [  'username']; 
 $ stmt = $ db-＆gt; prepare（“SELECT id FROM users WHERE username =：name”）; 
 $ stmt-＆gt; execute（[“name”=＆gt; $ name]）  ; 
 $ r = $ stmt-＆gt; fetch（）; 
 
 //  get userid 
 $ stmnt2 = $ db-＆gt; prepare（“SELECT info FROM infos WHERE userid =：r”）; 
 $ stmnt2-＆gt; execute（[“r”=＆gt;  $ r ['id']]）; 
  code>  pre> 
 
 我很难根据用户ID获取所有信息ID和信息。 我已设法获取信息，但它错过了第一个条目，我无法获取infoid  p> 
 
 
  //获取基于userid的信息
 if（$ info =  $ stmnt2-＆gt; fetch（））{
 echo'＆lt; p＆gt;您的信息：＆lt; / p＆gt;';  
 while（$ info = $ stmnt2-＆gt; fetch（））{
 echo“$ info [info]＆lt; / br＆gt;”; 
} 
}其他{
 echo“＆lt; p＆gt;否 信息＆lt; / p＆gt;“; 
  code>  pre> 
 
 有人可以告诉我如何获取infoid和信息显示谢谢。 p> 
  div>

答

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>";
}

// 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.

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

相关推荐