CODEWITHSUNDEEP
phpmysqldatabase
Tapiwanashe Mugoniwa
Tapiwanashe Mugoniwa

Reputation: 25

Getting two rows of results as one in MYSQL and PHP

I need help getting some information from a database. What I need is to get information from two rows to be collected as one, because I would need to get two sets of marks for the same subject so the initial code I have would be something like this:

SELECT scored_mark, total_mark
WHERE student_id ='$_SESSION[user_id]'
AND sub_id=$sid ORDER BY timestamp DESC LIMIT 2

But now what I want is to get both and calculate the percentages and the display them in PHP and HTML table as something like this:

<?php

$first_score = $row['scored_mark']; 
$first_total = $row['total_mark'];
$first_mark_percentage = $first_score/$first_total*100

$sec_score = $row['scored_mark']; 
$sec_total = $row['total_mark'];
$sec_mark_percentage = $sec_score/$sec_total*100
?>
<table border="1">
<tr>
<th>Subject</th>
<th>1st Mark</th>
<th>2nd Mark</th>
</tr>
<tr>
<td>Maths</td>
<td>$first_mark</td>
<td>$sec_mark</td>
</tr>
</table>

And this is the information in the table:

student_id | sub_id | mark_type | scored_mark | total_mark | timestamp
-----------------------------------------------------------------------
55         |    75  |  4        |        45   |       50   | 13984356
55         |    75  |  4        |        80   |      150   | 13984145
55         |    76  |  4        |        20   |       50   | 13984301
42         |    21  |  3        |        38   |       50   | 13984569

Update: The part I am really having a problem is:

<tr>
<td>Maths</td>
<td>$first_mark</td>
<td>$sec_mark</td>
</tr>

The problem is now display the two results in the rows side by side because it would end up being information for a new row.

Upvotes: 0

Views: 150

Answers (3)

Barmar
Barmar

Reputation: 782693

Just call mysqli::fetch_assoc() to fetch each row before you process it.

$row = $result->fetch_assoc();
$first_score = $row['scored_mark']; 
$first_total = $row['total_mark'];
$first_mark_percentage = $first_score/$first_total*100;

$row = $result->fetch_assoc();
$sec_score = $row['scored_mark']; 
$sec_total = $row['total_mark'];
$sec_mark_percentage = $sec_score/$sec_total*100

Upvotes: 1

Dragony
Dragony

Reputation: 1722

You could try using the MySQL SUM() Function.

SELECT
    SUM(scored_mark) as score,
    SUM(total_mark) as total
FROM
    [...]
WHERE
    student_id ='$_SESSION[user_id]'
AND
    sub_id=$sid
ORDER BY
    timestamp DESC
LIMIT
    2

With the result you should be able to get the total percentage over all marks for one subject.

Upvotes: 0

Knelis
Knelis

Reputation: 7159

You can do this in your SQL query.

Try this:

SELECT scored_mark / total_mark * 100 AS percentage 
WHERE student_id = '$_SESSION[user_id]' 
    AND sub_id = $sid 
ORDER BY timestamp DESC 
LIMIT 2

The result will be accessible as $row['percentage'].

Upvotes: 1

Related Questions