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

[dharamgollapudi]

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...

[r937]

show your query for joining institutions to degrees, and i'll show you how to extend it to a third table, cipcode

[dharamgollapudi]

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

[r937]

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

[dharamgollapudi]

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?

[r937]

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

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

[dharamgollapudi]

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....

[dharamgollapudi]

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.

[r937]

use single quotes to delimit your strings, not doublequotes

if the string contains a single quote, you must code two single quotes in succession --

a.level = 'Associate''s Degree'

you're gettin the error in your last query because you're not using AND correctly

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

[dharamgollapudi]

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.

[r937]

sorry, i don't do php

if you want to figure out what mysql error it is, run the query outside of php