Please, I would like to arrange rows from mysql tables into table headers and table data in html table. Most especially subjects and subjects scores

Normalization. Don’t arrange subject in column

id student_id subject_id class_id term_id score

For example 12 subject would need 12 different rows for storage for a specific student in a specific class in a specific term .

So every term 12 row each. For three terms it means 32 rows per student

The average is calculated after you fetch from SQL query so as any other calculation

goldmines214:
Please, I would like to arrange rows from mysql tables into columns. Most especially subjects and subjects scores

check how to transpose row into column using MySQL on stack overflow.

stanliwise:
Normalization. Don’t arrange subject in column

id student_id subject_id class_id term_id score

For example 12 subject would need 12 different rows for storage for a specific student in a specific class in a specific term .

So every term 12 row each. For three terms it means 32 rows per student

The average is calculated after you fetch from SQL query so as any other calculation

I am not arranging the subjects in columns. I arranged them in rows like you did if you look at the broadsheet table. The last table (required result) is the html table. That's how I want it to appear on the browser when viewed. So please how do I achieve that from mysql tables (students, subjects, class, sessions, terms, broadsheet) onto html tables like required result image above?

efelico:


check how to transpose row into column using MySQL on stack overflow.

My tables are quite different and I am trying to display that on an html table like required result image above using the other mysql tables to do that. I need help on the mysql and php code to achieve this

goldmines214:



My tables are quick different and I am trying to display that on an html table like required result image above using the other mysql tables to do that. I need help on the mysql and php code to achieve this

Check this link
https://stackoverflow.com/questions/1241178/mysql-rows-to-columns
The concept is the same. You can either do the transposition using your query or php code. I guess using query will be more easier for you

efelico:


Check this link
https://stackoverflow.com/questions/1241178/mysql-rows-to-columns
The concept is the same. You can either do the transposition using your query or php code. I guess using query will be more easier for you

I really, do not understand how I can apply this to give me my desired results. I have 6 tables to select from and in the example from stackoverflow.com it's just 1 table. Can you use my tables and write a query and display it in html table please. I would really appreciate. I have been stucked here for months

goldmines214:



I really, do not understand how I can apply this to give me my desired results. I have 6 tables to select from and in the example from stackoverflow.com it's just 1 table. Can you use my tables and write a query and display it in html table please. I would really appreciate. I have been stucked here for months

Send me a pm

efelico:


Send me a pm

Please, what are the steps to send you a private message?
I can't see any section like chat after clicking on your name. I just registered yesterday.

goldmines214:


Please, what are the steps to send you a private message?
I can't see any section like chat after clicking on your name. I just registered yesterday.

Have replied your mail

The OP's question is not clear.

You've arranged your database, now what you need is the code to create the database and store and retrieve data from it using PHP and (my)SQL, right?

I recommend using w3schools.com. Go to the PHP category, scroll down to MySQL section, you'll see some examples that'll get you started.

goldmines214:



I am not arranging the subjects in columns. I arranged them in rows like you did if you look at the broadsheet table. The last table (required result) is the html table. That's how I want it to appear on the browser when viewed. So please how do I achieve that from mysql tables (students, subjects, class, sessions, terms, broadsheet) onto html tables like required result image above?

Run sql queries and simply get the data. Store them in a variable. Arrange them how you want with a loop.
Use html table tag to arrange it.
Is anything hard there?

Google it. you will see php source codes on how to fetch or get (echo) data from database.
Or you pay me make i do am for you.

goldmines214:
Please, I would like to arrange rows from mysql tables into table headers and table data in html table. Most especially subjects and subjects scores

Hi. I believe what you want is the final table diagram you drew. Here is how you can do it.

1. Your broadsheet table is linked to some other tables, and to get those field values from the other tables - you will need to use a JOIN . You would probably use a LEFT JOIN, or a RIGHT JOIN.

2. Now you would want a situation where all the students, and all the subjects will appear on your HTML table regardless of whether they have scores or not. To achieve this, I would first run a query on the subjects table to get the subject names and their IDs. I would then store this is an associative array. With this I have the data to generate my table headers.

Digest this and let me know if you understand this. We can then proceed to the next stage.

naijasensei:


Hi. I believe what you want is the final table diagram you drew. Here is how you can do it.

1. Your broadsheet table is linked to some other tables, and to get those field values from the other tables - you will need to use a JOIN . You would probably use a LEFT JOIN, or a RIGHT JOIN.

2. Now you would want a situation where all the students, and all the subjects will appear on your HTML table regardless of whether they have scores or not. To achieve this, I would first run a query on the subjects table to get the subject names and their IDs. I would then store this is an associative array. With this I have the data to generate my table headers.

Digest this and let me know if you understand this. We can then proceed to the next stage.

I can do that quite well. I know how to fetch data from databases and display them but arranging those scores under the specified subjects is the issue. Assuming the class is jss1, session is 2020 /2021 and term is third term how do I use these with the broadsheet table and with students and subjects tables to display all students, subjects and scores for subjects in jss1, third term and 2020/2021 session?

Whatshallido:
Google it. you will see php source codes on how to fetch or get (echo) data from database.
Or you pay me make i do am for you.

I know how to do that but what I want to achieve is more of logic than just fetching and displaying

stanliwise:

Run sql queries and simply get the data. Store them in a variable. Arrange them how you want with a loop.
Use html table tag to arrange it.
Is anything hard there?

Look at the images, the last image(required result) is what I want displaying on my browser as html table while the other images are the database tables

Karleb:
The OP's question is not clear.

You've arranged your database, now what you need is the code to create the database and store and retrieve data from it using PHP and (my)SQL, right?

I recommend using w3schools.com. Go to the PHP category, scroll down to MySQL section, you'll see some examples that'll get you started.

What I want is the correct query and php code to achieve the required result as in the image above

efelico:


Have replied your mail

Thanks bros. I have replied it please check.

goldmines214:


I can do that quite well. I know how to fetch data from databases and display them but arranging those scores under the specified subjects is the issue. Assuming the class is jss1, session is 2020 /2021 and term is third term how do I use these with the broadsheet table and with students and subjects tables to display all students, subjects and scores for subjects in jss1, third term and 2020/2021 session?

Excellent!
As I said, first fetch the subject names and subject IDs from the subjects table. Store this data in an iterable object - like an array (associative array, or hash map).
Now, write a query to fetch the data (from a specific class, term, and session) from the broadsheet table, and use a RIGHT JOIN to join the students table, then use GROUP BY to group the results by student_id.
You will now have a data set that contains the students with their respective subject scores. You can now iterate through the data by using the student_id parameter. You can build your table like this:

foreach (studIDs as id) {
  // Create a table row
  foreach (subjIDs as subj) {
      //Table cell begin
     //Check if there is a score for the subject
     //If there isn't, skip to the next subject
     //use a variable to count the subjects with scores
     //And another variable to accumulate the total
     // Table cell end
  }
  //End table row
}

This is pseudo code, but this is how it can work. Others might have different solutions though.

goldmines214:



Look at the images, the last image(required result) is what I want displaying on my browser as html table while the other images are the database tables

It is simple now. Are you asking how to write the sql query?

goldmines214:


What I want is the correct query and php code to achieve the required result as in the image above

Pay someone to do so for you then or read up sql query.
What you need is simple JOIN
And WHERE clauses

naijasensei:


Excellent!
As I said, first fetch the subject names and subject IDs from the subjects table. Store this data in an iterable object - like an array (associative array, or hash map).
Now, write a query to fetch the data (from a specific class, term, and session) from the broadsheet table, and use a RIGHT JOIN to join the students table, then use GROUP BY to group the results by student_id.
You will now have a data set that contains the students with their respective subject scores. You can now iterate through the data by using the student_id parameter. You can build your table like this:

foreach (studIDs as id) {
  // Create a table row
  foreach (subjIDs as subj) {
      //Table cell begin
     //Check if there is a score for the subject
     //If there isn't, skip to the next subject
     //use a variable to count the subjects with scores
     //And another variable to accumulate the total
     // Table cell end
  }
  //End table row
}

This is pseudo code, but this is how it can work. Others might have different solutions though.

I like what you have done but please can you use my tables in the images above to write a query for me and how to loop the results using php in html table. Not object oriented php please, I am not familiar with object oriented php.

See the query I have used

You can achieve this when you use the students ID in the broadsheet or database table to get all the datas (name, class, scores, term, session and total) of each student. Very simple. Create an HTML form ID. Input the ID of a student and press submit. All the datas of the student in the row will be eched.

Whatshallido:
You can achieve this when you use the students ID in the broadsheet or database table to get all the datas (name, class, scores, term, session and total) of each student. Very simple. Create an HTML form ID. Input the ID of a student and press submit. All the datas of the student in the row will be eched.

That is not ideal. It's a broadsheet and not a particular student's result

Please, I really need help on this

What you need is joining related tables to get your broadsheet table. Read up how to use join and where keywords in SQL of your Database table on web.
Last fella I did something for on this forum denied me after it worked.

WebMind:
What you need is joining related tables to get your broadsheet table. Read up how to use join and where keywords in SQL of your Database table on web.
Last fella I did something for on this forum denied me after it worked.

I know how to join related tables and use where clause but it's not working out for me

Contact me to design it for u on 08109319667

goldmines214:



I know how to join related tables and use where clause but it's not working out for me

But that is what you need. What Webmind suggested.
Why isn't it working for you. What error message or result are you getting? You just need some correction on that part and nothing else. Cheers

goldmines214:



I know how to join related tables and use where clause but it's not working out for me

Then post your error here for all to see how you can be helped.