Joins!

uksherminator · July 12, 2017, 4:49pm

Hi there, this is the code I have set up to retrieve project details from the database.

Currently the
Customer = customers_id displayed from the customers table
Architect = suppliers_id displayed from the suppliers table
Structural Engineer = suppliers_id shown from the suppliers table

I would like to change the all id’s to the full_name column on respective tables (Customer to customers.customers_full_name and Architect and Structural Engineer to suppliers.suppliers_full_name)

I think this needs to be done as a JOIN but am really struggling to get it all working. I can get customer done and Architect but cant seem to join two fields to one table/column.

Any help would be appreciated.

<title>Theme Template for Bootstrap</title>

<!-- Bootstrap core CSS -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- Bootstrap theme -->
<link href="css/bootstrap-theme.min.css" rel="stylesheet">
<!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
<link href="css/ie10-viewport-bug-workaround.css" rel="stylesheet">

<!-- Custom styles for this template -->
<link href="css/theme.css" rel="stylesheet">

<!-- Just for debugging purposes. Don't actually copy these 2 lines! -->
<!--[if lt IE 9]><script src="js/ie8-responsive-file-warning.js"></script><![endif]-->
<script src="js/ie-emulation-modes-warning.js"></script>

<!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
<!--[if lt IE 9]>
  <script src="https://oss.maxcdn.com/html5shiv/3.7.3/html5shiv.min.js"></script>
  <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
<![endif]-->

<?php include 'db/connection.php'; ?> <?php $projectid=$_GET['id']; ?>

<nav class="navbar navbar-inverse navbar-fixed-top">
  <div class="container">
    <div class="navbar-header">
      <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
      <a class="navbar-brand" href="http://localhost/belmore/">Belmore</a>
    </div>
    <div id="navbar" class="navbar-collapse collapse">
      <ul class="nav navbar-nav">
                  <li class="dropdown">
          <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Menu <span class="caret"></span></a>
          <ul class="dropdown-menu">
            <li><a href="#">Action</a></li>
            <li><a href="#">Another action</a></li>
            <li><a href="#">Something else here</a></li>
          </ul>
        </li>
      </ul>
    </div><!--/.nav-collapse -->
  </div>
</nav>

<div class="container theme-showcase" role="main">
	<div class="page-header">
		<h1>View Project</h1>
	</div>
		<div class="col-md-10">
			<table class="table table-striped">
				<thead>
					<tr>
						<th>id</th>
						<th>Project Name</th>
						<th>Project Number</th>
						<th>Customer</th> 
						<th>Architect</th>
						<th>Structural Engineer</th>				
					</tr>
				</thead>
				<tbody>
					<?php
					$sql = "SELECT *
   					FROM projects
   				    WHERE projects_id = $projectid";
					$result = $conn->query($sql);
						if ($result->num_rows > 0)
						{
					// output data of each row
						while($row = $result->fetch_assoc())
						{
							echo '<tr>';
							echo '<td>' . $row['projects_id']. '</td>';
							echo '<td>' . $row['projects_name']. '</td>';
							echo '<td>' . $row['projects_number']. '</td>';
							echo '<td>' . $row['projects_customer']. '</td>';
							echo '<td>' . $row['projects_architect']. '</td>';
							echo '<td>' . $row['projects_structural_engineer']. '</td>';
							echo '</tr>';
						}
							} else {
							echo "0 results";
									}
					?>
				</tbody>
			</table>
		</div>
	</div>

<!-- Placed at the end of the document so the pages load faster -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script>window.jQuery || document.write('<script src="../../assets/js/vendor/jquery.min.js"><\/script>')</script>
<script src="js/bootstrap.min.js"></script>
<script src="js/docs.min.js"></script>
<!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
<script src="js/ie10-viewport-bug-workaround.js"></script>

phdr · July 14, 2017, 5:07pm

First of all, none of the html you have posted is even relevant to the problem and made it harder to even find where the bits of the relevant code is at.

You need to separate the database specific code, that knows how to query for and retrieve the data, from the presentation code, that knows now to produce the output from the data. The database specific code would go before the start of your html document. This will make it easier to design, write, test, and debug your code and to only post the relevant section when asking for help. The way to do this separation is to just fetch any data from the query into an appropriately named php variable, then use that php variable as input to the presentation code.

Next, don’t put data values directly into an sql query statement. This can cause errors and sql injection if the data accidentally or deliberately contains sql special characters. Use a prepared query instead (you can research the web and the php.net documentation to find out what this is and how to do it) and while you are at it, if you can, switch to use the php PDO extension. The php PDO extension is MUCH simpler and more consistent to use then the php mysqli extension.

So, finally, here is just the relevant part of what you posted, with the data value replaced with a prepared query place-holder, and posted using the forum’s bbcode php tags -
[php] $sql = “SELECT *
FROM projects
WHERE projects_id = ?”;
[/php]

To form a JOIN’ed query, you would SELECT the columns that you want FROM the main table holding the data JOIN’ed with each successive table holding the related data ON any join condition to match up the data WHERE any condition to determine what overall data to match. You should always use table ALIAS names, but table aliases are required when you join to the same table more than once, which is what you are doing to get the suppliers data.

The query would look (untested) like this -
[php]$sql = “SELECT p.projects_id, p.projects_name, p.projects_number,
c.customers_full_name,
a.suppliers_full_name AS architect_full_name,
se.suppliers_full_name AS structural_engineer_full_name
FROM projects AS p
JOIN customers AS c ON p.projects_customer = c.customers_id
JOIN suppliers AS a ON p.projects_architect = a.suppliers_id
JOIN suppliers AS se ON p.projects_structural_engineer = se.suppliers_id
WHERE projects_id = ?”;[/php]

I include the AS keyword where the table and column alias names are being defined, but you can leave the AS out once you become familiar with the syntax.

If data may not exist yet, such as if the architect and/or structural engineer has not been selected and entered, and you want to display the project and customer information anyway, you would change the last two joins to LEFT JOIN.