Using SQL Inner Join

I have an SQLite database stored on my MacBook Pro laptop that I use to track work requests. The database file is named CRQ.db, since it tracks work done under a Change Request (CRQ). Within that database are several tables two of which are "Equipment" and "Device". For every CRQ that I need to deal with, I store information on equipment that is affected by the CRQ. The equipment table has the following structure:

The device field holds a unique name assigned to the particular device. A device may be affected by more than one CRQ, so it can, potentially, occur multiple times in the table. A CRQ can affect multiple pieces of equipment.

Name	Type
CRQ	Text
Device	Text
Project	Text
Notes	Text

Name	Type
Description	Text Unique
Manufacturer	Text
Model	Text
Site	Text
Building	Text
Room	Text
Notes	Text

The description column in the table is the primary key for the table. The description field is the unique name assigned to the device and, though the device name can occur multiple times in the equipment table, that name should occur only once in the device table, since that table holds the attributes for the device, such as the manufacturer, model number, and its physical location. The contents of the "description" field is the same information that occurs in the "device" field in the equipment table.

When checking information for a CRQ, I sometimes want to see a list of the equpment affected by that CRQ. If I look in just the equipment table, I'll only see the device name and the project for which the work is being done. I also want to see details on the device, though, such as the manufacturer, model number, and the site where the equipment is located. To do so, I can use an Structured Query Language (SQL) " inner join" statement to combine information from the two tables in one list. I.e., I use the following statement:

SELECT Equipment.Device, Device.Manufacturer, Device.Model, Device.Site from Equipment INNER JOIN Device on Equipment.Device=Device.Description WHERE CRQ=$crq";

I use INNER JOIN Device on Equipment.Device=Device.Description for the INNER JOIN, since the "Device" column in the Equipment table correlates with the "Description" column in the Device table. E.g., if I have rsf3-v136a in the Device field for a record in the Equipment table, there should be an entry with that same name in the Description field in the Device table. The $crq variable represents the relevant CRQ. I perform the query on a web page using PHP. The code on the webpage is as follows:

<!DOCTYPE HTML>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Change Request (CRQ) - Equipment</title>
<style type="text/css">
   table { border: 1px outset black; }
   th { padding: 5px; border: solid 1px black; }
   td { padding: 5px; border: solid 1px black; }
</style>
</head>

<body>

<?php

$filename   = "/Users/jasmith1/Documents/www/CRQ/CRQ.db";

if (isset($_GET["crq"])) {
   $crq = $_GET["crq"];
   $query_string = "SELECT Equipment.Device, Device.Manufacturer, Device.Model, Device.Site from Equipment INNER JOIN Device on Equipment.Device=Device.Description WHERE CRQ=\"$crq\"";
   echo "<h1 style=\"text-align: center\">" . $crq . " - Equipment</h1>";
}
// Display an error message and exit
else {
   exit("Error! No CRQ specified");
}

$db_handle  = new SQLite3($filename);
$results     = $db_handle->query($query_string);

echo "<table>\n";
echo "<tr><th>Device</th><th>Manufacturer</th><th>Model</th><th>Site</th></tr>\n";
while ($row = $results->fetchArray()) {
    echo "<tr><td>" . $row['Device'] . "</td>" .
         "<td>" . $row['Manufacturer'] . "</td>" . 
         "<td>" . $row['Model'] . "</td>" .
         "<td>" . $row['Site'] . "</td></tr>\n";
}
echo "</table>\n\n";

?>

</body>

</html>

I access the information in a browser on the laptop using a URL such as http://localhost/CRQ/equipment.php?crq=CRQ000001115427. In the line

   $query_string = "SELECT Equipment.Device, Device.Manufacturer, Device.Model, Device.Site from Equipment INNER JOIN Device on Equipment.Device=Device.Description WHERE CRQ=\"$crq\"";

, I need to enclose $crq in double quotes, but those double quotes appear within surrounding double quotes, so I have to "escape" the meaning of those double quotes around $crq by preceding them with an escape character, which in this case is a backslash character, i.e., \.

If no CRQ number is provided, i.e., if I inadvertently used just http://localhost/CRQ/equipment.php for the URL, then the error message "Error! No CRQ specified" will be displayed on the page that would return the results for a query. If an invalid CRQ number is provided, e.g.,

http://localhost/CRQ/equipment.php?crq=CRQ000001115426

, then only the table header row will be displayed. Otherwise, a table similar to the one below should be displayed:

Device	Manufacturer	Model	Site
rsf1-eo1-x129a	Cisco	3560v2	RSFC
rsf3-v136a	Juniper	EX4200	RSFC
rsf1-eo1-x129b	Cisco	3560v2	RSFC
rsf3-v135a	Juniper	EX4200	RSFC

Using SQL Inner Join

CRQ000001115427 - Equipment