在 PHP 中使用来自 2 个 SQL 表的数据-解网

问：

我有一个脚本，可以访问数据库并遍历商店中的各种商品。我正在尝试访问另一个包含应用程序中的用户及其积分余额的表。当我使用我的脚本时，我得到的结果是每个项目重复三次。所以我的 shopitems 表中有 6 个项目，但我得到了 18 个结果，即项目 1 3 次，项目 2 3 次，等等。我想知道如何解决这个问题。我想要从列表表中获取的唯一一列是他们的积分。

<?php 




include('../config.php');
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT * FROM shopitems CROSS JOIN listings");
$stmt->execute();
while($row = $stmt->fetch()) {
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

$balance = $row['points'];
$cost = $row['itemcost'];

if ($balance < $cost) {
  $status = 'Not enough points';  
  } 
  else{
  $status = 'Purchase';
  }
echo'

      
  
        <div class="col-lg mb-md-0 mb-4">
          <div class="card border rounded shadow-none">
            <div class="card-body">
              <div class="my-3 pt-2 text-center">
                <img src="'.$row['itemimage'].'" alt="Basic Image" height="140">
              </div>
              <h3 class="card-title text-center text-capitalize mb-1">'.$row['itemname'].'</h3>

              <div class="text-center">
                <div class="d-flex justify-content-center">
                  <sup class="h6 pricing-currency mt-3 mb-0 me-1 text-primary">P</sup>
                  <h1 class="display-4 mb-0 text-primary">'.$row['itemcost'].'</h1>
                </div>
              </div>
<br/>

<a href="./protected/webhook-alerts/shop-purchase.php?desc='.$row['itemdesc'].'" method="post" type="submit" class="btn btn-primary d-grid w-100 post">'.$status.'</a>
            </div>
          </div>
    

     


  ';

}
}catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
    ?>

php sql pdo

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmtListings = $conn->prepare("SELECT points FROM listings WHERE username = :username");
$stmtListings->execute(["username" => $user->username]);
$balanceRow = $stmtListings->fetch(PDO::FETCH_ASSOC);
$balance = $balanceRow['points'];

$stmtItems = $conn->prepare("SELECT * FROM shopitems");
$stmtItems->execute();

while($row = $stmtItems->fetch(PDO::FETCH_ASSOC)) {

  $cost = $row['itemcost'];

  if ($balance < $cost) {
    $status = 'Not enough points';  
  } 
  else{
    $status = 'Purchase';
  }
echo'
        <div class="col-lg mb-md-0 mb-4">
          <div class="card border rounded shadow-none">
            <div class="card-body">
              <div class="my-3 pt-2 text-center">
                <img src="'.$row['itemimage'].'" alt="Basic Image" height="140">
              </div>
              <h3 class="card-title text-center text-capitalize mb-1">'.$row['itemname'].'</h3>

              <div class="text-center">
                <div class="d-flex justify-content-center">
                  <sup class="h6 pricing-currency mt-3 mb-0 me-1 text-primary">P</sup>
                  <h1 class="display-4 mb-0 text-primary">'.$row['itemcost'].'</h1>
                </div>
              </div>
              <br/>
              <a href="./protected/webhook-alerts/shop-purchase.php?desc='.$row['itemdesc'].'" method="post" type="submit" class="btn btn-primary d-grid w-100 post">'.$status.'</a>
            </div>
          </div>
  ';

上一个：在控制器空结果中查询雄辩，为什么？

下一个：获取没有include（）的PHP变量 - Chart.js

在 PHP 中使用来自 2 个 SQL 表的数据

Using data from 2 SQL tables in PHP

评论