SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Relational Databases - Basic principles

    Ive been looking at relational databases for the first time....just so that i can get things clear in my mind, could someone just confirm that the following is the correct method (or even structure!):

    For example, databases tables as:

    tbl_event
    eventID
    Desc (etc etc(holds most info))

    tbl_date
    eventdateID
    date

    tbl_image
    eventimageID
    image name/alt text etc

    tbl_sector
    sectorID
    artform, bkcolour etc

    To pull down the information i would use something like:

    SELECT* from tble_event, tbl_date, tbl_image, tbl_sector WHERE tbl_date.eventdateID, tbl_image.eventimageID, tbl_sector.sectorID = tbl_event.eventID

    .........how could i add 2 other filters, like show only sector ID =2 and sort by date ID?

    Please excuse the crudity, but im just typing as i think!
    Note, ill be using Dreamweaver to contruct the initial recordsets and then editing them after accordingly

    Many thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    three points --

    first, it is not necessary to create a "lookup" table with a surrogate key for each date -- just use the date

    second, take a moment to learn JOIN syntax -- you are using the old "table list with WHERE clause" syntax for your joins, and that's the hard way (and it's impossible if you want to do outer joins)

    finally, never use the evil "select star"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your response...just one thing that im not sure about...

    first, it is not necessary to create a "lookup" table with a surrogate key for each date -- just use the date
    Maybe its the wrong method, but ive constructed a date table for events that may contain more than one date, therefore refering to the eventdateID..or isnt this what you meant?

    cheers

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    maybe it's okay -- could you show some sample rows of your date table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry i just got your post....maybe you could help me a little (running before walking comes to mind....)

    to pull down the initail list of records i have

    PHP Code:
    $query_Recordset1 "SELECT * FROM props, gallery_photos WHERE gallery_photos.photo_category = props.propID"

    ...this is working fine, it pulls down all the records, however i have a detail button that will pull down all the information.

    What ive done before in the Dreamweaver enviroment is added something like

    PHP Code:
    ebro fincas properties_detail.php?ID=<?php echo $row_Recordset1['propID']; ?>
    How do i change the detail page recordset to relect this?

    Many thanks in advance

    b

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't do dreamweaver, and i don't do php

    just run this query and show me the first few rows --
    Code:
    select * from tbl_date order by 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im not actually working on the first query (about the date)...i will be starting that tomorrow morning.

    Im just 'playing' with the concept of numerous database tables.

    Ive managed to filter/sort the database tables to form a recordset...which im rather happy about, but my smuggness i quickly disappear now that i trying to get a detail page

  8. #8
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry...its me again!

    Ive been thinking about 2 many things at once and forgot what i had meant to be doing and therefore getting in a right mess. All im looking for is to display records with images specific to that record. Therefore i thought i should have one table with details 'props' (details of properties etc) and one for images 'gallery_photos'.

    I just dont know how to connect them together now! As a user interface i was thinking that the user adds a property record, then goes to a image upload page. This image upload page has a drop down menu that pulls down all the property record titles. Once one is selected the image is uploaded to the images table with the ID of the property. Therefore when i pull down the property record with an image file of the same id things would work beautifully...however...

    ive just realised that, based on the original script

    PHP Code:
    $query_Recordset1 "SELECT * FROM props, gallery_photos WHERE gallery_photos.photo_category = props.propID"
    that if theres no image, then the property record doesnt show at all!!

    I dont suppose you could suggest the correct structure for the 2 tables and propose the best method to bring them together in a usable recodset


    many many thanks...sorry for the neccessity for hand holding but im a complete newbie whos developing a nose bleed by thinking too much!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    structure is fine, all you need is to use a LEFT OUTER JOIN with an ON clause, instead of what you have now (comma list of tables in the FROM clause with the join condition in the WHERE clause)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •