使用mysql中的3个表使用php创建下载脚本

Question

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. :)

Answer 1

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:

• piece the $path_to_file together based on the $item['file_dir'] and $item['file_name']
• make your link (or your button, or whatever you choose to use) point to that $path_to_file.