How to query a file path from MySQL table and get it to display as a link-CodePudding

I need to figure out how to query several columns of data into a table on my webpage using PHP and get one column to display as clickable audio files. In my MySQL table the audio column consists of absolute paths to audio files saved in a folder on the disk of my web host. All attempts so far have only succeeded in querying the file path as text, not as clickable audio.

I've been using a PHP loop statement to try to query several data columns into a table, like this:

<?php
$sql = "SELECT id, word, audio FROM dictionary";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["word"]. " " . $row["audio"]. "<br>";
  }
} else {
  echo "0 results";
}

mysqli_close($conn);
?>

Here's a photo of my table with one record in it: enter image description here

But I've also tried the URL in place of the absolute path: https://memoriaelinguagrumentina/dictionaryAudio/a1.com

In either case, the path displays as text.

Any suggestions on how to get the audio to display on my webpage as something clickable?

CodePudding user response：

You would just need to change it so the echo that you output to the page is wrapped in an anchor tag something like this. You'll need to add in the href where it is going to be going based on your data tho.

echo "<a href=''>" . "id: " . $row["id"]. " - Name: " . $row["word"]. " " . $row["audio"] . "</a>" . "<br>";

CodePudding user response：

You have to output the $row["audio"] as a HTML a tag, but the absolute path is not always the path the file is accessible from outside.

The PHP code should look like something like this:

echo "id: ".$row["id"]." - Name: ".$row["word"]." <a href='".$row["audio"]."'>Click here to play audio</a><br>";

But for example if your audio file's path is /var/www/website/files/01.mp3 this isn't the path that can be used as a http link. You should convert it to a useable URL. If in the above example, your domain is website.com and your document root on the server is the /var/www/website you should do something like this:

func FilePathToURL($fp) {
    $protocol = isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http";
    return $protocol."://website.com".substr($fp, strlen("/var/www/website"));
}

And use this function to echo the correct link like:

echo "<a href='".FilePathToURL($row['audio'])."'>Click here</a>";