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!

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.

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:



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?

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.

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?

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

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

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

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

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.

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.

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!