将SQL表ID转换为PHP中的另一个字段

debugcn 发表于 Dev

蒲田

因此，我正在努力使以下工作有效；

//SEARCH USERPETS TABLE FOR ANYTHING USER OWNS
$query = "SELECT * FROM userpets WHERE owner = '$username'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);

$userspets = $row['petid'];

//SEARCH PETS TABLE FOR LIST OF PET NAMES AND DETAILS
$query = "SELECT * FROM pets";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);

$petid = $row['id']
$petname = $row['petname'];
$petimg = $row['petimg']

//TURN PET ID INTO PET NAME AND IMAGE

echo "Pets: ".$userspets;

本质上我想做的就是这个。“用户”表包含所有“拥有的”宠物，并显示玩家用户名。我要抓住该用户拥有的所有宠物，并将其与“宠物”表进行比较。然后，我想从桌子上取下宠物的名字和图像，然后将其“回显”到页面上。

获取所有ID很好，我只是不知道如何使它将ID转换为名称。

表结构

丁蓬

您可以使用JOIN的MYSQL或者Foreach的PHP

这是使用PHP Foreach的示例

$query = "SELECT * FROM userpets WHERE owner = '".$username."'";
$result = mysqli_query($conn, $query);

$petid = array(); // store all petid of this user

$rows = mysqli_fetch_all($result,MYSQLI_ASSOC);

foreach($rows as $row) {
    $petid[] = $row['petid'];
}

$query = "SELECT * FROM pets WHERE id IN (".implode(",",$petid).")";

// implode will convert an array to string with delimete
// example array(0=>35, 1=>36, 2=>48) will be convert to "35,36,48"
// and above query should be : SELECT * FROM pets WHERE id IN (35,36,48)

$result = mysqli_query($conn, $query);
$pets = mysqli_fetch_assoc($result);

// dump it 
echo "<pre>";
var_dump($pets);
echo "</pre>";
die;

使用MySQL Join

<?php 

$query = "SELECT pet.id, pet.petname, pet.petimg, up.owner FROM pets as pet LEFT JOIN userpets as up ON pet.id = up.pet_id WHERE up.owner = '".$username."'";

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。