Reputation: 35
I need to get record from two tables that have the same column (lease). I use the code below to get the records from the table (rows) where $q is equal to the (lease) column. This works fine however I would like to get records out of a different table (units) that also has a (lease) column in the same select query and use all these records in a while loop. Below is the code i am currently using to pull the data out of one table, could someone please help me modify the code to pull data out of both tables. Is this possible? or am i going about this the wrong way? I have tried looking into JOIN but i just cant seem to get my head around it, have tried many examples but none seem to work.
$sql = "SELECT * FROM rows WHERE lease = '".$q."'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "row " . $row["row_id"]. "Capacity" . $row["capacity"]. "<br>";*/
}
Upvotes: 0
Views: 2945
Reputation: 9010
To get values from two tables that share a common value, you use a join
.
eg:
select *
from table1
inner join table2
on table1.common_field = table2.common_field
This query will give you all the matching rows, and exclude those rows that don't match.
If instead you want to get all the rows from the first table, as well as the row that match from the second table, change inner join
to left join
.
That will give you all the rows from table1, all the matching rows from table2, and null
values for table2 where the rows don't match.
Since you are using PHP - if the tables contain other fields with the same name, you will need to alias them in the select query in order to make them all available in the result set. (ie, select table1.somevalue as value1, table2.somevalue as value2
)
Upvotes: 2