Inserting dynamic column into website table?

Question

I'm working on a website that contains a table with 12 columns that displays various information about students in each row. I'm trying to add a 13th column that displays either PASS, FAIL, or leaves it BLANK if the student hasn't written the exam yet.

In my MSSQL database I have two tables. T1 (Students) contains various information about the student and has a keyId (primary key) which is what the website table is using to group rows together by student. Once the student wrote the exam, he is assigned a student number in the same table (if not it is NULL). This student number is the primary key in the second table that has either a value of PASS or FAIL attached to it.

EDIT: The data PASS or FAIL in the second table is inserted through the website on another tab where the teacher inputs the student number and chooses either pass or fail.

The data type of the Status column (pass or fail) is enum(string) P & F.

<?php 

$objMSSQL = new cMSSQL(); 

//[...] 

for($i = 0; $i < $noOfRows; $i++)
    $noOfRows = $objMSSQL->getAffectedRows();

//[...]

$examStatus = $objMSSQL->getTable("
    SELECT * 
    FROM [Students] 
    INNER JOIN [Exams] ON Exams.studentNo = Students.studentNo
")

if ($examStatus[$i][Status] == 'PASS')
    echo '<td width="80px"> PASS </td>';
elseif ($examStatus[$i][Status] == 'FAIL')
    echo '<td width="80px"> FAIL </td>';
else
    echo '<td width="80px"></td>';

?>

I've been searching endlessly for a solution and just cant figure out why it isn't displaying correctly on the website (it's displaying pass or fail seemingly randomly and leaving nothing blank).

Sorry Aaron, I don't quite understand your question (remember I am somewhat of a beginner at this). — BillyCode, Jan 31 '13 at 15:29
You said "I've been searching endlessly for a solution". Would you describe in more detail what you looked for, what you found, and why it did not solve your problem. — Aaron Kurtzhals, Jan 31 '13 at 15:33
I don't understand why I am getting downvoted. I am obviously new to this site (which I love btw), so if I am doing something wrong or missing some information or code just let me know, please. — BillyCode, Jan 31 '13 at 15:33
Searching endlessly by researching w3, this website and others looking for solutions. I assume there is a problem with my SQL query? I have been trying different methods for days of making this work which is why im trying to use the "if" method of filling the table data now. In terms of debugging, my experience is limited so I am trying different SQL queries and such to the best of my knowledge. — BillyCode, Jan 31 '13 at 15:37
Would you post 1. Some sample data 2. The output you are expecting 3. The output you actually get. — Aaron Kurtzhals, Jan 31 '13 at 15:43
If your PASS and FAIL data is in a database column associated with a student ID, you don't need this if-else statement. You need to just create the php code that will generate the html you plan to use with the database field names/variables — N1tr0, Jan 31 '13 at 16:07
@Aaron Kurtzhals What is the best way to go about this? Simply explain or you need an SQL fiddle or something. — BillyCode, Jan 31 '13 at 16:21
@N1tr0 This makes sense. Could you please link me or show me what this looks like? — BillyCode, Jan 31 '13 at 16:22

N1tr0 · Answer 1 · 2013-01-31T16:02:59.313

0

In your SQL query, you can add in your 13 column there. Something like:

SELECT *, CASE WHEN EXAM_SCORE > 70.00 then 'PASS'
               WHEN EXAM_SCORE < 70.00 then 'FAIL'
               else ''
               END EXAM_SCORE as PassFail
FROM [Students] INNER JOIN [Exams] ON Exams.studentNo=Students.studentNo

edited Jan 31 '13 at 16:02

answered Jan 31 '13 at 15:03

N1tr0

485
2
6
24

Since I don't know your table structures I just made up a column name but you get the idea. :-). Not sure how you are determining PASS or FAIL. – N1tr0 Jan 31 '13 at 15:15
It is not related to the grade, on another tab of the website the teacher inputs the student number and either clicks PASS or FAIL. Thank you. – BillyCode Jan 31 '13 at 15:28
You might want to add this functionality to your question up top so people can approach it from the right angle. – N1tr0 Jan 31 '13 at 15:40
After reading your edit, I think my example should still work for you (or something like it). If the data is in the second table, why can't you select it out? – N1tr0 Jan 31 '13 at 15:55
This is where my problem lies and my lack of experience keeps me blocked. PASS or FAIL is in the second table, but in the website pass or fail needs to be related to the students keyId (in order to be in the correct row). The keyId is only available in the first table. Through the website, a student number is entered with either pass or fail, which is inserted into the second table. This student number is the only thing found in both tables (once inserted in the second table, theres a column where the student has NULL but now becomes the student number, so its basically a reference). – BillyCode Jan 31 '13 at 16:18
It sounds like you just need to make your query a JOIN between the two tables. – Barmar Jan 31 '13 at 16:52
@Barmar I will try this and let you know how it went – BillyCode Jan 31 '13 at 17:01

score 0 · Accepted Answer · edited May 23 '17 at 12:12

EDIT: I've copied in your code from above and added the piece you need using the appropriate function. (tweak/edit/rename as needed to get to work/display correctly).

Here is another example: Creating a dynamic table with PHP

You can use this as an example to create your dynamic table in PHP:

<?php 

$objMSSQL = new cMSSQL(); 

//[...] 

for($i = 0; $i < $noOfRows; $i++)
    $noOfRows = $objMSSQL->getAffectedRows();

//[...]

$examStatus = $objMSSQL->getTable("
    SELECT * 
    FROM [Students] 
    INNER JOIN [Exams] ON Exams.studentNo = Students.studentNo
")

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
       print '<tr>'
      .'<td>' . $row['col1'] . '</td>'
      .'<td>' . $row['col2'] . '</td>'
      .'<td>' . $row['col3'] . '</td>'
      .'<td>' . $row['colx'] . '</td>'
      .'</tr>';
}

?>

So it's basically looping through your query results. Since you are joining on your Students table, you will have access to the PASS/FAIL data. You will probably want to not use '*' but instead list out what columns you want from each table.

Now, I'm not certain the rest of your code is correct or what you need (i.e. FOR loop), the WHILE loop is needed to create the table and output the data.

I have something similar for my website but use ECHO instead of PRINT. — N1tr0, Jan 31 '13 at 16:39
It's very similar. Here a good place to start: http://php.net/manual/en/function.mssql-connect.php — N1tr0, Jan 31 '13 at 16:44

score 0 · Answer 3 · answered Jan 31 '13 at 16:48

0

if you require a variable / dynamic number of columns, you should look into vertical table layout

answered Jan 31 '13 at 16:48

beercan

61
5

Inserting dynamic column into website table?

3 Answers3