php - Return data from two tables based on identical value

Question

I have two tables with 3 columns each

People

id_number / first_name / last_name / town

T1234     / James      / Smith     / Dant
T1235     / Peter      / Mantle    / Dant
T1236     / Milly      / Wong      / Mormer
T1237     / Susan      / Tan       / Dant

Dates

number / id_reference / expiry

T1234  / Student id   / 24/12/2018
T1235  / Library Card / 23/07/2019
T1236  / Library Card / 16/07/2019
T1235  / Licence      / 02/03/2018

I'm trying to run a query to display data from both efficiently but having issues. Basically what I need is to run a query and get an answer like below with data from both tables, id_number and number are the same. So I need the query to find the data from one table, then get the expiry date from another:-

id_number / last_name / expiry

T1234     / Smith     / 24/12/2018
T1235     / Mantle    / 23/07/2019
T1237     / Tan       / 02/03/2018

<?php

        $dbconn = pg_connect("host=127.0.0.1 dbname=sammy_nt user=sammy_nt password=******) or die('Could not connect: ' . pg_last_error());
    $town = pg_escape_string($_POST['town']);
        $query = "SELECT * FROM People p, Dates d
            WHERE p.id_number=d.number
            AND p.town='$town'
            ORDER BY p.id_number";

        $result = pg_query($query); 
        if (!$result) { 
            echo "Problem with query " . $query . "<br/>"; 
            echo pg_last_error(); 
            exit(); 
        } 

        while($myrow = pg_fetch_assoc($result)) { 
            printf ("<tr><td>%s</td><td>%s</td><td>%s</td></tr>", $myrow['id_number'], htmlspecialchars($myrow['last_name']), htmlspecialchars($myrow['expiry']));
        } 
        ?> 

Please assist.

Answer 1

In your case you have 2 tables. People and Dates from which you want to fetch data. You can use the below SQL Query with INNER JOIN.

SELECT People.*, Dates.* FROM People INNER JOIN Dates ON People.id_number = Dates.number"

Please note that you can append SQL Query from WHERE and with ORDER BY and/or LIMIT as you would normally do.

Explanation of this is below:

First you have SELECT. After that you have People.*, which means that All from table People. So you write table name and the . and field (* for All). Add a comman , after this and then the next table with the same syntax. In your case it is Dates.*. Then you add FROM First Table Name. After this you add INNER JOIN and Second Table Name. After this you define the reference between the two tables by ON and then the fields from both the tables People.id_number = Dates.number. Syntax is the same, Table.Field.

Now you can simply display the result normally.

Please note that I have provided you with the SQL Query that you can insert in your code. It might be PDO or Prepared Statement.

If you require further clarification, do not hesitate in letting me know.

Hope this helps.