PHP MySQL get data from 2 tables

Question

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.

Kao · Accepted Answer · 2012-09-06 09:55:20Z

6

What you need to do is JOIN the tables.

Most common types of JOINs:

INNER JOIN - Used to match data where it can match the ON () statement. If the ON() doesn't match, the result will be excluded.
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'

edited Sep 6, 2012 at 9:55

answered Sep 6, 2012 at 9:49

Kao

2,2723 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fluffeh · Accepted Answer · 2012-09-06 09:49:58Z

3

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.

answered Sep 6, 2012 at 9:49

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

Comments

Ben · Accepted Answer · 2012-09-06 09:49:28Z

2

Join your tables.

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

answered Sep 6, 2012 at 9:49

Ben

11.3k19 gold badges80 silver badges140 bronze badges

Collectives™ on Stack Overflow

PHP MySQL get data from 2 tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related