0

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>";*/


}
pala_
  • 8,901
  • 1
  • 15
  • 32
Matt
  • 35
  • 1
  • 5
  • Use a `join`, https://dev.mysql.com/doc/refman/5.0/en/join.html. Is `$q` from user input? – chris85 Jun 03 '15 at 02:39
  • Yes $q is from a select box. – Matt Jun 03 '15 at 02:48
  • Then don't input that directly into your query. Use prepared statements. That will open you to SQL injections. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – chris85 Jun 03 '15 at 02:49
  • ^^. it's good that you're using mysqli, you should also look into prepared statements and parameter binding. – pala_ Jun 03 '15 at 02:54
  • Thanks guys, modifying my code to use prepared statements as specified in the linked thread. – Matt Jun 03 '15 at 03:09

1 Answers1

2

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)

pala_
  • 8,901
  • 1
  • 15
  • 32
  • This works when i don't add the WHERE lease = q clause at the end of the select. I can access all records from both tables however i only want the ones that = $q variable from user input. Is there a way to do this or am i missing something with your answer. – Matt Jun 03 '15 at 03:03
  • The `where` should go after the `on ...` of the join. Defer to the documentation for examples, https://dev.mysql.com/doc/refman/5.0/en/join.html. – chris85 Jun 03 '15 at 03:07