I need help from you guys here.
The problem is I have to display download link from a table that connected from other tables, there are three (3) tables.
**First Table:**
file_id | file_title | file_name | file_dir
-------------------------------------------
| | |
**Second Table:**
file_id | books_id
-------------------
**Third Table:**
books_id | books_title | books_author | books_publisher
-----------------------------------------------------------
I just want to create a button that can download the file from the first table, the files was stored in a folder. I was little bit confused, why the developer staff before me that built this scripts (now the person was quit and I cannot contact him) add to three tables for uploaded files. And if I was changed the upload field, I have to changed everything.
Any clue?or link that can help me perhaps?to solve my confusedness.
Thank you for the helps from you guys here.
Sorry for my English. :)
I believe the query you're looking for is:
SELECT t1.file_title, t1.file_name, t1.file_dir,
t3.books_title, t3.books_author, t3.books_publisher
FROM first_table t1, second_table t2, third_table t3
WHERE t1.file_id=t2.file_id AND
t2.books_id=t3.books_id
This assumes the names of your tables are first_table, second_table, and third_table. Feel free to modify accordingly.
To use this result in PHP, you could do something like this:
$sql = "SELECT t1.file_title, t1.file_name, t1.file_dir, " .
" t3.books_title, t3.books_author, t3.books_publisher " .
"FROM first_table t1, second_table t2, third_table t3 " .
"WHERE t1.file_id=t2.file_id AND " .
" t2.books_id=t3.books_id";
$query_result = mysqli_query($sql);
$data = array();
while ($row = mysqli_fetch_assoc($query_result)) {
$row_data = array();
foreach ($row as $key => $value) {
$row_data[$key] = $value;
}
array_push($data, $row_data);
}
foreach($data as $item) {
$path_to_file = $item['file_dir'] . '/' . $item['file_name'];
print "<a href='$path_to_file'>" .
$item['books_title'] .
' (Author: ' . $item['books_author'] . ', ' .
' Publisher: ' . $item['books_publisher'] . ')</a>';
print '<br>';
}
Of course, the outputting of HTML is entirely for demonstration purposes - I don't know exactly what kind of formatting you need. The critical pieces to understand are:
$path_to_file together based on the $item['file_dir'] and $item['file_name']$path_to_file.SELECT FirstTable.file_name, FirstTable.file_dir, ThirdTable.books_title, ThirdTable.books_author, ThirdTable.books_publisher INNER JOIN SecondTable ON FirstTable.file_id = SecondTable.file_id INNER JOIN ThirdTable ON SecondTable.books_id = ThirdTable.books_id
INNER JOIN may not necessarily be the JOIN type you want to use, but this would be the general idea for grabbing data from 2 tables corresponding to a third (SecondTable) which links them.
$link = $row['file_dir'] . $row['file_name'];