SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL - Need help with code for retreiving data from multiple tables

    Hi there!!!
    I am a kind of newbie in programming. My environment is Apache, MySQL and PHP. I have created the following tables in MySQL:

    Table1-INSTITUTIONS //Table that holds information about an institution
    id(Field, Primary Key) //Unique ID for an institution(ex:100251)
    name(Field) //Name of the Institution(ex:Harvard University)
    city(Field) //City of the Institution(ex:Cambridge)
    state(Field) //State of the Institution(ex:MA)

    Table2-DEGREES (lookup table)
    insid(Field) //Unique ID for an institution(ex:100251)
    cipcode(Field) //CIP Code of the Degree(ex: 11.0101)
    awalevel(Field) //Degree Level (ex:3, which corresponds to Associates)

    Table3-CIPCODE
    id(Field, Primary Key) //Unique ID for a Degree(ex:11.0101)
    cipfamily(Field) //CIP Family of the Degree(ex: 11)
    label(Field) //Description of the Degree (ex: Computer and Information Sciences, General)

    Table4-AWALEVEL
    id(Field, Primary key) //Unique ID for a Degree Level (ex:3)
    label(Field) //Label of the Degree Level (ex: Associates)
    description(Field) // Description fot he Degree Level

    Table5-CIPFAMILY
    id(Field, Primary Key) //Unique ID for CIPFamily (ex:11)
    label(Field) //Lable of the CipFamily (Ex: Computer and Information Sciences and Support Services)

    Here is my question (query): If I want to find out the name, city and state of all the institutions that offer Computer and Information Sciences, General at the Associates Level, what would be my query? I am a bit familiar with retreiving information between two tables, but here as there are more than two tables, I am not familiar with the code. Any help would be appreciated...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    show your query for joining institutions to degrees, and i'll show you how to extend it to a third table, cipcode
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am assuming that the following should be code to retreive the data,

    SELECT institutions.name, institutions.city, institutions.state
    FROM institutions, degrees, cipcode, awalevel, cipfamily
    WHERE awalevel="Associates", cipcode.label="Computer and Information Sciences, General", awalevel.id=degrees.awalevel, cipcode.id=degrees.cipcode AND degrees.insid=institutions.id

    but it is giving me the following warning,
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Server\Apache2\htdocs\univsearch\extras\universities_3.php on line 30

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that query attempts (incorrectly) to join 5 tables

    i was willing to help you extend the join from 2 (which i asked you to provide) to 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy-

    The following query should be able to retreive Name, City and State of all the institutions which offer degree type Associates as I know that 3 corresponds to Associates from AWALEVEL table and I also included how the INSTITUTIONS and DEGREES tables are related. (I beleive this is what you asked me)

    SELECT name, city, state
    FROM institutions, degrees
    WHERE awalevel="3" AND institutions.id=degrees.insid

    But I even want the query to find out that if the user selected Associates, it should be able to know awalevel as 3 from the AWALEVEL table. Help would be appreciated.
    Also Hoe many tables can we join at a max in a query?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let's start with that query, extend it to the awalevel table, and make some minor changes --

    1. always qualify every column (i prefer to use table aliases instead of table names)

    2. use JOIN syntax

    3. place the search conditions into the ON clause
    Code:
    select I.name
         , I.city
         , I.state
      from institutionsas I
    inner
      join degrees as D
        on I.id = D.insid
    inner
      join awalevel as A
        on D.awalevel = A.id
       and A.level = 'Associate'
    then extending the query to a 4th table should follow the same pattern
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy-

    Seems like it is working as I was able to retreive data, I just need to work on it and verify the data if it is accurate or not. BTW I am having trouble with apostrophe, as a havel a.level = "Associate's Degree", MySQL as well as PHP are not interpreting it as apostrophe, they are treating it as t_string and giving error message. Hoe do you think I can supress that error as I have apostrophe in various fields....Thanks in advance....

  8. #8
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy-
    When I tried to extend the QUERY to the 4th table "CIPCODE" with the following code:

    select I.name
    , I.city
    , I.state
    from institutions as I
    inner
    join degrees as D
    on I.id = D.insid
    inner
    join awalevel as A
    on D.awalevel = A.id
    inner
    join cipcode as C
    on D.cipcode = C.id,
    A.label = "Postbaccalaureate certificate" and C.label = "Gerontology"

    I am getting the warning message. Can you please check the code for errors. Also can you please let me know where I can learn more about join functions (inner). Thanks for your continious cooperation.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use single quotes to delimit your strings, not doublequotes

    if the string contains a single quote, you must code two single quotes in succession --
    Code:
    a.level = 'Associate''s Degree'
    you're gettin the error in your last query because you're not using AND correctly
    Code:
    select I.name
         , I.city
         , I.state
      from institutionsas I
    inner
      join degrees as D
        on I.id = D.insid
    inner
      join awalevel as A
        on D.awalevel = A.id
       and A.level = 'Postbaccalaureate certificate'
    inner 
      join cipcode as C
        on D.cipcode = C.id
       and C.label = 'Gerontology'
    you can learn more about JOIN in any sql tutorial
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy-

    It is still giving me the following Warning:

    mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Server\Apache2\htdocs\testing\universities_5.php on line 43

    where line 43 is the following:

    while ($data = mysql_fetch_array($results))

    Interesting thing is, its working fine when I just did SELECT name, city, state from institutions where state="fl" but when I modified my select statement with the one which you supplied, it is giving me the error.

    Help would be appreciated.

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

    if you want to figure out what mysql error it is, run the query outside of php
    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
  •