SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member frankie54's Avatar
    Join Date
    Feb 2001
    Location
    NYC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's my first post! (baby steps...)

    I wanted to greet everyone and say that I was very
    pleasantly surprised to find such a wealth of information
    and lively discussions on this forum; anyway, on to my
    newbie rantings...

    In an example recently quoted on this forum (music dbase),
    and supposing I am using some kind of browser front end to
    input the data into a MySQL database (for ex. the song,
    label and artist data for a CD), there must be a PHP script
    which automatically send the SQL statement that populates
    the various tables (artist, song, label, CD and whatever
    else) when I click the submit button after typing the
    information. --oversimplification--

    After entering a few of those CD's, what would the steps
    required to find a particular song (and its corresponding
    CD, artist, etc...) and display those results back in the
    browser?

    In other words, after the data is entered into those
    various tables, where and how does the database server
    maintain the relationship which associates a particular
    song with several artists, several CD's....?

    Lookup tables which keep each table's primary keys
    associated? (artists, songs, CD, and so on)

    What would the SQL query statement look like to retrieve
    the info on a particular song?

    How about the statement needed to automatically update the
    lookup table(s) when adding new songs to the database?

    Sorry if this topic has been covered before.

    Any help appreciated.

    FK
    stay groovy !

  2. #2
    SitePoint Evangelist
    Join Date
    May 2000
    Location
    Canada
    Posts
    533
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lookup tables?

    anyway, you might want a query in the form of:

    $query = "SELECT * FROM table WHERE title LIKE '%$searchstring%' OR ARTIST LIKE '%$searchstring%'";


    $searchstring if the thing you are searching for... the % signs are wildcards in mySQL ... after that, just loop through resullts and output

    $result = mysql_query($query);
    while($row = mysql_fetch_Array($result))
    {
    $artist = $row['artist'];
    echo $artist;
    // etc
    }
    cogito, ergo sum

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I believe FK is referring to look-up tables in terms of bridging entites in many-to-may realationships; ie, a Song may have a many-to-many relationship to Album (An Album has many Songs and a Song may appear on many albums). So your relational schema will require the tables Song, Album, and the "lookup table" Song_Album whose rows contain foreign keys to SongTitle and Album records.

    If I get the gist of what you are asking FK, the way you use these relationships in your queries is through joins. A join between two tables produced a set which is the product of the two sets being joined. For example, if we have table X with the values with the records {X1, X2 X3} and table Y which contains the records {Y1, Y2, Y3} and we join the two tables we get these results:
    Code:
    Table X
    -------
    X1
    X2
    X3
    
    and 
    
    Table Y
    -------
    
    Y1
    Y2
    Y3
    
    and we join these two tables we get
    
    Table X . Table Y
    ---------------
    X1   Y1
    X1   Y2
    X1   Y3
    X2   Y1
    X2   Y2
    X2   Y3
    X3   Y1
    X3   Y2
    X3   Y3
    So what's this gotto do with your question?

    OK, lets say you want to find the details of all the CDs that contain a certain song and you have the following database schema:

    Code:
    Song
    -----
    ID
    name
    length
    
    primary key = ID
    
    Album
    ------
    ID
    name
    length
    
    primary key = ID
    Now the relationship between Song and Album is many-to-many, so you need the bridging entity Song_Album.
    Code:
    Song_Album
    -----------
    songID
    albumID
    
    primary key = songID, albumID
    Note that for Song_Album I have specified a "composite" key; that is, for each record the combination of songID and albumID must be unique.

    Now, to find the details (Album.name, Album.length) of all albums that a certain song (Song.name) appears on I would write the following query which "joins" the three tables Album, Song and Song_Album. Using php the song name to search for is stored in $songName.

    Code:
    $sql = "
       SELECT Album.name AS albumName, Album.length AS albumLength
       FROM Song, Album, Song_Album
       WHERE Album.ID = albumID
       AND Song.ID = songID
       AND Song.name = '$songName' ";
    The "FROM" clause joins the three tables creating a mega "table" which is the product of all three tables (as explained above).

    The "WHERE" clause tells the sql server to narrow down the result set to just those records in Album which have a relationship to entries in Song_Album which in turn have a relationship to records in Song which have a name that matches $songName !

    Note that in the WHERE clause I use both the table name and column name to identify Song.ID and Album.ID. This is to avoid the ambiguity that arises from both Song and Album having an attribute "ID". If I had ommitted this, when I send the sql string in a query, the sql server would return an error.

    Note I haven't considered performance issues in the syntax of my query.

    Look through the resent postings in this forum as somewhere I've given some urls to an online tutorial and book on SQL.
    Last edited by freakysid; Feb 9, 2001 at 14:43.

  4. #4
    SitePoint Member frankie54's Avatar
    Join Date
    Feb 2001
    Location
    NYC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid
    I believe FK is referring to look-up tables in terms of bridging entites in many-to-may realationships; ie, a Song may have a many-to-many relationship to Album (An Album has many Songs and a Song may appear on many albums). So your relational schema will require the tables Song, Album, and the "lookup table" Song_Album whose rows contain foreign keys to SongTitle and Album records.

    Look through the resent postings in this forum as somewhere I've given some urls to an online tutorial and book on SQL.
    Yes, you are correct.

    In the case I am asking about, there is also a record label table involved. How does that change the join and select statement?

    Also, are the "bridging" tables created on-the-fly, and how complicated and time-consuming is this for the database server? How would one go about making those tables permanent or cached to avoid choking either in processor or
    application?

    Thanks for taking the time and answer.

    FK
    stay groovy !

  5. #5
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The look-up or "bridging" tables are just regular tables that you need to create and populate with data yourself. Most relational database management systems (RDBMS) allow you to create "referential integrity" rules so that if you update or delete an item in one table, the records in tables which have a foreign keys relating to the record are also updated or deleted. But, MySQL doesn't handle this (yet). So you must write appropriate sql statements to update all tables that may be affected by an update or delete to maintain the referential integrity of your data.

    Regarding the music database. Can you post a link to where this is discussed? I remember a discussion involving this example. I think I even contributed to it and managed only to confuse with my post!

  6. #6
    SitePoint Member frankie54's Avatar
    Join Date
    Feb 2001
    Location
    NYC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid
    The look-up or "bridging" tables are just regular tables that you need to create and populate with data yourself. Most relational database management systems (RDBMS) allow you to create "referential integrity" rules so that if you update or delete an item in one table, the records in tables which have a foreign keys relating to the record are also updated or deleted. But, MySQL doesn't handle this (yet). So you must write appropriate sql statements to update all tables that may be affected by an update or delete to maintain the referential integrity of your data.

    Regarding the music database. Can you post a link to where this is discussed? I remember a discussion involving this example. I think I even contributed to it and managed only to confuse with my post!
    Hi there,

    The original discussion was posted here.

    Quite an interesting one, I might add.

    Now about your above comment, are you saying that this
    would be one of the merits of using PostgresSQL or such
    rather than MySQL?

    Any chance of hearing anyone's experiences with database
    abstraction layers (which from what I understand allow the
    author to port their code to different back-end database
    servers) in such situations where one system supports
    referential integrity and the other doesn't?

    Also, where do I find working examples of those "bridging
    tables" being automatically created with PHP when the user
    is entering new data?

    Thanks

    FK
    stay groovy !

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No RDBMS is going to automatically create a table. So you need to create all the tables you need to represent your relational schema. You also need to insert all the records in each table to represent how, say a song might relate to an album. The refrenetial integrity stuff just means that some RDBMS will help maintain the integrity of your data by following certain rules such as "don't allow a record to be deleted if that record is referenced by a foreign key in another table". As MySQL doesn't allow this you have to maintain this logic in your application (say your PHP code) which is OK if you have just one application accessing the database. But if the database is a server to many applications then your data is less secure because you are relying on each application to maintain the relational integrity of the data.

    So, the lack of data integrity in MySQL is IMHO one of its weaknesses (at the moment).

    Regarding MySQL vs PostegreSQL. Id on't have much experience in either. But I believe MySQL's advantage is in speed - handy for web application where most of the time you are "reading from the database" with SELECT queries and doing less UPDATE, INSERT, DELETE queries.

    Regarding the RecordLabel table you alude to. I'll assume that there is a one-to-many relationship between albums and record labels. That is an album can belong to only one record label, but a record label can relate to many albums. In this case, you just need a column in your Album called, say, "label_ID" which is a foreign key to a record in the Record_Label table. You don't need a bridging entity in this case.

    Now, lets say we want to modify my previous sql query statement to find include the record label name. Lets assume that table Record_Label looks like this.
    Code:
    Record_Label
    ------------
    ID 
    name
    location
    
    primary key = ID
    So the sql will look like this

    Code:
    $sql = "
       SELECT Album.name AS albumName, Album.length AS albumLength, Record_Label.name AS labelName
       FROM Song, Album, Song_Album, Record_Label
       WHERE label_ID = Record_Label.ID
        AND Album.ID = albumID
       AND Song.ID = songID
       AND Song.name = '$songName' ";
    So now you are joining 4 tables. Note, it might be more effiecient to break this up into a couple of seperate queries.

    Have you read Kevin Yank's tutorial on PHP and MySQL at www.webmasterbase.com ?I think there is a link to it in the music database thread.

  8. #8
    SitePoint Member frankie54's Avatar
    Join Date
    Feb 2001
    Location
    NYC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid

    So now you are joining 4 tables. Note, it might be more effiecient to break this up into a couple of seperate queries.

    Have you read Kevin Yank's tutorial on PHP and MySQL at www.webmasterbase.com ?I think there is a link to it in the music database thread.
    --------------------------------------------------------

    Hi again,

    Thanks for the detailed answer.
    I am in the process of reading the tutorial in question,
    which probably will take a while to absorb (108 pages) !!!!

    Curious on how I can determine the efficiency of a query?
    I seem to remember hearing about some kind of benchmarking tool
    you can run on your database server, or this part of the MySQL utilities??

    FK
    stay groovy !

  9. #9
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A simple way to test the time required to run your query is to enter it at the command line in mysql server. As well as receiving your result set you will get the info as to how long it took to process the query.

    You may be able to have this info returned to PHP by the query. I dunno. Perhaps others here will know this.

    With many RDBMS you can even query the server as to how it plans to execute a query. By looking at its explaination you can tweek your sql and indexing. Again I don't know about how you do this in mysql. Someone else here may chip in and explain.


Bookmarks

Posting Permissions

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