Vary the length of output from a mysql table

Beginners - Learning PHP
#1

I made a form to show the correct answers for homework after the homework is finished.

The student enters his or her course code, like 19BE and the week he or she wants to see, like Week1.

There is a table, 19BEcorrectAnswers.

The table has the columns:

id, questionnr, Week1, Week2, Week3 … Week18

The thing is, each week may have a different number of of entries in each WeekX column.

For example, Week1 has 17 entries, Week2 has 28 entries.

In the column Week1, after row 17 there is nothing, NULL entry.

The rows in the column ‘questionnr’ look like this: Question1, Question2, Question3 … Question28

The table is at present 28 rows deep, but that may change to more, so it can hold more answers.

First, if the form entries are correct, I get:

$result = $pdo->query($sql);

then:

if(isset($_POST['weeknr'])) {
foreach ($result as $row)
{
$numname[] = array(
'questionnr' => $row['questionnr'], 'weeknr' => $row[$weeknr],);
}
include 'output.html.php';
exit();
}

This does what I want. I get a nice 2 column table with the questionnr and the answer in each row.

It is not a big problem, but, if I query Week1, I still get 28 rows of output.

Can you think of an easy way to suppress the output if the entry in say, column Week1 is NULL, so that Week1 only shows 17 rows??

Thanks for any tips or advice!

#2

Well, I would assume you may get up to the full 28 rows. But, you want to not show any if there are nulls.
You would handle that in your query. Just add a WHERE to it to use “NOT NULL” and you would only get
the rows that have data. You might want to show us the query. OR, if you want to leave the query as-is,
you can check all the rows starting at the highest and working down to see if there is any live data in them.
Once you find a row that has data, keep that number and do a FOR instead of a FOREACH.
Either way should work. Good luck!

1 Like
#3

I think it would be better to design your database tables differently.
You could continue to use a different table for each course.

Then in each course’s table, set your columns as ‘week’, ‘question’, and ‘answer’

So to add a question for week three, you’d enter week=3, question=“Why did the chicken cross the road?”, answer=“To get to the other side.”

Then when you query you can just say
SELECT question, answer FROM class_table_name WHERE week = 3
(or whichever week)

1 Like
#4

Thank you both for the replies!

@ tbeckett1211: good advice, and thanks, but often there is no actual written question. The students are listening to an mp3 and filling in gaps in a text, or listening to an mp3 then answering spoken questions. They can always open the webpage to see what the context is. So I just display the question number and the correct answer. Tomorrow’s homework will be choose the right word from radio buttons. No question really.

@ErnieAlex: Great! worked perfectly, thank you!!

$sql = 'SELECT questionnr, ' . $weeknr . ' FROM ' . $class . 'correctAnswers WHERE ' . $weeknr . '!= \'\';';

#5

Glad to help… Always nice to solve a programming problem, right!?!

#6

Bad advice. This breaks normalization without cause.

#7

@astonecipher: What does that mean?

#8

There are reasons to denormalize a database for a few reasons, your use case isn’t.

Creating a table for each course turns into a nightmare to maintain and query. If you were to add 5 new courses you shouldn’t create 5 new tables. What happens if you have a short lived course that is only done once? Or a hundred different courses?

The proper design is a many to many relationship, where many students can enroll in many courses. That means there is a table for the students, a table with the course details, and a joining table that relates students to those courses. The only variation of this design is the same course offered many times, like a university does. Then you would have a parent course table and a detail course table; one would be the overall descriptors like name and description, the other would hold things like term, dates, and instructor with a foreign key back to the parent table.

1 Like
#9

@astonecipher

Haha, I think we have a question of scale here:

" What happens if you have a short lived course that is only done once? Or a hundred different courses?"

You are, by your title, an Enterprise Architect, maybe you learned this stuff at university, maybe you deal with large companies and enormous databanks. I am just a hack English teacher who studied German. All I know about computing is what I can glean from the Internet.

If I had more than 3 courses, or more than 350 students, I would definitely feel underpaid!! At the moment, I have 3 classes, 142 students, 4 new classes coming next month, plus an elective course, probably small, in all maybe 350 students, 3 courses, 8 classes. My requirements are therefore modest.

Dealing with the databanks of a university is for people like you. I could not do it!

I’m sure what you say is correct, but I, at the moment, do not know how to implement that, so I keep it simple for my simple mind.

Next up: learn what a Foreign Key is.

#10 
create database Course_Example;
use Course_Example;


create table course (
	id int auto_increment primary key,
    `name` varchar(50) not null,
    description varchar(500)
) engine= InnoDB;

create table course_details (
	id int auto_increment primary key,
    course_id int not null,
    date_start date,
    date_end date,
    foreign key(course_id) references course (id) 
) engine= InnoDB;

create table student (
	id int auto_increment primary key,
    first_name varchar(20) not null,
    last_name varchar(20) not null
) engine=InnoDB;

create table courses_students (
	id int auto_increment primary key,
    course_detail_id int not null,
    student_id int not null,
    final_grade int null,
    paid bool default true,
    foreign key(course_detail_id) references course (id) ,
    foreign key(student_id) references student (id) 
) engine= InnoDB;


insert into course (`name`, description) values
('English 101', 'English Description'),
('Conversational German', 'German Description'), 
('French for Business', 'French Description'),
('Web Programming for Beginners', 'Web Programming for Beginners Description');

insert into course_details (course_id, date_start, date_end) values
(1, '2020-08-24','2020-12-06'),
(1, '2021-01-04','2021-04-18'),
(2, '2020-08-24','2020-12-06'),
(2, '2021-01-04','2021-04-18'),
(3, '2020-08-24','2020-12-06'),
(3, '2021-01-04','2021-04-18'),
(4, '2021-01-04','2021-04-18');

insert into student (first_name, last_name) values
('Larry', 'Martin'),
('Jane', 'Goodall'),
('Patrick', 'Flannery'),
('John', 'Doe'),
('Jane', 'Doe');

insert into courses_students (course_detail_id, student_id, final_grade) values
(1,1,0),
(1,2,0),
(1,3,0),
(2,1,0),
(4,4,0),
(3,2,0);

This is the example structure

#11

Thank you very much!

I will try this out, try ti get the hang of it!

Sponsor our Newsletter | Privacy Policy | Terms of Service