CODEWITHSUNDEEP
phpmysql
user1651535
user1651535

Reputation: 33

PHP MySQL get data from 2 tables

I am trying to combine 2 tables from my database:

files table:

    id
    file_name
    file_description
    file_url

access_files table:

    id
    student_id
    file_id

Here is my sql code, currently getting all files from the files table, it doesn`t show the selected files for the user.

<?php
    $SQL = "SELECT * FROM files, access_files WHERE student_id ='$studentid'";
    $result = mysql_query($SQL);

    while ($db_field = mysql_fetch_assoc($result)) {
?>                          

<div class="accordion-group">
<div class="accordion-heading">
    <a href="#<?php print $db_field['file_id']; ?>" data-parent="#accordion" data-toggle="collapse" class="accordion-toggle collapsed">
    <?php print $db_field['file_name']; ?>
    </a>
</div>
<div class="accordion-body collapse in" id="<?php print $db_field['file_id']; ?>">
    <div class="accordion-inner">
    <?php print $db_field['file_description']; ?><br/><br/>
    <a href="?download=<?php print $db_field['file_url']; ?>" class="more">Download File Now!</a>
    <br/><br/>
    </div>
</div>
</div>
<?php } ?>

The code is suppose to show only the files associated to the user.

Upvotes: 3

Views: 6277

Answers (3)

Kao
Kao

Reputation: 2272

What you need to do is JOIN the tables.

Most common types of JOINs:

  1. INNER JOIN - Used to match data where it can match the ON () statement. If the ON() doesn't match, the result will be excluded.
  2. LEFT JOIN - Used if the data you match in the ON() doesn't have to be there. It just appends the data to the original FROM, and fills columns with NULL if no data is matched.

Example

SELECT
 ft.id,
 ft.file_name,
 ft.file_description,
 ft.file_url,
 af.id as access_id,
 af.student_id,
 af.file_id
FROM
 files ft
 INNER JOIN access_files af ON ( ft.id = af.file_id )
WHERE
 fa.student_id = '$studentid'

Upvotes: 6

Fluffeh
Fluffeh

Reputation: 33502

You are making a classic cartesian join with your query:

SELECT * FROM files, access_files WHERE student_id ='$studentid'

You need to specify how the two tables are connected:

SELECT * FROM files a, access_files b WHERE a.student_id ='$studentid' and b.studentID=a.student_id

If you don't specify the link - or don't have one, the database will try to link every single row in the first table with every single row in the second.

Upvotes: 3

Ben
Ben

Reputation: 11188

Join your tables.

SELECT table1.*, table2.*
FROM table1
LEFT JOIN table1.pk = table2.fk
WHERE table1.pk = 1;

Upvotes: 2

Related Questions