CODEWITHSUNDEEP
phpmysqlsqlselect
user1940081
user1940081

Reputation: 13

Return search results from second table

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>";
}

Upvotes: 1

Views: 106

Answers (1)

John Woo
John Woo

Reputation: 263723

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.

Upvotes: 1

Related Questions