0

What is the best way to fill the second column of this table?

enter image description here

I want to display in the "Percent of class time" column the result of this formula:

= time spent on topic / total time spent on topic * 100

All the values come from the MySql database.

Here's my code to display data in the above table

$query=mysqli_query($con,
    "select * from test_validity_items
     where test_validity_id='".$_GET['test_v_id']."'");
$i=0;
while($row=mysqli_fetch_array($query))
{ 
    $i++;

    <tr>        
        <td><center><?php echo $row['test_items_time_spent'];?> </center></td>                              
        <td><center><?php echo $row['test_items_percent_class_time'];?></center></td>
    </tr>
}
trincot
  • 317,000
  • 35
  • 244
  • 286
Oplochi
  • 11
  • 4
  • 1
    This probably can be done easier in SQL. Can you share your SELECT statement? – trincot Feb 07 '16 at 12:39
  • this is the only SQL I am using "select test_items_percent_class_time from test_validity_items where test_validity_id='".$_GET['test_v_id']."'" $i=0; while($row=mysqli_fetch_array($query)){ $i++;
    – Oplochi Feb 07 '16 at 12:53
  • Thanks, It would be nice if you could edit your question and insert this information. – trincot Feb 07 '16 at 12:55
  • done. badly needed to finish this problem – Oplochi Feb 07 '16 at 13:03
  • I suppose your code has closing/opening PHP tags before and after those `tr` and `td` tags? As it is now, this is not valid code. You also put me on wrong foot with the `SELECT test_items_percent_class_time` in previous comment: you don't select that at all. – trincot Feb 07 '16 at 13:09

1 Answers1

0

This can be done in your SQL statement. Your current SELECT looks like this:

 SELECT  *
 FROM    test_validity_items
 WHERE   test_validity_id='...'

where the dots represent a value retrieved from the request.

You can add the percentage as a calculated column in the above SQL, like this:

SELECT  test_items_time_spent,
        test_items_time_spent * 100
            / (SELECT SUM(test_items_time_spent)
               FROM   test_validity_items
               WHERE  t.test_validity_id = test_validity_id)
        AS test_items_percent_class_time
FROM    test_validity_items t
WHERE   test_validity_id='...'

Here is a SQL fiddle

Be careful, when you add other conditions in the WHERE clause, to always add the same conditions in the inner sub-query as well.

Altering your existing PHP code, this becomes:

<?php
$query=mysqli_query($con,
    "select test_items_time_spent,
            test_items_time_spent * 100
                / (SELECT SUM(test_items_time_spent)
                   FROM   test_validity_items
                   WHERE  t.test_validity_id = test_validity_id)
            AS test_items_percent_class_time
     from   test_validity_items
     where  test_validity_id='".$_GET['test_v_id']."'");
$i=0;
while($row=mysqli_fetch_array($query))
{ 
    $i++;
?>    
    <tr>        
        <td><center><?php echo $row['test_items_time_spent'];?> </center></td>                              
        <td><center><?php echo $row['test_items_percent_class_time'];?></center></td>
    </tr>
<?php
}
?>

SQL Injection

Please be warned that your code is vulnerable to SQL injection. It is not safe to concatenate $_GET values to your SQL. You should use prepared statements instead and pass the parameter to MySql separately.

Community
  • 1
  • 1
trincot
  • 317,000
  • 35
  • 244
  • 286
  • i only used $_GET to select data's from foreign table with specific ID. if I click from a list in test.php which is tbl_test, it will display list of test items that is connected with an ID from tbl_test but items are in tbl_test_items. How can I automatically computed the 'test_items_percent_class_time' with this formula? % of Class time on topic = time spent on topic / total time spent on topic * 100 – Oplochi Feb 07 '16 at 13:25
  • I just updated my answer. I repeat that `$_GET` is unsafe to use in SQL. If users just click, there is no problem, but a user could manipulate the URL and type a value of his own which could trick the SQL statement in revealing information that is not intended. Please read about SQL injection. – trincot Feb 07 '16 at 13:28
  • Thanks bro. Got it. I am now going to use prepared statements to prevent SQL injection. – Oplochi Feb 07 '16 at 13:45
  • why I am not getting the correct ans in that sql query? E.g ---time spent - 50 (1st data),50(2nd data) TOTAL= 100 ---% time spent = 0.4348 (1st data), 0.4348 (2nd data) it is supposed to be 0.5% – Oplochi Feb 07 '16 at 14:00
  • The [SQL fiddle](http://sqlfiddle.com/#!9/5a887/2) I posted works fine. When I modify it to work on 50, 50, it returns as percentage 50, 50, which is correct: [SQL fiddle for 50/50](http://sqlfiddle.com/#!9/17c2c7/1). I don't get the 0.4348 you talk about. It looks like you have included a condition in the WHERE clause which you did not repeat in the inner sub-SELECT. I added a **be careful** paragraph in my answer. – trincot Feb 07 '16 at 14:05
  • Can you tell me if that was the problem? – trincot Feb 08 '16 at 09:11