gary falkland gary falkland - 1 month ago 4
PHP Question

Three tables joined but need to links ids

I have joined three tables for a query. The problem is that is displays each class Name and then dog, but does not link the dog name to specific shows. It displays the same list of dognames in every show. I have tried to do this using a show_id in my dogs table, and linking it to the show_id in shows table.

The three tables are: shows, results, dogs.

Can anyone help me?

<?php
if(isset($_GET['s_id'])) {
$the_show_id = $_GET['s_id'];

$view_query = "UPDATE shows SET show_view_count = show_view_count + 1 WHERE show_id = $the_show_id ";
$send_query = mysqli_query($connection, $view_query);

if(!$send_query) {

die("query failed" );
}


if(isset($_SESSION['user_role']) && $_SESSION['user_role'] == 'admin' ) {

$query = "SELECT * FROM shows WHERE show_id = $the_show_id ";

} else {

$query = "SELECT * FROM shows WHERE show_id = $the_show_id AND show_status = 'published' ";
}

$select_all_shows_query = mysqli_query($connection,$query);

if(mysqli_num_rows($select_all_shows_query) < 1) {

echo "<h1 class='text-center'>No shows available</h1>";

} else {

while($row = mysqli_fetch_assoc($select_all_shows_query)) {
$show_name = $row['show_title'];
$show_author = $row['show_author'];
$show_date = $row['show_date'];
$show_content = $row['show_content'];

?>

<h1 class="page-header">
Shows
</h1>
<!-- First Blog Post -->
<h2>
<a href="#"><?php echo $show_name ?></a>
</h2>
<p class="lead">
by <a href="index.php"><?php echo $show_author ?></a>
</p>
<h4>Show Date: <span class="glyphicon glyphicon-time"> </span>
<? php echo $show_date ?></h4>
<hr>
<div class="row">
<div class="col-xs-6 col-sm-3"><h4>Class Name</h4></div>
<div class="col-xs-6 col-sm-2"><h4>Placement</h4></div>
<div class="col-xs-6 col-sm-7"><h4>Dog Name</h4></div>
</div><hr>
<?php

$query = "SELECT result.class_name, result.placement, dogs.dog_name
FROM result
INNER JOIN dogs on result.resultID = dogs.resultIDD
INNER JOIN shows on dogs.show_id = shows.show_id
WHERE dog_name NOT LIKE 'absent' GROUP BY shows.show_id";

$result = mysqli_query($connection, $query) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($connection), E_USER_ERROR);

if($result) {
while($row = mysqli_fetch_assoc($result)) {
$dog_name = $row['dog_name'];
$placement = $row['placement'];
$class_name = $row['class_name'];
?>

<div class="row">
<div class="col-xs-6 col-sm-3"><p><?php echo $class_name ?></p></div>
<div class="col-xs-6 col-sm-2"><p><?php echo $placement ?></p></div>
<div class="col-xs-6 col-sm-7"><p><?php echo $dog_name ?></p></div>
</div>
<?php
}
}
}
?>

Answer

Well, assuming all other things are working correctly, it is possible that you need only add the dogs.show_id (or the shows.show_id either way) to the select. Might you try the query below and then if that doesn't work report your table structure (you can use "show create table TBL_NAME" to do that for each of the three tables.

<?php
    $query = "
        SELECT
            dogs.show_id,
            result.class_name, result.placement,
            dogs.dog_name
        FROM
            result INNER JOIN
            dogs on result.resultID = dogs.resultIDD INNER JOIN
            shows on dogs.show_id = shows.show_id
        WHERE
            dog_name NOT LIKE 'absent'
    "; 
?>

UPDATE: okay, so now that we know you are already in a loop that tells us the show_id, you just need to update the query to only pull dogs for that show (assuming show_id is actually in the dogs table...which isn't really where it belongs unless you have dog+show_id as a unique, composite key).

Take a look here where i add $show_id to the list of declarations from the initial show query, and then it is used in the query to limit dogs from that query. Also, inner join isn't best here...imho.

...
while ($row = mysqli_fetch_assoc($select_all_shows_query)) {

  // WE ALREADY KNOW THE SHOW WE'RE IN
  $show_id = $row['show_id']; 

  $show_name = $row['show_title'];
  $show_author = $row['show_author'];
  $show_date = $row['show_date'];
  $show_content = $row['show_content'];
    ?>
      <h1 class="page-header">Shows</h1>

        <!-- First Blog Post -->
        <h2><a href="#"><?php echo $show_name ?></a></h2>
        <p class="lead"> by <a href="index.php"><?php echo $show_author ?></a></p>
        <h4>Show Date: <span class="glyphicon glyphicon-time"> </span> <?php echo $show_date ?></h4>
        <hr>
        <div class="row">
          <div class="col-xs-6 col-sm-3"><h4>Class Name</h4></div>
          <div class="col-xs-6 col-sm-2"><h4>Placement</h4></div>
          <div class="col-xs-6 col-sm-7"><h4>Dog Name</h4></div>
        </div>
        <hr>
      <?php

        // SO UPDATE THE QUERY TO ONLY PULL THAT SHOW'S DOGS
        $query = "SELECT result.class_name, result.placement, dogs.dog_name
                  FROM result
                  LEFT JOIN dogs on result.resultID = dogs.resultIDD
                  WHERE dogs.show_idd = $show_id AND dog_name NOT LIKE 'absent'";

         $result = mysqli_query($connection, $query) or trigger_error
           ("Query Failed! SQL: $query - Error: ". mysqli_error
           ($connection), E_USER_ERROR);

         if ($result) {
           while ($row = mysqli_fetch_assoc($result)) {
             $dog_name = $row['dog_name'];
             $placement = $row['placement'];
             $class_name = $row['class_name'];
             ?>

               <div class="row">
                 <div class="col-xs-6 col-sm-3"><p><?php echo $class_name ?></p></div>
                 <div class="col-xs-6 col-sm-2"><p><?php echo $placement ?></p></div>
                 <div class="col-xs-6 col-sm-7"><p><?php echo $dog_name ?></p></div>
               </div>
Comments