SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Vancouver, WA
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rows Holding the Group-wise Max of a Field

    I'm trying to use the code in Section 9.5.4 of the MySQL documentation titled "The Rows Holding the Group-wise Maximum of a Certain Field" (highlighted in red). I'm obviously incorporating it into the query statement incorrectly as I'm getting an "Error in query" message from the $result variable (highlighted in blue). Hopefully one of you more experienced folks will spot my error right away.

    Following is my code:
    -----------------------------------------------------------
    <html>
    <head>
    <title></title>
    </head>

    <body>
    <?php

    // set up some variables

    // server name
    $server = "localhost";

    // username
    $user = "test";

    // password
    $pass = "test";

    // database to query
    $db = "test";

    // open a connection to the database
    $connection = mysql_connect($server, $user, $pass) or die("Invalid server or user");

    // formulate the SQL query
    $query = "CREATE TEMPORARY TABLE tmp (
    clubs VARCHAR(20),
    manufacturer VARCHAR(20),
    model VARCHAR(50),
    shaft VARCHAR(20),
    price DECIMAL(5,2));

    LOCK TABLES clubs read;

    INSERT INTO tmp SELECT clubs, manufacturer, model, shaft, MIN(price) FROM clubs GROUP BY clubs, manufacturer, model, shaft;

    SELECT clubs.clubs, clubs.manufacturer, clubs.model, clubs.shaft, price FROM clubs, tmp
    WHERE clubs.clubs=tmp.clubs AND clubs.manufacturer=tmp.manufacturer AND clubs.model=tmp.model AND clubs.shaft=tmp.shaft AND clubs.price=tmp.price;

    UNLOCK TABLES;

    DROP TABLE tmp";


    // run the query on the database
    $result = mysql_db_query($db,$query,$connection) or die("Error in query");

    // display the result
    echo "<table width=500 border=1 cellspacing=1 cellpadding=2>";
    echo "
    <tr>
    <td width=100 align=left><strong>Clubs</strong></td>
    <td width=100 align=left><strong>Manufacturer</strong></td>
    <td width=100 align=left><strong>Model</strong></td>
    <td width=100 align=left><strong>Shaft</strong></td>
    <td width=100 align=left><strong>Price</strong></td>
    </tr>";


    // with a while loop
    // this loop will iterate as many times as there are records
    while ($myrow = mysql_fetch_row($result))
    {
    echo "
    <tr>
    <td width=100 align=left>$myrow[0]</td>
    <td width=100 align=left>$myrow[1]</td>
    <td width=100 align=left>$myrow[2]</td>
    <td width=100 align=left>$myrow[3]</td>
    </tr>";
    }

    // free up used memory
    mysql_free_result($result);

    ?>

    </body>
    </html>
    -----------------------------------------------------------
    If I go to the mysql> prompt and enter the information from the $query variable I'm able to test that it works correctly. I just can't get it to work with PHP from a web page. I'm not sure if all that code belongs in the $query variable itself. I've gone through and checked to make sure I didn't have any grammatical errors and as far as I can tell it must be something to do with the format of the code.

    Thanks for the help guys!

    Adam

  2. #2
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe try them as separate queries? I dunno.

  3. #3
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, are you sure the username and password and database name are correct?

  4. #4
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Vancouver, WA
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes they are correct.

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    delemtri is correct. i'm almost positive you can only run one query at a time. run them each seperately and i bet it'll work.

  6. #6
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Vancouver, WA
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As you've probably noticed I'm pretty new to this so now my question is how exactly do I go about modifying this to perform multiple queries so that I get the final results I need?

    Thanks, delemtri and Dr. Larry.

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    k, here's how i'd do it.

    PHP Code:
    $server 'localhost';
    $user 'test';
    $pass 'test';
    $db 'test';

    $connection mysql_connect($server$user$pass) or die('Invalid server or user');

    mysql_select_db($db);

    mysql_query('CREATE TEMPORARY TABLE tmp ( 
    clubs VARCHAR(20), 
    manufacturer VARCHAR(20), 
    model VARCHAR(50), 
    shaft VARCHAR(20), 
    price DECIMAL(5,2))'
    );

    mysql_query('LOCK TABLES clubs read');

    mysql_query('INSERT INTO tmp SELECT clubs, manufacturer, model, shaft, MIN(price) FROM clubs GROUP BY clubs, manufacturer, model, shaft');

    $result mysql_query('SELECT clubs.clubs, clubs.manufacturer, clubs.model, clubs.shaft, price FROM clubs, tmp 
    WHERE clubs.clubs=tmp.clubs AND clubs.manufacturer=tmp.manufacturer AND clubs.model=tmp.model AND clubs.shaft=tmp.shaft AND clubs.price=tmp.price'
    );

    echo 
    '<table width=500 border=1 cellspacing=1 cellpadding=2>'
    echo 

    <tr> 
    <td width=100 align=left><strong>Clubs</strong></td> 
    <td width=100 align=left><strong>Manufacturer</strong></td> 
    <td width=100 align=left><strong>Model</strong></td> 
    <td width=100 align=left><strong>Shaft</strong></td> 
    <td width=100 align=left><strong>Price</strong></td> 
    </tr>'
    ;

    while (
    $myrow mysql_fetch_row($result))
    {
    echo 
    "
    <tr>
    <td width=100 align=left>
    $myrow[0]</td> 
    <td width=100 align=left>
    $myrow[1]</td> 
    <td width=100 align=left>
    $myrow[2]</td> 
    <td width=100 align=left>
    $myrow[3]</td> 
    </tr>"
    ;
    }

    mysql_query('UNLOCK TABLES');

    mysql_query('DROP TABLE tmp'); 
    of course you could add back in some of your error handling you had for the mysql_query()s.
    Last edited by DR_LaRRY_PEpPeR; Jul 10, 2001 at 14:14.

  8. #8
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Vancouver, WA
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Dr. Larry. I'll give it a shot and see what happens


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
  •