CODEWITHSUNDEEP
phpmysql
Matt
Matt

Reputation: 35

PHP MYSQL Select query on two tables with common field

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

Answers (1)

pala_
pala_

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

Related Questions