Storing audio files in a database

Hello. Can audio files be stored in a MySQL database? And if so, is there a way I can put the files in a CSV file and then batch upload them to the database? Thanks in advance!

I’m not sure about the limits on MySQL types, you might be able to store it in a binary form but there’s unlikely to be a benefit to doing this (assuming it even allowed such arbitrary sizes, you’d have to check). It’d be more typical to store metadata about the audio file (artist, title, length, encoding details and so forth) along with a filepath to the actual file or something similar. Then you have options to use your database for useful fields with relevant searches and you’re not wasting the vast majority of the database on a datatype with no purpose.

That’s really helpful, thank you. Just to explain a bit more, I ultimately want to create a digital dictionary with thousands of entries; and I’d like an audio file to be associated with each entry so that readers can hear the pronunciation of the word. If I have a column in my table with filepaths to the audio file associated with each record, will a clickable audio file show up when I query the record, along with the data from my other columns (phonetic transcription, part of speech, definition, etc.)?

And the filepath will look something like this, right? \projectFolder\dictionaryAudio\fileName.wav

I should not do a relative path, right? Because it won’t work once I upload all the project files to the server? So not: …\projectFolder\dictionaryAudio\fileName.wav

1 Like

Sounds like a nice idea. I guess it would depend on how it’s all set up, relative file paths rely on the idea of a starting or current directory. If you’re uploading all the files together then you already know the relative directory structure but I couldn’t say whether or not best practices would try to avoid stepping backwards. If you’re looking for answers about the web dev side of setting this up I’m afraid I’m the wrong person to speak to as it’s not something I’m much involved with. I’d assume doing it for audio wouldn’t be too different to doing it for other media but if you’re looking for the best option I think it’d be worth a new question to catch someone more knowledgable than me, sorry :sweat_smile:.

No, this was very helpful. Thanks!

Hi, just wanted to follow up on this. I’m having trouble querying my audio. When I query the file paths, they display as text on my webpage, not as audio files. I’ve used the following code to query individual audio files:

<audio controls> <source src="/link/to/mp3/<?php echo $row['audio'] ?>" type="audio/mpeg"> </audio>

The audio didn’t actually work, but it at least displayed as an audio player on the webpage.

Still my ultimate goal is to find a way query multiple data columns and get them to display in a table with one of the columns as clickable audio files. I’ve gotten this to work with data columns that are either text or integers using this code:

<?php $sql = "SELECT id, firstname, lastname FROM MyGuests"; $result = $conn->query($sql); if ($result->num_rows > 0) { echo "<table><tr><th>ID</th><th>Name</th></tr>"; // output data of each row while($row = $result->fetch_assoc()) { echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>"; } echo "</table>"; } else { echo "0 results"; } $conn->close(); ?>

But I don’t believe I can place the HTML audio tag into the echo statement. Or at least I couldn’t get it to work.

Any suggestions would be much appreciated.

I’d highly suggest setting that up as a new question and maybe tagging it with PHP or add it to the title if you can’t :+1:

1 Like