Using data from 2 SQL tables in PHP

Question

I have a script that accesses a database and loops through the various items within the shop. I am attempting to access another table that contains users in the application and their points balance. When I am using my script, I am getting results duplicated three times per item. So I have 6 items within my shopitems table, but I am getting back 18 results, i.e., item 1 3 times, item 2 3 times, etc. I am wondering how I can fix this. The only column I want from the listings table is their points.

<?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();
}
    ?>

Answer 1

The two tables appear to be basically unrelated, so a JOIN of any sort doesn't appear to make sense. It sounds like you simply you need two queries. Then you can use PHP to put the different pieces of data in the right place in the output.

Something like this:

$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>
  ';