Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Codewalkers ForumsOther TechnologiesDatabase Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
Old August 18th, 2003, 02:33 PM
myraleen myraleen is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: Ottawa, Ontario, Canada
Posts: 123 myraleen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via ICQ to myraleen Send a message via AIM to myraleen
SQL/PHP Multiple query TRICKY

Ok, in my data base, there is a table we will call Education.

Now there is also a bunch of other tables, names, users, codes, codebooks, ect.

Education is the main one.

I have a very complex select box for a search engine.
You can select multiple choices from it.

You can select any or all.

The problem I am facing, is that when selecting ALL, if you select the education selections, it is not working.

I also know why...
Because it is set up to match the code of the selection, as well as the code of the user... both are columns in the education field.
Problem is there can be two rows for one user code, so it confuses with only being able to find one user row, and therefore not finding the other possible education selections that user may have.

I want to set it up so that I do not change the db, only the coding, and I know that somehow I can make it so that the education table can be some sort of an array, like education2 education3 something like that.

So do you think someone has any ideas of where i start?
do i make a completely new search for the specific education?
argh
fustrating this stuff can be!

Reply With Quote
Old August 18th, 2003, 02:59 PM
jaltiere jaltiere is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Posts: 55 jaltiere User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via AIM to jaltiere
RE: SQL/PHP Multiple query TRICKY

Can you tell us the current table structures and the current SQL call that isn't doing what you want it to?

I can probably help you if I have a better idea of the data you are trying to pull out.

Reply With Quote
Old August 18th, 2003, 03:52 PM
myraleen myraleen is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: Ottawa, Ontario, Canada
Posts: 123 myraleen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via ICQ to myraleen Send a message via AIM to myraleen
RE: SQL/PHP Multiple query TRICKY

ok... here ya go

I will just keep it simple as I can, because it's a very complex database and system.

So I'll just let you know the two tables, one with the user information, it's what is used in the search results, one with the codes for the courses and corresponding user id.

USERS
------
UserID | UserName | UserDOB
1 Fred Jan
2 Jim Dec
3 Sarah Feb

COURSES
-------
UserID | CourseCode | Descriptions
1 111 computerSkills
1 112 accounting
2 111 computerSkills
3 111 computerSkills
3 112 accounting

And as for the code, it is all broken up into query and bits... but I'll do my best... the select statement:

php Code:
Original - php Code
  1.  
  2. SELECT DISTINCT users.userID, users.userName, users.userDOB FROM users
  3. LEFT JOIN courses ON users.userID = courses.usersID WHERE courses.courseCode = 111 AND courses.courseCode = 112 ORDER BY users.userID SORT BY asc


The coursecode where statement is also using a reg exp but I reallly didn't want to type all that out.

heh
Does that help clarify?

Reply With Quote
Old August 18th, 2003, 05:33 PM
bakertrg's Avatar
bakertrg bakertrg is offline
Contributing User
Codewalkers Regular (2000 - 2499 posts)
 
Join Date: Apr 2007
Location: Scottsdale AZ, US
Posts: 2,255 bakertrg User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 48 m 45 sec
Reputation Power: 10
Send a message via Yahoo to bakertrg
RE: SQL/PHP Multiple query TRICKY

your error is almost certainly a problem with database normalization. For instance, the table you list as course should not have the student id or not have the course description in it. A normalized database would have the student info in one table(like you have it), the course info id, name, description in another and a third table (maybe called schedule) that puts courses and students together in a 1 to many realtionship. You can tell you have a problem with your data because if you wanted to change the name of a course you would have to do so in multiple locations.

Even though you don't want to redesign the database that is most likely where your problem stems from.

Reply With Quote
Old August 18th, 2003, 05:43 PM
jaltiere jaltiere is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Posts: 55 jaltiere User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via AIM to jaltiere
RE: SQL/PHP Multiple query TRICKY

I would agree with bakertrg, I would design the database the same way.

Here is one way you could lay out the database

USERS
------
UserID
UserName
UserDOB

COURSES
-------
CourseID
CourseCode
Descriptions

SCHEDULE
-------
ScheduleID
CourseID
UserID

You don't need the ScheduleID, but I like having a unique ID in each table

Even though it sounds like it would be a lot of work to change your schema, I think it would make getting the data you need a lot easier in the long run.

One thing I don't understand about your original query....

WHERE courses.courseCode = 111 AND courses.courseCode = 112

how can the coursecode be 111 and 112? maybe this should be an or instead?

Reply With Quote
Old August 18th, 2003, 05:44 PM
myraleen myraleen is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: Ottawa, Ontario, Canada
Posts: 123 myraleen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via ICQ to myraleen Send a message via AIM to myraleen
RE: SQL/PHP Multiple query TRICKY

Because it is such a complex system It is impossible to do that. The user ID is not something that ever changes.
The user table does combine most everything and then the extras are put in their own tables.
The code where it is edu2 doesn't sound familiar to anyone?
I know that it works somehow, I just don't know where to put everything.

this is an example I was given by my coworker:
[highlight=php]
SELECT DISTINCT
education.userID, users.userID, users.userName, users.userDOB
FROM users
LEFT JOIN
education ON users.userID = education.usersID, education education2
WHERE
(
education.usersID = education2.usersID
AND education.usersID = 268
AND education2.usersID = 275
)
ORDER BY users.userID

Reply With Quote
Old August 18th, 2003, 05:45 PM
myraleen myraleen is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: Ottawa, Ontario, Canada
Posts: 123 myraleen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via ICQ to myraleen Send a message via AIM to myraleen
RE: SQL/PHP Multiple query TRICKY

Because it is such a complex system It is impossible to do that. The user ID is not something that ever changes.
The user table does combine most everything and then the extras are put in their own tables.
The code where it is edu2 doesn't sound familiar to anyone?
I know that it works somehow, I just don't know where to put everything.

this is an example I was given by my coworker:
[highlight=php]
SELECT DISTINCT
education.userID, users.userID, users.userName, users.userDOB
FROM users
LEFT JOIN
education ON users.userID = education.usersID, education education2
WHERE
(
education.usersID = education2.usersID
AND education.usersID = 268
AND education2.usersID = 275
)
ORDER BY users.userID

Reply With Quote
Old August 18th, 2003, 05:46 PM
myraleen myraleen is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: Ottawa, Ontario, Canada
Posts: 123 myraleen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via ICQ to myraleen Send a message via AIM to myraleen
RE: SQL/PHP Multiple query TRICKY

I can't change the database, it's a complex system with about 20 different tables already working the system. And my boss would laugh at the thought.
I need a plan B.
ugh!!!!!

Reply With Quote
Old August 18th, 2003, 05:48 PM
myraleen myraleen is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: Ottawa, Ontario, Canada
Posts: 123 myraleen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via ICQ to myraleen Send a message via AIM to myraleen
RE: SQL/PHP Multiple query TRICKY

It's impossible for me to actually show you my real db and work.. but It's got to be an AND because that's what we are doing, looking for all people who took both courses.
THere is also an OR and it works fine

Reply With Quote
Old August 18th, 2003, 05:57 PM
bakertrg's Avatar
bakertrg bakertrg is offline
Contributing User
Codewalkers Regular (2000 - 2499 posts)
 
Join Date: Apr 2007
Location: Scottsdale AZ, US
Posts: 2,255 bakertrg User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 48 m 45 sec
Reputation Power: 10
Send a message via Yahoo to bakertrg
RE: SQL/PHP Multiple query TRICKY

two problems, one, this is never going to return a result since your checking if two unequal things are equal...

education.usersID = education2.usersID
AND education.usersID = 268
AND education2.usersID = 275

second, even if it's a complex system if it's poorly designed you won't always be able to do everything you want it to do.
Quote:
The user table does combine most everything and then the extras are put in their own tables.

this is usualy a recipe for eventual disaster. If your database isn't normalized you will eventually create insertion and deletion anomalies and your data will become, at best, less than optimal and at worst, nearly useless. The larger and more complex the database the more critical it becomes to have one that is properly designed.

the original query you showed should return the cross section of both classes and if that doesn't work you could always pull back all the students who took the first class and make a list to use in a query that looks for those same students in the second class. WHERE student.id IN (id1, id2, id3 etc.) you can make the list easily in a while loop of the first query.

Reply With Quote
Old August 19th, 2003, 01:45 PM
myraleen myraleen is offline
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: Ottawa, Ontario, Canada
Posts: 123 myraleen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Send a message via ICQ to myraleen Send a message via AIM to myraleen
RE: SQL/PHP Multiple query TRICKY

I got it to work my way just had to put a few loops here and there to check the number of courses from the db first.

Thanks!

Reply With Quote
Reply

Viewing: Codewalkers ForumsOther TechnologiesDatabase Help > SQL/PHP Multiple query TRICKY


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap