Finding two largest values in a row of a table [SOLVED]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Finding two largest values in a row of a table [SOLVED]

Post by PHP_mySQL__Newbie »

There must be a better way to do this. I am trying to print Tina's test scores and the average of two highest scores. [Average of (20, NULL, NULL) will be 20].
It seems like this is giving me a run around. Can someone please help me here?

1) What code should I use to find the average?
2) Is there an efficient (and simple) way to find two largest values in a row of a table?

Table:
[text]+------+------+------------+-------+-------+-------+------+------+------+
| Name | Age | address | test1 | test2 | test3 | f1 | f2 | f3 |
+------+------+------------+-------+-------+-------+------+------+------+
| John | 17 | 1st street | 20 | 30 | 100 | 20.1 | 20.2 | 20.6 |
| Tina | 23 | 3rd street | 80 | | | 5.4 | 5.5 | NULL |
| Jim | 23 | 2nd street | 10 | 12 | | 3.2 | 3.5 | 0 |
+------+------+------------+-------+-------+-------+------+------+------+[/text]

Code:

Code: Select all

<?php
        $conn = mysql_connect("localhost", "x", "x") or die(mysql_error());
        mysql_select_db("x", $conn) or die(mysql_error());
        $sql = "        
            SELECT name, test1, test2, test3, f1, f2, f3
            From testtableR
            Where name = 'Tina';                
        ";

        $result = mysql_query($sql, $conn) or die(mysql_error());
        $row = mysql_fetch_array( $result );
        
        $T_scores = array(0, 0, 0);    // Do I have to do this?
        $F_scores = array(0, 0);
        
        echo "T_scores<br>";
        for ($i=1;$i<=3;$i++)
        {
          echo $row[$i]."<br>";
          $T_scores[$i-1] = $row[$i];   
        }

        echo "<br>F_Scores<br>";
        for ($i=4;$i<=6;$i++)
        {
          echo $row[$i]."<br>";
          $F_scores[$i-4] = $row[$i]; 
        }
        
        echo "<br>T_Scores and F_Scores<br>";
        for ($i=0;$i<3;$i++)
        {
            echo $T_scores[$i]." ".$F_scores[$i]."<br>"; 
        }
        
// Sorting to get the get the two largest T_scores and F_Scores 
        sort($T_scores);
        sort($F_scores);
       
        echo "<br>Sorted arrays<br>";
        for ($i=0;$i<3;$i++)
        { echo $T_scores[$i]." ".$F_scores[$i]."<br>"; }

// Take the last 2 elements--that is the two largest elements

        $T = array();
        $F = array();

        for ($i=0;$i<2;$i++)
        { 
            $T[$i] = $T_scores[$i+1];
            $F[$i] = $F_scores[$i+1];
        }
        
        function calculate_average($arr) 
        {
            $total = 0;
            $count = count($arr); //total numbers in array
            foreach ($arr as $value)
            {
               $total = $total + $value; // total value of array numbers
            }
            $average = ($total/$count); // get average value
            return $average;
        }
        
        echo "<br>Averages<br>";        
        $F_Ave = calculate_average($F);
        echo $F_Ave."<br>";
 
        $T_Ave = calculate_average($T);
        echo $T_Ave."<br>";
      
        ?>

Output:
---------------------------------------
T_scores
80



F_Scores
5.4
5.5
2

T_Scores and F_Scores
80 5.4
5.5
2

Sorted arrays
2
5.4
80 5.5

Averages
5.45
40
Last edited by PHP_mySQL__Newbie on Wed Aug 17, 2011 1:20 am, edited 3 times in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Finding two largest values in a row of a table (incl. nu

Post by califdon »

Is there no possibility that a student will take more than 3 tests?? This is what is called an denormalized table structure and therefore doesn't meet the "First Normal Form" of a relational table, which is why this is more difficult than if you normalized the table by adding a second table to hold the scores. It is still possible to find the highest one or two scores by writing a function, but it isn't possible to do it in the query because the table doesn't meet the definition of a valid relational table. Normalizing your database would also make it simple to allow any student to take as many tests as appropriate, since an additional test would simply mean another record in the second table. If you don't want to, or can't correct the table structure, you could write a custom function to examine all 3 grades and return the only score, or the top 2 scores as an array.
User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Re: Finding two largest values in a row of a table (incl. nu

Post by PHP_mySQL__Newbie »

Califdon,thank you for replying.
The number of scores will vary.
I receive an Excel file with some 15 plus columns and 100 rows (and tons of null values) and I simply load it in my database. I didn't think about normalization (or thought it was necessary for this task ) before making that table.

Are you suggesting that instead of loading the entire Excel file, I should create tables like this?

Table 1
Fields: name, Age, Adress, etc
John, 25, 1st street
Jay, 26, 2nd street

Table 2
Fields: name, T1
Values:
Tina, 80,
John, 32
Jay, 29

Table 3:
Fields: name, T2
David, 25
Jane, 29

etc., etc.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Finding two largest values in a row of a table (incl. nu

Post by califdon »

PHP only connects to a database, it's the database design that I'm talking about. You would benefit from reading a few tutorials on relational databases, such as:
http://www.phlonx.com/resources/nf3/
http://www.surfermall.com/relational/lesson_1.htm
http://www.lucascobb.com/relational-dat ... -tutorial/
http://forums.aspfree.com/microsoft-acc ... 08217.html (I wrote this one)

The principle is that there is a many-to-many relationship between students and tests. That means that any one student may take "many" tests (anything from zero to infinity) AND any one test may be taken by "many" students. This ALWAYS requires 3 tables, regardless of how many students or how many tests:

The students table, with its unique primary index field, contains whatever data you collect on each student, like perhaps:

Code: Select all

ID          Integer, Auto-increment  (Primary Key)
LastName    VarChar(20)
FirstName   VarChar(20)
DateOfBirth Date   (never "age"!)
Address     VarChar(40)
... etc.
The tests table, with ITS unique primary index field, contains any information you need to keep about each test, maybe:

Code: Select all

ID          Integer, Auto-increment  (Primary Key)
TestName    VarChar(30)   ("Spelling Test 4B" or whatever)
... possibly other information, such as the time limit or the source of the test material, etc.
Then the "3rd table", sometimes called the "intersection table" or "junction table", or in this case perhaps the "Student_Test" table, contains 2 Foreign Key fields (one matches the ID of the student and one matches the ID of the test). There will be one record in this table for each occurrence of ONE student taking ONE test:

Code: Select all

StudentID   Integer  (Foreign Key into students)
TestID      Integer  (Foreign Key into tests)
DateTaken   Date
This is the standard way to structure a database for this purpose. There are no limitations on how many students, how many tests, how many tests any one student can take, it is very flexible and extensible. The junction table will have as many records as the number of occurrences of a student taking a test. This requires perhaps more logic (and thus code) than a beginner expects, but once written, everything works and you can retrieve all sorts of information from these 3 tables, joined appropriately.

A note about "age": avoid storing a person's age; when you go to recover data later, it will not be correct! Store the date of birth (DOB), from which you can easily calculate their age as of any particular date, such as the current date or the date a test was taken. The date when a test was taken by a particular student should be captured in the junction table. If the same student takes 2 tests this year, one before their birthday and one after, then another test a year later, you will have all the data required to determine (and print out on a report, for example) how old the student was when each test was taken.
Post Reply