Displaying Table data using IDs

Question

I have a script that shows the information from 2 tables using inner join. I need to add a 3rd table to show if that row has a note attached to it.

This is my current query:

 $from_missed = 'SELECT  A.*,
                         l.City,
                         l.Owner,
                         l.Outlet_ID,
                         l.Region,
                         l.district

  FROM vzw_missopportunity A
  INNER JOIN locations l ON A.Agent_ID = l.Outlet_ID
  WHERE A.Agent_ID = "'.$_SESSION['agent'].'"
  AND l.district = "'.$_SESSION['district'].'"

  ';

The notes are stored in the vz_notes table with an ID, opp_ID, content column. The ID is for the table vz_notes and then opp_id is the id of the row from the oppourtunity table. Content is the information the user inputs, note content.

Im haveing an issue getting the note data to show in the table.

   <table id="vzw" class="cell-border" width="100%">
       <thead>
<tr><th colspan="15">Missed Opportunities for outlet ID: <?php echo $_POST['agent']; ?></th></tr>
         <tr>
           <th> Agent ID    </th>
           <th> Agent Name  </th>
           <th> Tran Year   </th>
           <th> Tran Period </th>
           <th> Original Mobile ID  </th>
           <th> Device ID   </th>
           <th> New Plan    </th>
           <th> Customer Name   </th>
           <th> Device Change Date  </th>
           <th> New_Plan Access Charge  </th>
           <th> Model   </th>
           <th> Agent SSO ID    </th>
           <th> Customer Type   </th>
           <th> Previous Plan   </th>
           <th> Previous Access Charge  </th>
           <th> Notes   </th>

         </tr>
       </thead>

   <?php while ($stmt2->fetch()) {


     ?>

     <tr>

 <?php

       echo "<td>" . $Agent_ID. "<br><a href='#notes' data-toggle='modal' data-book-id='".$id."'>Add Note</a></td>" ;
       echo "<td>" . $Agent_Name. "</td>";
       echo "<td>" . $Tran_Year. "</td>";
       echo "<td>" . $Tran_Period. "</td>";
       echo "<td>" . $Original_Mobile_ID. "</td>";
       echo "<td>" . $Device_ID. "</td>";
       echo "<td>" . $New_Plan. "</td>";
       echo "<td>" . $Customer_Name. "</td>";
       echo "<td>" . $Device_Change_Date. "</td>";
       echo "<td>" . $New_Plan_Access_Charge. "</td>";
       echo "<td>" . $Model. "</td>";
       echo "<td>" . $Agent_SSO_ID. "</td>";
       echo "<td>" . $Customer_Type. "</td>";
       echo "<td>" . $Previous_Plan. "</td>";
       echo "<td>" . $Previous_Access_Charge. "</td>";
       echo "<td>" . $notecontent. "</td>";

Updated Query: Not working

  'SELECT  A.*,
                         l.City,
                         l.Owner,
                         l.Outlet_ID,
                         l.Region,
                         l.district,
                         n.ID,
                         n.note

  FROM vzw_missopportunity A
  INNER JOIN locations l ON A.Agent_ID = l.Outlet_ID
  LEFT JOIN notes n on n.opp_id = A.ID
  WHERE A.Agent_ID = "'.$_SESSION['agent'].'"
  AND l.district = "'.$_SESSION['district'].'"

  ';

Im kinda lost in this code at the moment. How can I get the data to show for each record and combine if ID is the same. Some of the content may not have a note but still need it to show. Would this be an outer join?

This is my full script

<?php
session_start();
if(!isset($_SESSION['user_id']) || !isset($_SESSION['logged_in'])){
    //User not logged in. Redirect them back to the login.php page.
    header('Location: /index.php');
    exit;
}
?>


try{
    $conn = new PDO('mysql:host=; dbname=', '','');

}catch (PDOException $e){
    echo "Cannot connect to database";
    exit;
}

if (isset($_GET["agent"])) {
    $_SESSION["agent"] = $_GET["agent"];
}

$_SESSION['agent'] = $_GET['agent'];

$region_owner = $_SESSION['district'];

 $from_missed = 'SELECT  A.*,
                         l.City,
                         l.Owner,
                         l.Outlet_ID,
                         l.Region,
                         l.district,
                         n.ID,
                         n.note

  FROM vzw_missopportunity A
  INNER JOIN locations l ON A.Agent_ID = l.Outlet_ID
  LEFT JOIN notes n on n.opp_id = A.ID
  WHERE A.Agent_ID = "'.$_SESSION['agent'].'"
  AND l.district = "'.$_SESSION['district'].'"

  ';


   $stmt2 = $conn->prepare($from_missed);

   $stmt2->bindColumn(1,  $Agent_ID);
   $stmt2->bindColumn(2,  $Agent_Name);
   $stmt2->bindColumn(3,  $Tran_Year);
   $stmt2->bindColumn(4,  $Tran_Period);
   $stmt2->bindColumn(5,  $Original_Mobile_ID);
   $stmt2->bindColumn(6,  $Mobile_ID);
   $stmt2->bindColumn(7,  $Device_ID);
   $stmt2->bindColumn(8,  $New_Plan);
   $stmt2->bindColumn(9,  $Customer_Name);
   $stmt2->bindColumn(10,  $Device_Change_Date);
   $stmt2->bindColumn(11,  $New_Plan_Access_Charge);
   $stmt2->bindColumn(12,  $Model);
   $stmt2->bindColumn(13,  $Agent_SSO_ID);
   $stmt2->bindColumn(14,  $Customer_Type);
   $stmt2->bindColumn(15,  $Previous_Plan);
   $stmt2->bindColumn(16,  $Previous_Access_Charge);
   $stmt2->bindColumn(18,  $notes);
   $stmt2->bindColumn(17,  $id);
    $stmt2->bindColumn(19,  $Region);
    $stmt2->bindColumn(20,  $District);
    $stmt2->bindColumn(21,  $Outlet_ID);
    $stmt2->bindColumn(22,  $Owner);
    $stmt2->bindColumn(23,  $City);
    $stmt2->bindColumn(24,  $noteid2);
    $stmt2->bindColumn(25,  $rowNote);



   $stmt2->execute();
   $numRows2 = $stmt2->rowCount();
   echo "<br>Rows: " . $numRows2;

   if (isset($numRows2)) {

     ?>


<body>

<br /><br />
<div class="vzwdiv">

  <form action="index.php" method="get">
    <div class="form-row align-items-center">
      <div class="col-auto my-1">
        <input type="text" name="agent" placeholder="Your Outlet ID">
      </div>

      <div class="col-auto my-1">
      <button type="submit" class="btn btn-primary">Submit</button>
      <?php if($_SESSION['user_id'] == 38){ ?>
        <button type="submit" class="btn btn-primary">Show All</button>
    <?php   } ?>

      </div>
    </div>
  </form>


  <?php


  /**if(!isset($_POST['agent'])){

  echo "Input your Agent ID above. This is the 6 digit number for your location";
  exit;
  }
**/
   ?>


  <br />

     <table id="vzw" class="cell-border" width="100%">
       <thead>
<tr><th colspan="15">Missed Opportunities for outlet ID: <?php echo $_POST['agent']; ?></th></tr>
         <tr>
           <th> Agent ID    </th>
           <th> Agent Name  </th>
           <th> Tran Year   </th>
           <th> Tran Period </th>
           <th> Original Mobile ID  </th>
           <th> Device ID   </th>
           <th> New Plan    </th>
           <th> Customer Name   </th>
           <th> Device Change Date  </th>
           <th> New_Plan Access Charge  </th>
           <th> Model   </th>
           <th> Agent SSO ID    </th>
           <th> Customer Type   </th>
           <th> Previous Plan   </th>
           <th> Previous Access Charge  </th>
           <th> Notes   </th>

         </tr>
       </thead>

   <?php while ($stmt2->fetch()) {


     ?>

     <tr>

 <?php





       echo "<td>" . $Agent_ID. "<br><a href='#notes' data-toggle='modal' data-book-id='".$id."'>Add Note</a></td>" ;
       echo "<td>" . $Agent_Name. "</td>";
       echo "<td>" . $Tran_Year. "</td>";
       echo "<td>" . $Tran_Period. "</td>";
       echo "<td>" . $Original_Mobile_ID. "</td>";
       echo "<td>" . $Device_ID. "</td>";
       echo "<td>" . $New_Plan. "</td>";
       echo "<td>" . $Customer_Name. "</td>";
       echo "<td>" . $Device_Change_Date. "</td>";
       echo "<td>" . $New_Plan_Access_Charge. "</td>";
       echo "<td>" . $Model. "</td>";
       echo "<td>" . $Agent_SSO_ID. "</td>";
       echo "<td>" . $Customer_Type. "</td>";
       echo "<td>" . $Previous_Plan. "</td>";
       echo "<td>" . $Previous_Access_Charge. "</td>";
       echo "<td>" . $rowNote. "</td>";







 ?>


     </tr>

   <?php } ?>
  <?php }




?>
</table>
</div>






<!--- This is for the modal box for notes section ---->


<div class="modal" id="notes">
  <div class="modal-dialog">
    <div class="modal-content">
      <form action="#" method="post">
      <div class="modal-body">
        <div class="modal-header">
          <h4 class="modal-title">Add a Note</h4>
          <button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">&times;</span><span class="sr-only">Close</span></button>
        </div>
        <p>Note will show when page is refreshed</p>
          <label for="fname">Note: </label>
          <input type="hidden" name="rowID" value=""/>
          <input class="form-control" type="text" name="notes" value=""/>
          <div class="modal-footer">
            <button type="submit" value="Submit" class="btn btn-primary">Submit</button>
          </div>
        </form>
      </div>
    </form>
    </div>
  </div>
</div>


<script type="text/javascript">
$('#notes').on('show.bs.modal', function(e) {
    var row = $(e.relatedTarget).data('book-id');
    $(e.currentTarget).find('input[name="rowID"]').val(row);
});
</script>



<?php

define('MYSQL_USER', '');
define('MYSQL_PASSWORD', '');
define('MYSQL_HOST', '');
define('MYSQL_DATABASE', '');

$pdoOptions = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
);

$pdo = new PDO("mysql:host=" . MYSQL_HOST . ";dbname=" . MYSQL_DATABASE, MYSQL_USER,MYSQL_PASSWORD,$pdoOptions);

$data = [
   'notes' => $_POST['notes'],
   'id' => $_POST['rowID'],
];
$sql = "INSERT INTO notes (note_id, note) Values(:id, :notes)";
$stmt= $pdo->prepare($sql);
$stmt->execute($data);

 ?>










</body>

Answer 1

How can I get the data to show for each record. Some of the content may not have a note but still need it to show.

If I followed you correctly, you do want a left join on the notes table, like so:

select  
    a.*,
    l.city,
    l.owner,
    l.outlet_id,
    l.region,
    l.district,
    n.*
from vzw_missopportunity a
inner join locations l on a.agent_id = l.outlet_id
left join vz_notes n on n.opp_id = a.id
where a.agent_id = ? and l.district = ?

Notes:

• I used n.* to pull out all columns from the notes table, but you really should be enumerating the columns that you expect, so it is clear which columns you want - same goes for a.* in the query

• I modified the query so it uses parameters (denoted by the question marks in the query) - you do want to use parameterized queries to make your code more efficient and safe from sql injection