[SOLVED] Please add constructive feedback!

[Barand]

Is this on track?

 

Code:

SELECT Characteristic, SUM(student_score.value)

FROM value student_score

    INNER JOIN question q ON v.q_id = q.q_id

    INNER JOIN section s ON s.section_id = q.section_id

WHERE v.rubric = 'midterm' AND v.student_id = '7001234'

GROUP BY s.section_name

 

Table name, you can only select column names

Not a table = FROM must be table names

v is an undefined alias

not a valid column name

You must group by a selected column

 

 

[odisey]

So - Have the skeleton site built now.  Registration with email admin approval (activation), regular and admin user access levels, change and retrieve passcode.  Now I am thinking about inserting records.  I've studied the tables you suggested and the logic of the inner join to retrieve data.  I wrote a inner join as well from what I've learned. 

 

SELECT student.studentname, question.question, score.description, SUM(student_score.score) as total

FROM student_score

    INNER JOIN question ON student_score.q_id = question.q_id

    INNER JOIN area ON question.area_id = area.area_id

    INNER JOIN characteristics ON area.char_id = characteristics.char_id

    INNER JOIN student ON student_score.student_id = student.student_id

    INNER JOIN score ON student_score.description_id = score.description_id

 

WHERE student_score.mid_final = 'M'

    AND student_score.student_id = '7001234'

    AND student_score.year = '2006'

GROUP BY student.studentname, question.question

ORDER BY total DESC

 

There is probably some rubbish I can weed out of there - what is important is I added an id the the score table giving each score and description a unique id - the join I wrote retrieves the records and displays them in an order that allerts the evaluator to weak and strong areas the student has in order , and etc.

 

Now I am thinking about:

 

1) Creating a student record - Name, bio, student number (student number = student_id). I need to populate the student_info table AND the student_score table with the student_id.  All other tables have a set field value ids that are accessed and associated with joins (ie score has set ids of 1 - 5, questions have set ids 1 - 20, etc.)  The question record ids are auto_increment. The only new ids I need to insert are of course the student ids and they need to be inserted into two tables with one query - on the student registration.  Is this possable?

 

2) Inserting scores from a drop down form.  If the evaluator selects NA for question one for example the db is populated with INSERT INTO student_score (score, description_id) VALUES ("$score","$des_id") WHERE student_id = "$student_id" AND question = "$question";

 

Can I insert all this data with ONE drop down selection?  SO I am deriving my values ("$score","$des_id") from the drop down - which may be the 'key' and 'value' - if they are the 'key' and 'value' - are both these array values passable to (score, description_id)?       

 

 

[odisey]

 

 

Can I insert all this data with ONE drop down selection?  SO I am deriving my values ("$score","$des_id") from the drop down - which may be the 'key' and 'value' - if they are the 'key' and 'value' - are both these array values passable to (score, description_id)?       

 

 

 

I've solved it with some friend's help:

 

I can get the question number, score, and description id this way:

 

 

<select name="question">

    <option value="15~0~1">Not Applicable</option>

    <option value="15~0~2">Unsatisfactory</option>

    <option value="15~1~3">Basic</option>

    <option value="15~3~4">Proficient</option>

    <option value="15~5~5">Distinguished</option>

</select>

 

 

$choice = explode ('~', $_POST['question'];

$qu = $choice[0];

$sc = $choice[1];

$de = $choice[2];

[Barand]

I was confused by your previous post. Now I'm even more confused about why that is necessary.

[odisey]

That's not good - confusion. 

 

The idea is to populate a student_score table row with one drop down selection option.  This populates the question_id, score, and description_id (this is something I added) with one submit.