CODEWITHSUNDEEP
phpmysqllist
Chris Ford
Chris Ford

Reputation: 93

Listing results from two connected tables

I have two tables as following:

files

id | path | filename

changes

id | file_id | change

file_id field is connected to the id which is in files table.

What I do with these tables is storing which files are changed and what changes are done in a file.

What I want to do is listing the files and listing the changes which were made in that table under file.

What is the best and most optimized way to do that in PHP?

Thank you for your help.

Regards.

Upvotes: 2

Views: 67

Answers (1)

netcoder
netcoder

Reputation: 67695

Use a JOIN query:

SELECT files.*,changes.change FROM files 
   LEFT JOIN changes ON change.file_id = files.id
   ORDER BY files.id;

This will get you a result set with each row containing:

  • id
  • filename
  • path
  • change

With the use of the ORDER BY query, you can store the last :

$fileId = null;
foreach ($results as $result) {
    if ($fileId != $result['id']) {
        echo "{$result['filename']}\n";
        $fileId = $result['id'];
    }

    echo "  {$result['change']}\n";
}

Upvotes: 1

Related Questions