Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2009
    Posts
    9

    Unanswered: Link mySQL db to content

    Hello

    I am building a site that will enable people to stream MP3 content.

    I have been working on the basis that it's more efficient to link the database entries to the audio content rather than actually have the content in the database.

    What I am puzzling with is how to connect database entries to the actual audio files. I've looked for ages on Google but not found the answer to my question.

    Thanks in advance for any help - do say if you need further info.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Bazzaah View Post
    What I am puzzling with is how to connect database entries to the actual audio files.
    Code:
    INSERT INTO mp3s ( title , genre , location )
    VALUES ( 'my lonely heart' , 'country rock' , 'D:/stolen/country/lonely.mp3' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    9
    Quote Originally Posted by r937 View Post
    Code:
    INSERT INTO mp3s ( title , genre , location )
    VALUES ( 'my lonely heart' , 'country rock' , 'D:/stolen/country/lonely.mp3' )
    Thanks.

    The content is not music and the copyright over the material is mine; just wanted to make that clear to you.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Bazzaah View Post
    just wanted to make that clear to you.
    no prob, sorry, i was just having a spot of fun
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2009
    Posts
    9
    ok, no biggie - I obviously missed the joke there.

  6. #6
    Join Date
    Nov 2009
    Posts
    9
    This is the form of the database I will use, most likely.

    The db will link to an audio dictionary.

    Obviously the commands you give above will insert a link to the location of the file but equally there needs to be something in the relevant table to receive the data that specify that file's location.

    Could I use a text entry to do that, on the line 'wordfile' to do that?

    CREATE TABLE words (
    word_id int(50) NOT NULL auto_increment,
    word varchar(255) NOT NULL default '',
    sentence1 text NOT NULL,
    wordfile text NOT NULL,
    status tinyint(1) NOT NULL default '0',
    PRIMARY KEY (`word_id`),
    UNIQUE KEY `word` (`word`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    Thanks for the help.
    Last edited by Bazzaah; 11-17-09 at 04:53. Reason: change question

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Bazzaah View Post
    Could I use a text entry to do that, on the line 'wordfile' to do that?
    sure

    and it should likely be VARCHAR(255) as i don't think the path will be any longer than that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Not sure if you're uploading files via a web interface but if so this link might help. Do you need to associate mp3 files with certain tags and would this be stored in your database. Would you need a field to record who uploaded the file and when? If you're storing words then would you need to have a language field? if only for future expansion. What is it you're actually trying to store?

  9. #9
    Join Date
    Nov 2009
    Posts
    9
    Quote Originally Posted by mike_bike_kite View Post
    Not sure if you're uploading files via a web interface but if so this link might help. Do you need to associate mp3 files with certain tags and would this be stored in your database. Would you need a field to record who uploaded the file and when? If you're storing words then would you need to have a language field? if only for future expansion. What is it you're actually trying to store?
    The site is on a LAMP server I run myself - I'm the only uploader.

    I might expand at some point, so the language field is a good thought, thanks.

    What I'm looking to store is a few thousand audio files. I've copied them to my server so all I need to do is construct a database, the content of which I can echo to the relevant page on the site through a php script - the media player will follow.

    I'll need to spend some time making entries to the database, of course, so I'm hoping that maybe there's a Perl script I could use for that. Otherwise, it's a looooong job!

    I'm teaching myself as I go, so it's one step at a time. The db only needs to be fairly simple I think, as it's job is simple.

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Bazzaah View Post
    I'll need to spend some time making entries to the database, of course, so I'm hoping that maybe there's a Perl script I could use for that. Otherwise, it's a looooong job!
    It should be straightforward building insert statements from a list of files - When you're ready post a question in the unix forum and we should be able to sort that out pretty quick. I'd suggest giving a few examples of your file names and what the insert statement should look like. You still haven't explained what the mp3 files contain - it's often quite interesting to hear about what people are doing (or attempting).

  11. #11
    Join Date
    Nov 2009
    Posts
    9
    Thanks.

    The MP3s contain recordings of people saying common words, together with sentences where those words are used. They'll be used in an on-line language tuition aide I'm working on.

  12. #12
    Join Date
    Nov 2009
    Posts
    9
    One thing I was wondering is this - my site currently links to a database that manages people who register to use the site.

    Should I make the audio db one a table within the existing database or start a new one?

    My connection script only seems to envisage one db and not sure how a second might affect things?

    Thanks for any comments on this.

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Thanks for any comments on this.
    You'll want to use a single database and store the mp3 info in a single table along with the person who created the mp3. I'll assume only the owner of the mp3 can edit the info or delete the mp3 so just check the current user has the same id as the current mp3 record. Only show the edit and delete buttons to users who are allowed to alter the records. If a special user (ie you) is looking at the files then you should be allowed to edit or delete anything. It's not a bad idea having a MySQL function that says whether the current user can edit the current record.

  14. #14
    Join Date
    Nov 2009
    Posts
    9
    Thanks for that.

    So it's better to have one table for users and others for content as part of the same one db for the site.

    I'll make any changes direct on the server itself, so I don't currently envisage including anything like an admin area to amend users/content.

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You'll need to allow users to edit their entries (I'm guessing) so why not use the same code to allow you to edit their entries. Just show the edit buttons and allow editing if the current user is the owner or the the admin. Then you don't need to mess with SQL to alter things. If you don't want to use the front end then it suggests there's something wrong with the front end and your users are unlikely to use it either.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •