I have a join of two tables. Actually the code and the fetching worked fine before I inserted : and But now it only fetches the first row even though there are two: I used fetchAll and a foreach loop so I just don't see why it is only fetching the first row. Maybe someone can help, thank you! Answer

fetchAll only fetches one row from two joined tables

I have a join of two tables. Actually the code and the fetching worked fine before I inserted :

$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

JavaScript
​x
 
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');​

and

WHERE id = ? LIMIT 0,1";

JavaScript
 
WHERE id = ? LIMIT 0,1";​

But now it only fetches the first row even though there are two:

<head>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<?php


  $id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

  try {
  require "config.php";
  require "common.php";

  $connection = DbConnect();

  $sql = "SELECT * FROM product
LEFT JOIN brand
ON product.id = brand.product_id
WHERE id = ? LIMIT 0,1";

  $statement = $connection->prepare($sql);
  $statement->bindParam(1, $id);
  $statement->execute();

  $result = $statement->fetchAll(PDO::FETCH_ASSOC);

} catch(PDOException $error) {
  echo $sql . "<br>" . $error->getMessage();
}
?>
<?php require "public/templates/header.php"; ?>

<h2>Update </h2>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"] . "?id={$id}");?>" method="post">
<table>
  <thead>
    <tr>
      <th>id</th>
      <th>product name</th>
      <th>date</th>
      <th>brand name</th>
      <th>brand location</th>
    </tr>
  </thead>
    <tbody>

    <?php foreach ($result as $row) : ?>
      <tr>
        <td><?php echo escape($row["id"]); ?></td>
        <td><?php echo escape($row["product_name"]); ?></td>
        <td><?php echo escape($row["date"]); ?></td>
        <td><?php echo escape($row["brand_name"]); ?></td>
        <td><?php echo escape($row["brand_location"]); ?></td>
        <td><a href="entry_update.php?id=<?php echo escape($row["id"]); ?>">Select</a></td>
      </tr>
    <?php endforeach; ?>
    </tbody>
</table>
</form>
<a href="index.php">Back to home</a>

<?php require "public/templates/footer.php"; ?>

JavaScript
 
<head>  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script></head><?php​​  $id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');​  try {  require "config.php";  require "common.php";​  $connection = DbConnect();​  $sql = "SELECT * FROM productLEFT JOIN brandON product.id = brand.product_idWHERE id = ? LIMIT 0,1";​  $statement = $connection->prepare($sql);  $statement->bindParam(1, $id);  $statement->execute();​  $result = $statement->fetchAll(PDO::FETCH_ASSOC);​} catch(PDOException $error) {  echo $sql . "<br>" . $error->getMessage();}?><?php require "public/templates/header.php"; ?>​<h2>Update </h2><form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"] . "?id={$id}");?>" method="post"><table>  <thead>    <tr>      <th>id</th>      <th>product name</th>      <th>date</th>      <th>brand name</th>      <th>brand location</th>    </tr>  </thead>    <tbody>​    <?php foreach ($result as $row) : ?>      <tr>        <td><?php echo escape($row["id"]); ?></td>        <td><?php echo escape($row["product_name"]); ?></td>        <td><?php echo escape($row["date"]); ?></td>        <td><?php echo escape($row["brand_name"]); ?></td>        <td><?php echo escape($row["brand_location"]); ?></td>        <td><a href="entry_update.php?id=<?php echo escape($row["id"]); ?>">Select</a></td>      </tr>    <?php endforeach; ?>    </tbody></table></form><a href="index.php">Back to home</a>​<?php require "public/templates/footer.php"; ?>​

I used fetchAll and a foreach loop so I just don’t see why it is only fetching the first row. Maybe someone can help, thank you!

Answer

Your error here:

 $sql = "SELECT * FROM product LEFT JOIN brand ON product.id = brand.product_id WHERE id = ? LIMIT 0,1";

JavaScript
 
 $sql = "SELECT * FROM product LEFT JOIN brand ON product.id = brand.product_id WHERE id = ? LIMIT 0,1";​

You using limit context, which it return back one row. Try you change limit 0,1 to limit 10.

Advertisement

Answer