Hi there!

I'm still a beginner coding in both PHP and SQL (theoretical stuff mostly) and I was hoping someone could possibly help or nudge me in the right direction with the 'application' I'm trying to create.

So basically I'd like to upload a CSV file and enter the information into a MySQL database.

This is my PHP code for reading and creating the SQL to insert:

There are 50 unit, mark, grade, period_studied fields (each), however only 2 units need to be entered and the rest (3-50) can be null.

I'm trying to enter some test information (through CSV) like below:

I've been able to upload the file and 'read it' but get this error when trying to insert it into the database.


I've searched around a bit and have found out that I may not have enough values to be entered. Could it be that even though the following commas have been added that the database does not like this? Would updating the way the data is entered or the insert sql line help overcome this?

Any help would be greatly appreciated. Thanks.

Are you trying to put everything in a single table? If so, why?

Yep, I'm putting it in one table - probably not the best design but I'm not sure how exactly I'm going to get all this information linking to one particular student through several tables.

I'm doing it to try to make a kind of reporting system. Like these students received the highest score in this unit etc.

I've got it down to the following tables: student (which is what I'm trying to put the above information into), course and unit.

If you've got a better design suggestion for the structure, I'd love to hear it. It'd be good to get other people's views. =)

Start by thinking of a single entity -- student. Now think of all the things you want to know about a student. For example, you might want to know a student's name, grade point average (GPA), commencement date, courses taken, assignments, grades, and other things.

For each of those things, consider whether the student can be related to one or many of that item. A student might have only one name, GPA, and commencement date; so these attributes can be stored in the same table. A student has many courses, so you can think of a course as a separate entity. Each entity should have its own table.

You could store everything in the same table, but one point of a relational database like MySQL is to reduce redundancy. In a single-table structure, if you have two students who take the same class, the name of that class will be stored twice. If you change the name of the class for one student, you will have to change it for the other. This is not only tedious; it can lead to data inconsistency.

Now consider the relationship between students and courses. A student can have many courses, but also a course can have many students. When you have a many-to-many relationship like this, there are actually three entities involved: students, courses, and the relationship between them. This means you need three tables: student, course, and student_course. If students and courses are each identified by a unique ID, the student_course table would store rows that contain only two fields: student_id and course_id. That way, you turn the many-to-many relationship into two one-to-many relationships. You want your tables to have one-to-many relationships.

So, come up with lists of attributes that describe each of your entities and then I will help you build queries to link them together.

Hi, thanks for you help on this, I really appreciate it. I think I'm getting what you're saying.

So the information that I want would be grouped something like below then?:

Student

• student_id [PK]
• course_code [FK] (student can only have one course)
• GPA
• commencement_date
• process_category
• grade_id [FK] (student can have many grades)

Course

• course_code [PK]
• course_name
• degree_type

Unit

• unit_id [PK]
• unit_name

Grade_Record

• grade_id [PK]
• student_id [FK]
• unit_id [FK]
• final_mark
• final_grade
• period_studied

I think maybe I got a little confused with this because .. the student could have many units but I wasn't sure how I'd store that into the one db field.

Your tables are looking good.

The Grade_Record table takes care of linking the Student and Unit tables.

I think it's a little unusual that a student would have only one course, but I can deal with that. Your structure provides a way to relate Course and Unit.

Some courses (like language or math) could be shared among different degrees, so you might reconsider whether degree_type belongs in the Course table.

When you use a foreign key, the key from the "one" side of the relationship is stored as a foreign key in the "many" table, but the "many" key is not stored in "one" table. That means there should be no grade_id in the Student table.

Can a student have more than one grade per unit? If yes, then you need the grade_id field in the Grade_Record table. Otherwise, if a student can have only one grade per unit, you can use student_id and unit_id together as the primary key. Your database server considers a table's primary key when you try to insert a record (row) into that table. If Grade_Record's primary key is grade_id, you are allowed to insert two separate rows with the same student_id and unit_id (two grades for the same unit for the same student). If your primary key is student_id + unit_id, you can insert only one grade per unit per student.

Can the final_mark be calculated from the final_grade? I am assuming final_grade is something like "90%" and final_mark is something like "A". The problem with having both fields in your database is that if you change "90%" to "80%", you also have to change "A" to "B". You could trade storage for processing and convert "90%" to "A" in your program. This solution introduces a different problem: all programs that access the database have to agree on how to convert final_grade to final_mark.

Student

• student_id [PK]
• course_code [FK]
• GPA
• commencement_date
• process_category
• (Removed grade_id)

Course

• course_code [PK]
• course_name
• degree_type (Is a course common to multiple degrees?)

Unit

• unit_id [PK]
• unit_name

Grade_Record

• student_id [FK][PK] (You can use multiple fields as one primary key.)
• unit_id [FK][PK]
• final_mark (Can this be calculated from final_grade?)
• final_grade
• period_studied

Look this over. Make changes. Tell me where I'm wrong...

Do you have control over the data in your CSV file or is it from an external source?

Do you have phpMyAdmin installed on your server?

Ahh, course in this context would be like degree eg. Bachelor of Business and Commerce or (a double degree) the Bachelor of Information Technology/Bachelor of Business and Commerce or the Master of Accounting.

Unit in this context would be the subjects studied. A student can only study one course at a time (and we would only want the currently studied course).

And degree type either undergraduate or postgraduate (degree).

And! you're pretty close with your assumption. final_mark (eg 95/100) calculates the final_grade (eg A or High Distinction (HD)). But you're right in perhaps letting the program handle that instead conversion itself.

A student can only have one type of final_mark or final_grade per unit however a student can study the same unit twice or more (if the student fails). I think this may make a bit of a problem with the grade_record table. Period_studied (eg. Aut/2007 or Spr/2009) and unit_id (together) would never be the same - compared to unit_id and student_id which could be duplicated (studying twice).

Student

• student_id [PK]
• course_code [FK]
• GPA
• commencement_date
• process_category

Course

• course_code [PK]
• course_name
• degree_type (Undergraduate or Postgraduate)

Unit

• unit_id [PK]
• unit_name

Grade_Record

• student_id [FK][PK]
• unit_id [FK][PK]
• final_grade
• period_studied ([FK][PK] too? if we can do three)

I have a limited amount of control over the csv file. I can ask for the data to be in a different order, but overall it would look like this from the original post because this is all of the information that we would need:

and yep! I have phpmyadmin.

Based on your description, I suggest:

Grade_Record

• student_id [FK][PK]
• unit_id [FK][PK]
• period_studied [PK]
• final_grade

Since period_studied is a date-like field, it probably doesn't need its own table. Therefore it's not a foreign key. If you want to collect additional data about each period, like the average outdoor temperature or the total number of students in attendance, then you need an additional table.

You might have problems with the "Spr/2009" format for period_studied. It will be difficult to sort the records by year. If you use a format like "2009-Spr" you will be able to sort the records by year, but it will still be difficult to sort chronologically because "Aut" comes before "Spr" alphabetically. If you want to be able to sort records chronologically or retrieve records for only Spring or only 2009, a better way is to use two fields: year_studied and season_studied (or similar). Another solution is to use the starting month of the period, like "2008-09" or "2009-01".

If you use a points system where each unit is worth a certain number of points and the overall grade for the unit is calculated based on earned points divided by possible points, you may need to split the grade into two fields. Then again, maybe not.

Once you are satisfied with the fields you have selected, you need to decide on data types and other properties for each field. A description of the different data types can be found at http://dev.mysql.com/doc/refman/5.0/en/data-types.html. If you want, you can create your database and decide on data types as you create your tables. phpMyAdmin is helpful with this process.

IDs are usually INT. GPA could be VARCHAR(5) or FLOAT(4,3). Dates are DATE or DATETIME. Names are VARCHAR. Degree type could be VARCHAR, ENUM('Undergraduate', 'Postgraduate'), or ENUM('U', 'P'). Grade could be FLOAT(5,2). It's your database, though, so use the data types that are right for you.

INT IDs commonly have the auto_increment property. When you set up a table in phpMyAdmin, you can find it in the "Extra" drop-down list.

The UNSIGNED attribute means that a number can only be positive. It has no sign. Don't associate the term "unsigned" with a security certificate.

I suppose I should confirm your terminology on another thing. What is process_category in the Student table?

=] Here in Australia, Autumn comes before Spring (as well as alphabetically) so I don't think it'll be a problem. But it might be a good idea to separate the two as you said - year_studied and semester_studied.

process_category is something which will help us identify what kind of student (administration wise) we have - eg international, local, fee paying, scholarship etc. We only want the code which the users will already understand, so its ok to keep this as a single attibute and not expand it into a table.

Thank you for all your help, I'll go ahead and develop the tables and take a look at the coding and update it and let you know if I encounter any difficulties! =]

Hi!

I'm having some trouble when I try to enter a date into the table. This is how I'm putting the information in:


This is the input line:

The query [that is echo'ed] comes out like this:

commencement_date's type in the table is date without a format etc being specified.

But when I look in the phpmyadmin interface the commencement_date is: 0000-00-00. When I edit it through there with 2006/02/20 it saves fine.

Is there something I'm missing/doing wrong to enter the date?

Advanced thanks!