-2

I'm looking to join two SQL queries together if possible.. or an alternative method to achieve what I am wanting my table to show. 

$sql = "SELECT id, title, description, multiple, staffid FROM projects";

$sql2 = "SELECT staff.firstname, staff.surname 
        FROM staff 
            JOIN projects ON staff.staffid = projects.staffid";

Currently, I am only able to show the results from one of the queries into my table however.. I would like title, description, multiple to show in the table PLUS the firstname of the staff that I am trying to retrieve using a join.

here is my code.. 

<?php include ('../resources/styling.html'); ?>
<?php include ('adminNavbar.php'); ?>


<div class="container content-area">
<div class="row">
<div class="col-md-12">
<div class="panel">

<br>

<div class="text-center border border-light p-5">
<p class="h4 mb-4">View Projects</p>
</div>

<br>
<table class="table table-hover">
  <thead>
    <tr>
      <th scope="col">#</th>
      <th scope="col">Project Title</th>
      <th scope="col">Description</th>
      <th scope="col">Multiple Attempts?</th>
      <th>View</th>
      <th>Created by</th>

    </tr>
  </thead>
  <tbody>
  </div>
  </div>
  </div>
  </div>

<?php
include ('../resources/config.php');

$sql = "SELECT id, title, description, multiple, staffid FROM projects";

$sql2 = "SELECT staff.firstname, staff.surname FROM staff JOIN projects ON staff.staffid = projects.staffid";

$result = $db->query($sql);
$result2 = $db->query($sql2);
    // output data of each row

    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>".$row['id']."</td>";
        echo "<td>".$row['title']."</td>";
        echo "<td>".substr($row['description'],0, 50)."....</td>";
        echo "<td>".$row['multiple']."</td>";
        echo  "<td><a href='ProjectDetail.php?id=".$row['id']."'>View Project</td>";  
        echo "<td>".$row['firstname']."</td>"; 

    }
    $db->close();
?>
  </tbody>

</table>

currently I am recieving the error

Notice: Undefined index: firstname in C:\xampp\htdocs\test\adminDashboard\ViewProjectsML.php on line 61

which I am aware is because I am not using result2. But with numerous trial and errors I can only get the table to show TITLE DESCRIPTION and MULTIPLE OR FIRSTNAME.. not both. I would be really grateful if someone could point me in the right direction.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
shanls96
  • 1
  • yes you can, use unoin all – Usman Ali Mar 01 '20 at 11:29
  • Since there is a link between projects and staff, write one query instead similar to `$sql2` – Rotimi Mar 01 '20 at 11:29
  • 2
    you already have join statement between `projects` and `staff` tables, just add `id, title, description, multiple, staffid` fields to the second SQL statement and remove the first statement. – ROOT Mar 01 '20 at 11:38
  • You must SELECT all desired columns in your JOIN query. Show us you best attempt without all the unnecessary fluff. This is a mysql question. No other details matter. Show us your table schemas. – mickmackusa Mar 01 '20 at 11:40
  • Sorry, where would i add id title description multiple and staffid to the second sql statements? not sure how I would rewrite it.. sorry im a noob – shanls96 Mar 01 '20 at 11:40

1 Answers1

0

You are so close already.

When you join 2 tables like this, you also have to mention in the SELECT list the column names from both tables that you want to see to have those columns returned in the results set.

$sql = "SELECT p.id, p.title, p.description, p.multiple, p.staffid, 
                s.firstname, s.surname
            FROM projects p
                JOIN staff s ON s.staffid = p.staffid";

Then remove anything to do with the $sql2 query.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
  • I am getting Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in C:\xampp\htdocs\test\adminDashboard\ViewProjectsML.php:54 Stack trace: #0 {main} thrown in C:\xampp\htdocs\test\adminDashboard\ViewProjectsML.php on line 54 when trying to use this method – shanls96 Mar 01 '20 at 11:56
  • SORRY!! I missed a comma in the select list after `p.staffid,` – RiggsFolly Mar 01 '20 at 11:57
  • This is a good candidate for mysql's `USING`. – mickmackusa Mar 01 '20 at 11:58
  • you have saved me haha.. thank you so much its working! – shanls96 Mar 01 '20 at 11:59