SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: sorting data

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sorting data

    I want my web site to be as dynamic as possible (hence the reason for trying to master php) but the knowledge I have at the moment will not quite stretch to doing the following:

    I want to have the height attribute of an image to be controlled by data in a database table. The table will hold the numeric test result that pupils score when they take a test on my web based tutorial. The image is a column, the larger the score, the larger the column. I would like to know:
    1. Is it possible to do this? I thought I would do something like:
    <?php
    $Query = "SELECT score FROM $TableNAME";
    $Result = mysql_query ($Query);
    $Row = mysql_fetch_array ($Query);
    print("<img src = \"column.gif\" Height = \"$score\", WIDTH = \"25\" Border = \"0\" HSPACE = \"0\" ALIGN = \"Center\">");
    ?>

    2. Obviously, I need to have a WHERE clause in the Query. I am not too sure of the code to write to bring back only the biggest value from the score column. I have read about arrays and how to sort them but I don't know how to go from there. I would also like to know how to bring back the smallest score and how to bring back the average score. To add one more complication, I don't know how many scores there will be in the table, it will grow as more and more pupils take the test on the tutorial. The sort ($Row); would put alphabetical info into alphabetical order so I guess there is a similar function for sorting numerical info? and then is there any ready made functions to identify the largest, smallest and average?
    Thanks
    Last edited by p0c79; Aug 9, 2001 at 01:48.

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php 
    // select the max score, min score and average score from the table
    $Query "SELECT MAX(score) AS maxScore, MIN(score) AS minScore AVG(score) AS avScore FROM $TableNAME"
    $Result mysql_query ($Query); 
    $Row mysql_fetch_array ($Result);
    // extract the associative array key=>value pairs in $Row
    // into their own variables - $maxScore, $minScore, $avScore
    extract($Row); 
    print(
    "<img src = \"column.gif\" Height = \"$maxScore\", WIDTH = \"25\" Border = \"0\" HSPACE = \"0\" ALIGN = \"Center\">"); 
    ?>
    The query above will produce a result set with just one row with those summary values:
    eg:
    Code:
    maxScore   minScore   avScore
    10         1          5
    If you want to also extract a result set of all the scores from the databse, then this query
    PHP Code:
    $Query "SELECT score, MAX(score) AS maxScore, MIN(score) AS minScore AVG(score) AS avScore FROM $TableNAME"
    Will produce a result set that looks like this
    Code:
    score   maxScore   minScore   avScore
    7       10         1          5
    4       10         1          5
    10      10         1          5
    1       10         1          5
    To sort the above result set, you use ORDER BY ASC or ORDER BY DESC in your sql:
    PHP Code:
    $Query "SELECT score, MAX(score) AS maxScore, MIN(score) AS minScore AVG(score) AS avScore 
              FROM 
    $TableNAME
              ORDER BY score"

    <<edited to add>>

    BTW, I've just been thinking that you might need to have a GROUP BY clause in your SQL when using the summary functions MAX(), MIN(), AVG(), COUNT() etc in conjunction with normal columns such as score. That is when you want a result set with all the rows and the summary data. Hmm, not sure off the top of my head but have a hunch that you do!

    So, if don't really want to group the results, ie, you want all the rows, then just include a GROUP BY on the primary key and she'll be apples.

    EG:

    SELECT score, AVG(score) AS avScore, MAX(score) AS maxScore
    FROM tableName
    GROUP BY primaryKeyColumnName
    ORDER BY score DESC
    Last edited by freakysid; Aug 9, 2001 at 17:06.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help!

    That was very helpful - I followed your tip on getting the function
    from the manual by tagging its name on the URL, saves a lot of time
    and the page with the resources is great.
    I have just run your first query on a simple little table in my database
    called 'test'. It has two columns, called 'id' and 'score'. Unfortunately,
    something is going wrong! - I get the following in my browser:

    Your now connected to the Mysql server at the host ISP.
    The database p0c79 has been located.

    Warning: Supplied argument is not a valid MySQL result resource in /home/p0c79/www/query2.php on line 59

    Warning: extract() expects first argument to be an array in /home/p0c79/www/query2.php on line 65

    The script I am using is below:

    <html>
    <head>
    <title> Connecting to Mysql server at ISP host</title>
    </head>
    <body>
    PHP Code:
    <?php
    $Host 
    "*********";// Set the variables for the database access:
    $User "*****";
    $Password "*******";
    $DBName "****";     
    $TableName "test";

    $link = @mysql_connect("$Host""$User""$Password");
    if (!
    $link)
     {
     print(
    "<P>Unable to connect to the " .       
     
    "database server at this time.</P>\n");      //note how 2 strings have been concatonated
     
    exit();
    }
    else
    {
    print(
    "Your now connected to the Mysql server at the host ISP.<P>\n");
    }

    if (! @
    mysql_select_db("$DBName") )
     {
     print(
    "<P>Unable to locate the $DBName database at this time.</P>\n");
     exit();
     }
     else
    {
    print(
    "The database $DBName has been located.</P>\n");
    }
    /*
    $Query = "Select * FROM $TableName";
    $Result = mysql_query ($Query);
    $Return = mysql_result($Result, 0,2);
    print("$Return\n");
    */
    /*
    $Query = "Select id FROM $TableName ORDER BY id DESC LIMIT 1";
    $Result = mysql_query ($Query);
    if($Result)
    {
    $Return = mysql_result($Result, 0,0);
    print("$Return\n");
    }
    else
    print("No results in the database");
    */


    // select the max score, min score and average score from the table

    $Query "SELECT MAX(score) AS maxScore, MIN(score) AS minScore AVG(score) AS avScore FROM $TableNAME"

    $Result mysql_query ($Query); 

    $Row mysql_fetch_array ($Result);

    // extract the associative array key=>value pairs in $Row

    // into their own variables - $maxScore, $minScore, $avScore

    extract($Row); 

    print(
    "<img src = \"column.gif\" Height = \"$maxScore\", WIDTH = \"25\" Border = \"0\" HSPACE = \"0\" ALIGN = \"Center\">"); 




    mysql_close ($link);
    ?>
    </body>
    </html>

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    not a valid argument

    I found a couple of errors in the query.
    1. $TableNAME should be $TableName
    2. There was a comma missing after minScore

    However, to my disappointment, I still get not a valid argument.
    This message remains until I have commented out the query. Is there
    something else wrong with the query?
    Thanks

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Doesn't print

    Syntax OK - I had messed something up.. No parse errors but I get
    nothing when I try to print as below:

    // select the max score, min score and average score from the table

    $Query = "SELECT MAX(score) AS maxScore, MIN(score) AS minScore, AVG(score) AS avScore FROM $TableName";

    $Result = mysql_query ($Query);
    $Row = mysql_fetch_array($Result);


    // extract the associative array key=>value pairs in $Row

    // into their own variables - $maxScore, $minScore, $avScore

    extract($Row);


    print ("{$Row['score']}");
    printf ("%s",$Row['score']);

    print ("<img src = \"column.gif\" Height = \"$maxScore\", WIDTH = \"25\" Border = \"0\" HSPACE = \"0\" ALIGN = \"Center\">");

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting there!

    I understand! - you have pulled the data out of the table using 'AS'
    so I have to refer to it by these names - got ya!

    print("{$Row['maxScore']}"); this works now!

    Just one more thing how would I print the whole row of the query
    and the names that you have used to alias the column headings.
    Thanks

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $Query*=*"SELECT*MAX(score)*AS*maxScore,*MIN(score)*AS*minScore,*AVG(score)*AS*avScore*
             FROM*
    $TableNAME";*
    $Result*
    =*mysql_query*($Query);*
    $Row*
    =*mysql_fetch_array*($Result);

    // for each element in array $Row
    // grab the $key and $value and print them out
    foreach($Row AS $key => $value) {
       print 
    "$key :  $value <br>";


  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    error message!

    Hi
    Thanks for the help.
    Something needs attention somewhere though. I get the following
    message:

    Your now connected to the Mysql server at the host ISP.
    The database p0c79 has been located.


    Warning: Supplied argument is not a valid MySQL result resource in /home/p0c79/www/query4.php on line 53

    Warning: Invalid argument supplied for foreach() in /home/p0c79/www/query4.php on line 63

    My script is:
    PHP Code:
    <?php
    $Host 
    "***********";// Set the variables for the database access:
    $User "****";
    $Password "*****";
    $DBName "****";     
    $TableName "******";

    $link = @mysql_connect("$Host""$User""$Password");
    if (!
    $link)
     {
     print(
    "<P>Unable to connect to the " .       
     
    "database server at this time.</P>\n");      //note how 2 strings have been concatonated
     
    exit();
    }
    else
    {
    print(
    "Your now connected to the Mysql server at the host ISP.<P>\n");
    }

    if (! @
    mysql_select_db("$DBName") )
     {
     print(
    "<P>Unable to locate the $DBName database at this time.</P>\n");
     exit();
     }
     else
    {
    print(
    "The database $DBName has been located.</P>\n");
    }
    /*
    $Query = "Select * FROM $TableName";
    $Result = mysql_query ($Query);
    $Return = mysql_result($Result, 0,2);
    print("$Return\n");
    */
    /*
    $Query = "Select id FROM $TableName ORDER BY id DESC LIMIT 1";
    $Result = mysql_query ($Query);
    if($Result)
    {
    $Return = mysql_result($Result, 0,0);
    print("$Return\n");
    }
    else
    print("No results in the database");
    */
    $Query "SELECT MAX(score) AS maxScore, MIN(score) AS minScore, AVG(score) AS averageScore FROM $TableName";
    $Result =mysql_query ($Query);
    $Row mysql_fetch_array($Result);
    /*
    for ($n = 0; $n < count($Row); $n++)
          {
        $Line = each ($Row);
        print ("{ $Line[key]'s value is $Line[value].}\n");
        }
    */
    // for each element in array $Row 
    // grab the $key and $value and print them out 
    foreach($Row AS $key => $value) { 
    print 
    "$key : $value <br>"
    }



    mysql_close ($link);
    ?>
    </body>
    </html>

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    OK NOW!

    I used the mysql_error() on the query and it came back with the
    answer, it said:
    "there is no such field as 'score'" and sure enough, I was using the wrong table.

    Have you any idea why the results come back with a ratio between each line?

    0 : 9
    maxScore : 9
    1 : 2
    minScore : 2
    2 : 6.0000
    averageScore : 6.0000

  10. #10
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah because I put it in the string that gets printed as the delimiter
    PHP Code:
    print*"$key*:*$value*<br>"
    So edit the string to suit.

    However, your results show some undesirable consequences of the code I provided. See how each column from the result set is getting printed out twice? Once for its index number in the array, and once for its key? That's unexpected. So I wrote up some test code using two different ways of achieving the same thing. Might as well post it here:
    PHP Code:
    <?php
    $myArray 
    = array( "foo"=>"100""bar"=>"200""zoot"=>"300" );

    echo 
    "<br>list(key, value) each array element<br>";

    while(list(
    $key$value) = each($myArray) ) {
       echo 
    "$key : $value <br>";
    }

    echo 
    "<br>testing foreach key=>value<br>";

    foreach(
    $myArray AS $key => $value) {
       echo 
    "$key : $value <br>";
    }
    Results:
    [php]
    Code:
    list(key, value) each array element
    foo : 100 
    bar : 200 
    zoot : 300 
    
    testing foreach key=>value
    foo : 100 
    bar : 200 
    zoot : 300
    Last edited by freakysid; Aug 10, 2001 at 15:31.

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: OK NOW!

    Originally posted by p0c79
    I used the mysql_error() on the query and it came back with the
    answer, it said:
    "there is no such field as 'score'" and sure enough, I was using the wrong table.

    Have you any idea why the results come back with a ratio between each line?

    0 : 9
    maxScore : 9
    1 : 2
    minScore : 2
    2 : 6.0000
    averageScore : 6.0000

    Again the reason for this is beacuse you used mysql_fetch_array() wghich returns two sets of data for each row one with numercial indices 0, 1, 2 and one with associative indices maxScore, minScore, averageScore.
    Either use mysql_fetch)array($result, MYSQL_ASSOC) or mysql_fetch_assoc() (this one requires php 4.0.3 and up)
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Appreciate all the help

    Thanks for such good answers everyone. I have benefited greatly
    from all these informative replies.

  13. #13
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah-ha! Smart cat


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •