[4 levels of 'relationality' (is that a word?)]

and if you really cared about actually being solution-oriented you'd read past the stuff that only relates to YOU and maybe offer a pointer in why my query is returning duplicate results.

[4 levels of 'relationality' (is that a word?)]

"because you assumed the client knew what they wanted upfront"

 

a clue might have been found IN MY VERY FIRST SENTENCE OF THIS THREAD, "my first mistake was assuming my client knew what he wanted" or something to that effect.

 

get off your high horse and consider helping people instead of pointing out their faults. there was a brief moment in your life where you, too, didn't know everything on the planet.

[4 levels of 'relationality' (is that a word?)]

thorpe, AS i already said, what he had originally described was different than what it later turned out to be, and was something i WAS able to do. this guy said, "i need my oil changed" and i charged him to change his oil, and when i was halfway through it, he said, "no, what i meant was i need new rings, which means you'd also have to change the oil".

 

why is that hard for you to understand?

 

i did NOT charge him to fix his transmission, i only charged him to do what he originally asked because THAT's what he originally described. so you are WRONG. you're just not willing to admit it.

 

which is why you are a sanctimonious jerk. which sounds like a GOOD place for me to mark this thread solved.

 

i'm going to have to remember how the administrators like to talk to their people in here next time i need some help. damn.

[4 levels of 'relationality' (is that a word?)]

This is the point at which I fail at SQL.  Just as in PHP, there is probably a miniscule error going on in the JOINing of these tables.  I don't think I can be any more help than Google at this point.  :shrugs:

 

's ok, you've given me a GREAT leap forward, that's why i sent the 2nd donation. i'll learn a lot from putting all this together.

 

hopefully i can aspire to be as smart as that other jerk with the sermon.

 

 

)

 

peace, Zane.

[4 levels of 'relationality' (is that a word?)]

This may not be the problem, but it's the only thing that stands out to me.

 

Is uid, in the a_child table, set as a VARCHAR variable?  Why do you have 2 quoted?  And how did the query not fail if you do have it set as INT?

 

u_id is an INT -

 

i had the 2 quoted because i thought it was supposed to be, as an integer - nevertheless, unquoted i still get a duplicate return -

 

i don't know -

 

i shortened the Q. considerably to troubleshoot this:

 

  $result = mysql_query("SELECT c.ch_id, c.name, s.sub_name
FROM a_child c
INNER JOIN a_subjects s ON (c.ch_id = s.ch_id) WHERE c.u_id = 2") or die(mysql_error());

a_subjects: 
sub_id	ch_id	sub_name
1	         1	       English
2	         1	       History

 

STILL getting duplicate child (david, ch_id 1)

[4 levels of 'relationality' (is that a word?)]

major help you've been, Zane - one minor issue:

 

this worked, except that my one test student came back twice:

 

  
	  $result = mysql_query("SELECT c.name, s.sub_name, t.hrs_reqd, ts.hrs_spent, ts.work_desc
FROM a_topic_stuff ts
INNER JOIN a_child c ON (c.ch_id = ts.ch_id)
INNER JOIN a_subjects s ON (s.sub_id = ts.sub_id)
INNER JOIN a_topics t ON (t.t_id = ts.t_id) WHERE c.u_id = '2'") or die(mysql_error());

	  while ($child = mysql_fetch_array($result)) {
		  
		  echo $child['name'].", ";
		  
	  }
	  

 

 

id ch_id sub_id t_id hrs_spent work_desc

1   1         1       1     1               read a book

2   1         1       1     2               read a longer book

 

and i'm thinking this is the reason, but i have to have all my hrs_spent itemized like this - what do i need to change?

 

[4 levels of 'relationality' (is that a word?)]

god, it may not sound it, but i'm a bit beyond INSERT and UPDATE and DELETE - mainly it was just organizing all this data and how it all relates was confusing me. once i see how to lay it all out, i should be all right. i'll end up being comfortable with joins once i do enough of them.

 

thanks

[4 levels of 'relationality' (is that a word?)]

no, i mean they want to KEEP the hours individually - it's like a report, really. they want to see each hour spent, he claims, so i don't want to change it, i just need to keep adding rows and then go get them to display a whole bunch of rows on a page... it should work this way, tho.

 

also, each subject has to be unique to each student because this guy states he wants the parent to be able to edit the name of the subject, so i'm adding the child_id to the subject table...

 

 

an Hours_Spent entry has to be a new record each time, because the purpose of this is to display the individual times, pretty much like the table i uploaded. so the displayed table would show a column of Hours Spent instead of just a total...

For this you would use an UPDATE query

something like,

UPDATE studentTopics stop 
SET stop.hoursSpent = stop.hoursSpent + 1
WHERE ... stop.topic = ? AND stop.student = ?

[4 levels of 'relationality' (is that a word?)]

ok, i have to throw a couple of questions in here:

well, one question:

 

an Hours_Spent entry has to be a new record each time, because the purpose of this is to display the individual times, pretty much like the table i uploaded. so the displayed table would show a column of Hours Spent instead of just a total...

 

is that going to work with the joining table as you defined it? i guess it would as long as the query is done right... right?

 

 

 

 

Alright then.

 

Looks like you'll need to have a joining table for the students' topics instead.

 

You should have a table for :

students

- would contain everything about the STUDENT

subjects

- would simply contain the subject name... and the primary key of course.

topics

- would contain every topic, along with subject id, description, and hours required.

 

Then, for the joining table, you'd have

- primary key

- student id

- subject id

- topic id

- hours spent

 

 

Grabbing all that information would look something like this

SELECT st.studentName, sub.subjectName, top.TopicName, top.hourReq, stop.hoursSpent, top.desc
FROM studentTopics stop
INNER JOIN students st ON (st.studentID = stop.student)
INNER JOIN subjects sub ON (sub.subjectID = stop.subject)
INNER JOIN topics top ON (top.topicID = stop.topic)

Then. you could craft your WHERE statement however you'd like.

For instance if you're looking for all students who have not satisfied the required hours.. you do

WHERE stop.hoursSpent != top.hoursReq

[4 levels of 'relationality' (is that a word?)]

Alright then.

 

Looks like you'll need to have a joining table for the students' topics instead.

 

You should have a table for :

students

- would contain everything about the STUDENT

subjects

- would simply contain the subject name... and the primary key of course.

topics

- would contain every topic, along with subject id, description, and hours required.

 

Then, for the joining table, you'd have

- primary key

- student id

- subject id

- topic id

- hours spent

 

 

Grabbing all that information would look something like this

SELECT st.studentName, sub.subjectName, top.TopicName, top.hourReq, stop.hoursSpent, top.desc
FROM studentTopics stop
INNER JOIN students st ON (st.studentID = stop.student)
INNER JOIN subjects sub ON (sub.subjectID = stop.subject)
INNER JOIN topics top ON (top.topicID = stop.topic)

Then. you could craft your WHERE statement however you'd like.

For instance if you're looking for all students who have not satisfied the required hours.. you do

WHERE stop.hoursSpent != top.hoursReq

 

effin' brilliant! that makes sense... i can handle the WHERE statements, there'll be a few different ones for different parts (mainly displays for the parents. - i didn't mention the PARENTS of these little brats, did i!!! )

 

thanks, i'll say hi to Tony again in a minute.

[4 levels of 'relationality' (is that a word?)]

So Dad has two kids, Bobby and Alice.

 

Alice is assigned 3 Subjects for the year. Bobby is assigned 2:

 

English and Math

 

For English, Dad will begin teaching him a topic that will require 6 hours to complete, and he will enter a 6 Hrs Req'd for Topic1, and he will enter a 1 (Hrs Done) for the first hour they spend on Topic1. He'll enter a 1 for the second hour they spend on Topic1. and so on and on...

 

Later on, he'll begin teaching him Topic2 and he'll enter an 8 hours required for Topic2 (STILL in English!), and he'll enter a 1 when they spend an hour on Topic2, and so on and on…

 

[4 levels of 'relationality' (is that a word?)]

an image of the layout is attached. the numbers of all the rows is arbitrary...

[4 levels of 'relationality' (is that a word?)]

Ok, so from what I understand, every subject has a set amount of sessions, right?  And each session has a set amount of hours required?

It seems the real dilemma here is keeping track of the days, which I assume will just be stored as a UNIX timestamp or MySQL DATE formatted field.

 

It seems to me that you would need a joining table that includes

- the primary key id of the table

- the id of the session

- the id of the subject

- the id of the student

- the datetime of hours earned for said session

- the number of hours earned for the said datetime..

 

I'm nothing close to an SQL expert so I'm pretty much just listing things right now.

 

Am I on the right track here?

 

not a set amount - it's arbitrary all down the line. can i upload an image? i've laid it out...

 

[4 levels of 'relationality' (is that a word?)]

First off, you need your two main tables for:

- students    // This will include students' name, id, address, hours done, etc...

- subjects    //  This will include the hours required, id, name, description, etc..

 

From the ids of these two tables you will create a joining table for whatever you need joined.  So the next table would be for the subjects each student has.

  - studentsSubjects  // This will have a field for tableID, student ID, subject ID

 

As far as

each subtopic/hours required will have many hours done and description (of "hours done") assigned to it.

I have no idea what you need...  what is a subtopic?

 

ok, let me try a better description: the HOURS DONE doesn't apply to the student... let's try it this way:

 

BOB : ENGLISH : SESSION1 (hrs_reqd 6) : DAY1 (hrs_done 1) : DAY2 (hrs_done 1) : DAY3 (hrs_done 2) ...

BOB : ENGLISH : SESSION2 (hrs_reqd 9) : DAY1 (hrs_done 2) : DAY2 (hrs_done 0) : DAY3 (hrs_done 1) ...

BOB : ENGLISH : SESSION3 (hrs_reqd 12) : DAY1 (hrs_done 2) : DAY2 (hrs_done 0) : DAY3 (hrs_done 1) ...

 

BOB : GEOGRAPHY: SESSION1 (hrs_reqd 4) : DAY1 (hrs_done 2) : DAY2 (hrs_done 0) : DAY3 (hrs_done 1) ...

 

 

i know that i'm assigning id's to each entity, STUDENT, SUBJECT, etc, the part i have trouble with is associating each with the other. i can get ALL the SUBJECTS that belong to STUDENT, and i could work out a JOIN to get ALL the SESSIONS for each SUBJECT for each STUDENT, but that's about as deep as i can go.

[4 levels of 'relationality' (is that a word?)]

Not to be harsh but I would suggest that your first mistake was taking on a client when you quite obviously are missing some basic database design and implementation skills. Joins are not difficult and in avoiding them because you think they are you are selling your client an inferior product.

 

You have already described the data relationships, where exactly are you stuck on implementation?

 

and i didn't say i was 'avoiding' them, i've used them before, in fact, but i'm just not good with them.

 

you might note how your friend here was quite a bit more constructive (helpful) in his post which will surely be helpful for ANYONE who reads this thread, unlike this post.

[4 levels of 'relationality' (is that a word?)]

first, thanks for your help instead of sanctimony. now i'll go read your post and learn something.

 

(right, i just stuck subtopic in there as some sort of identifier) the subject is broken down into sessions, or 'sub_subjects', and each 'sub_subject' is then broken down into hours.

 

First off, you need your two main tables for:

- students    // This will include students' name, id, address, hours done, etc...

- subjects    //  This will include the hours required, id, name, description, etc..

 

From the ids of these two tables you will create a joining table for whatever you need joined.  So the next table would be for the subjects each student has.

  - studentsSubjects  // This will have a field for tableID, student ID, subject ID

 

As far as

each subtopic/hours required will have many hours done and description (of "hours done") assigned to it.

I have no idea what you need...  what is a subtopic?

[4 levels of 'relationality' (is that a word?)]

in fact i wasn't doing him a disservice if the data he required was AS he described - i wouldn't have needed any joins. so i appreciate it, but i care about my clients enough to know when i'm not qualified. for his original description, i AM qualified, thanks.

 

perhaps not everyone is as, oh, nevermind.

 

i didn't need joins until it became obvious he hadn't exactly presented the facts. so NOW i'm trying to get him taken care of.

 

thanks.

[4 levels of 'relationality' (is that a word?)]

ok, my first mistake was assuming my client knew what the heck he wanted. he had described a database to store a 'student', many 'subjects' for a student, with the fields 'date', 'hrs required', 'hours used', and 'description' for each subject. so i built it. easy, right?

 

ha.

 

because i was unable to read his mind, i wasn't aware that this data ACTUALLY needs to be related as the following, and I NEED HELP. ) please...

 

student will have many subjects .

 

subject will have many hours required (subtopic?) assigned to it.

 

each subtopic/hours required will have many hours done and description (of "hours done") assigned to it.

 

 

i was tempted to just throw the hours done into an array for each hours required, but then looking up the ladder my brain gave up and began whimpering like Al Gore, so i'm HUMBLY asking for some help/advice on how to build these tables, if i can get away with using arrays (a LOT easier for me than JOINS all over the place, which means i'd be asking for help with those next -- ARRGGH!) or not.

 

can someone help a guy out? i'd really appreciate it.

 

GN

[help writing a (somewhat) complex query...]

yes, that fixed it, thanks so much - i had tried adding field names but obviously not the correct combination. i'll read over your notes, thanks again

[help writing a (somewhat) complex query...]

" Unknown column 'users_users_projects' in 'on clause' "

 

(users_users_projects is correct, all these tables have a prefix "users_")

 

this can NOT be this hard. i don't know why i can't understand it...

[help writing a (somewhat) complex query...]

this is what i want to end up with for a project 123:

 

<input chckbx nme=user[] value="18" />18

<input chckbx nme=user[] value="19" />19

<input chckbx nme=user[] value="20" />20

<input chckbx nme=user[] value="21" "checked" />21

 

that's all.

 

[help writing a (somewhat) complex query...]

well, i'm just showing the list of users as checkboxes for a given project, so all i need is the list of ALL users <checkboxes> (as defined in users_users) and the ones that are already assoc. with the project (as defiined in users_users_rfpb) would be ticked, leaving the option for admin to tick others that are unticked (the ones that are NOT in users_users_rfpb).

 

i'll go work on what you just sent. thanks, so far... )

[help writing a (somewhat) complex query...]

ok, let's try this:

 

table 'users_users' has many users: 18, 19, 20 and 21

 

i'm printing

 

<input chckbx nme value="18" />18

<input chckbx nme value="19" />19

<input chckbx nme value="20" />20

<input chckbx nme value="21" />21

 

table 'users_users_rfpb' contains records:

user_id | rfpb_id

  20      |  ABC...

  21      |  123

 

while i'm showing all the users for rfpb "123", i really want to print

<input chckbx nme value="18" />18

<input chckbx nme value="19" />19

<input chckbx nme value="20" />20

<input chckbx nme value="21" "checked" />21

 

sorry again. i really appreciate the help -

 

 

 

[help writing a (somewhat) complex query...]

i played with this a while, got empty checkboxes OR duplicate checkboxes with some checked, some not.

 

ok, i'm guilty of very poor explanation, i s'pose. let me describe mo' better:

 

RE rfpb_id: "123" ($rfpb_id)

 

'users_users'.'userid' - i need all of them.

 

'users_users_rfpb'.'user_id' and 'users_users_rfpb'.'rfpb_id'

 

while returning a checkbox for EACH userid, if userid exists in 'users_users_rfpb' with $rfpb_id i want to check the box.

 

sorry for my ambiguity. thanks for your help... big time...

[help writing a (somewhat) complex query...]

thanks, Kieth - i'll go work with this and tell you of my success. thanks