Return search results from second table

Question

I have two tables, Chemicals & Consumables. "Chemicals" stores all of the general info about the chemicals (formula, safety etc) and "Consumables" stores information about stock & location in various rooms etc. The common field is "CAS" (Chemical Abstract Service Registry Number - a combination of numbers and hyphens).

I would like to search the "Chemicals" Table and return information from both tables

Question #1 - should I join them in the database (Navicat) or just through the php query?

Question #2 - why won't the following code work?

$item = $_POST['item'];

$chem = mysql_query("
SELECT * 
FROM Chemicals
INNER JOIN Consumables 
ON Chemicals.Name_Chem1 = '%$item%'");

while ($row = mysql_fetch_array($chem)){

echo
"<table border='0'>
<tr class='content'>
<th>Name</th>
<th>Quantity</th>
<th>GHS Code</th>
<th>Formula</th>
<th>CAS</th>

</tr>";

while($row = mysql_fetch_array($chem))
{

 echo "<tr>";

echo "<td class='content'>" . $row['Consumables.Name'] . "</td>";
echo "<td class='content'>" . $row['Consumables.Quantity'] . "</td>";
echo "<td class='content'>" . $row['Chemicals.GHS_1'] . "</td>";
echo "<td class='content'>" . $row['Chemicals.Formula'] . "</td>";
echo "<td class='content'>" . $row['CAS'] . "</td>";
echo "</tr>";
}
echo "</table>";
}

score 1 · Answer 1 · edited May 23 '17 at 11:56

A1.) join them MySQL.

A2.) Since you have mentioned that their common column is called CAS, you should define them on the ON clause, eg.

SELECT  a.*, b.*
FROM    Chemicals a
        INNER JOIN Consumables b
            ON a.CAS = b.CAS
WHERE   a.Name_Chem1 = '%$item%'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

Return search results from second table

1 Answers1