SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
-
Feb 20, 2005, 11:30 #1
- Join Date
- Dec 2004
- Location
- San Francisco
- Posts
- 263
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...
-
Feb 20, 2005, 11:46 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Feb 20, 2005, 12:31 #3
- 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
-
Feb 20, 2005, 12:48 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Feb 20, 2005, 17:55 #5
- 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?
-
Feb 20, 2005, 18:06 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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'
-
Feb 20, 2005, 22:23 #7
- 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....
-
Feb 21, 2005, 00:04 #8
- 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.
-
Feb 21, 2005, 01:02 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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'
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'
-
Feb 21, 2005, 21:42 #10
- 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.
-
Feb 21, 2005, 21:56 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
Bookmarks