Search column name, display row


#1

Hi,

I have a table with about 40 columns. I would like to search the column names and if there’s a match with the search, display the rows of that particular column.

I just can’t figure it out, can someone help me?

Let’s say the columns are school classes and the rows are grades, I’d like to search for MATH and display the students’ names and their grade in that class. I hope that makes sense :slight_smile:


#2

Well, it is easy enough to do if we know your data structure. If you mean you have a database, you should just run the correct query. Loosely, something like this:
SELECT grades FROM school_classes WHERE class_name=“MATH”;
This would select all of the grades for the math classes.
You can also add in an ORDER BY clause to sort them high-to-low if needed.

Have you any code to show us how you have been testing this so far?


#3

This is a bad database design, making it almost impossible to find or manipulate data. You need to research database normalization.

The table holding the grades should have one row for each data item, with columns for a unique id (auto-increment integer), school year, class id, student id, and grade. You would also need tables that hold class and student information.


#4

Hey, thanks for your input.
I think I may not have been clear about my intent. The idea is a website with a search option, looking up classes and being shown the grades. As I understand it, you are saying select grades from one class? How would I apply that to a search through multiple classes?


#5

Hi, thanks for your input.
I will look into database normalization but I’m having trouble visualizing what you’re suggesting. Let’s say I have 40 classes per student, would you then do 40 rows for every student?

Student | Class | Grade
Student1 | Math | A
Student1 | History | B
Student1 | Biology | C


#6

Yes, you will have 40 row per student.

Once your data is normalized, you can EASILY and QUICKLY query for the data for any student or students, for any class or classes, and by storing date information too, for any date period or periods. And by using the student and class id’s in the grade table, your design will use the least amount of storage and the queries will execute the fastest.


#7

http://sqlfiddle.com/#!9/9de84f