SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Showing highest score&name from mysql

    This builds onto the following forum and I need some simple coding help:
    http://www.sitepoint.com/forums/show...38#post2025738

    Can someone code a fairly simple this that displays in text letters the highest score in a section of my mysql database?

    database: paintballscores
    host: localhost
    username: root
    password: (leave blank, ill fill it in)

    The scores are in paintballscores (database)>score (table)>2 fields, name & score. I want the entry with the highest score to have it's name and score embedded in my website in a little box I can have say: Top scorer is: (name) with (score) points.


    THANK YOU ALL SO MUCH FOR HELPING ME SO FAR.

  2. #2
    SitePoint Evangelist TRISPECTIVE's Avatar
    Join Date
    Sep 2002
    Location
    n/a
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could use MAX() to get the highest value for the score column.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't program much in php, so please help me code this. Here is the top, thats all I know how to do:

    Code:
    // ***********************************************************//
    // file:               showhighscore.php
    // purpose:        finds the highest score in the mysql database and displays it 
    //                     along with the player who acheived that score's name.
    // authors:        (so far) Marc Gugliuzza (entire submitscore.php),
    //                               TRISPECTIVE (idea of using MAX()  )
    // ***********************************************************//
    
    <?php
    
    $db_host            = "localhost";
    $db_user            = "root";
    $db_pass             = "password";
    $db_name            = "paintballscores";
    
    
    mysql_connect($db_host,$db_user,$db_pass);
    mysql_select_db($db_name);
    
    // the rest of code goes here, please help me out
    
    ?>

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can anyone code this for me?

  5. #5
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not the greatest at PHP or MySQL commands, but wouldn't something like:
    PHP Code:
    <?php
    $name 
    "SELECT name FROM table WHERE score=MAX();";
    $score "SELECT score FROM table WHERE score=MAX();";
    echo(
    '<p>The person with the highest score is:'.$name.' his/her score is:'.$score.'</p>');
    ?>
    Maybe?
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is what I have so far and it isn't working:
    Code:
    <?php
    // ***********************************************************//
    // file:               showhighscore.php
    // purpose:        finds the highest score in the mysql database and displays it 
    //                       along with the player who acheived that score's name.
    // authors:        (so far) Marc Gugliuzza (entire submitscore.php),
    //                       TRISPECTIVE (idea of using MAX()  )
    //                       JABIRD setting variables&basic connection
    // ***********************************************************//
    
    // database info
    $db_host            = "localhost";
    $db_user            = "root";
    $db_pass             = "";
    $db_name            = "paintballscores";
    // end of database info
    
    //set variables
    $name = "SELECT name FROM score WHERE score=MAX();";
    $score = "SELECT score FROM score WHERE score=MAX();";
    //no other vatiables
    
    //database connection
    mysql_connect($db_host,$db_user,$db_pass);
    mysql_select_db($db_name);
    //database connected
    
    
    echo('<p>The person with the highest score is:'.$name.' his/her score is:'.$score.'</p>');
    
    ?>
    The page is at www.gamerssoft.com/viewscore.php

    and this is what is being displayed:
    Code:
    The person with the highest score is:SELECT name FROM score WHERE score=MAX(); his/her score is:SELECT score FROM score WHERE score=MAX();
    so you can see the echo is working, as well as the variables being substituted in, but the variables arn't getting the data from mysql. What else do I need?

    Just so you can understand the structure, this is how it goes, databaseaintballscores>table:score>data, name & score fields

  7. #7
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $name = mysql_query("SELECT name FROM score WHERE score=MAX();");
    $score = mysql_query("SELECT score FROM score WHERE score=MAX();");
    if that doesn't work take the semi-colon away after MAX() and see if that works... like I said, I'm not the best at all of this though..

    you also may want to check for a failure:
    $name = mysql_query("SELECT name FROM score WHERE score=MAX();");
    if(!mysql_query($name)) {
    exit('There was an error retrieving the highest scorer. The error we received isbr />'.mysql_error().';
    }
    $score = mysql_query("SELECT score FROM score WHERE score=MAX();");
    if(!mysql_query($score)) {
    exit('There was an error retrieving the score. The error we received isbr />'.mysql_error().';
    }

    its always a good idea to check for errors in mysql with mysql_error... this way you know why it isn't working...

    You also want to connect to the database and select it BEFORE you try to get content from the database :P
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    now it's better, but the score isn't showing, it's just blank (but better that before).

    The test gives a parse error on the last line, but the mysql is working, i just conected with phpmyadmin, and my store works.

    the semicolon removal isn't doing anything, so i put it back,
    here is the new code:

    Code:
    <?php
    // ***********************************************************//
    // file:               viewscore.php
    // purpose:        finds the highest score in the mysql database and displays it 
    //                       along with the player who acheived that score's name.
    // authors:        (so far) Marc Gugliuzza (entire submitscore.php),
    //                       TRISPECTIVE (idea of using MAX()  )
    //                       JABIRD setting variables&basic connection
    // ***********************************************************//
    
    // database info
    $db_host            = "localhost";
    $db_user            = "root";
    $db_pass             = "";
    $db_name            = "paintballscores";
    // end of database info
    
    //set variables
    $name = mysql_query("SELECT name FROM score WHERE score=MAX();");
    $score = mysql_query("SELECT score FROM score WHERE score=MAX();");
    //no other vatiables
    
    //database connection
    mysql_connect($db_host,$db_user,$db_pass);
    mysql_select_db($db_name);
    //database connected
    
    
    echo('<p>The person with the highest score is:'.$name.' his/her score is:'.$score.'</p>');
    
    ?>
    you can see it at www.gamerssoft.com/viewscore.php

    if it works right it should say the name is brandon and score is 3

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If Someone Gets This Right I"ll Pay Them Via Paypal

  10. #10
    SitePoint Guru Marc's Avatar
    Join Date
    Nov 2000
    Location
    New York
    Posts
    765
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's a solution that doesn't involve the MAX() function, but it does require you to change the original table a little bit. The command to create the table is this:
    CREATE TABLE `score` (
    `name` varchar(64) NOT NULL default '',
    `score` int(64) NOT NULL default '0'
    ) TYPE=MyISAM; Once you've done that, here's the code needed.
    PHP Code:
     <?php
     
    // ***********************************************************//
     // file:               viewscore.php
     // purpose:        finds the highest score in the mysql database and displays it 
     //                       along with the player who acheived that score's name.
     // authors:        (so far) Marc Gugliuzza (entire submitscore.php),
     //                       TRISPECTIVE (idea of using MAX()  )
     //                       JABIRD setting variables&basic connection
     // ***********************************************************//
     
     // database info
     
    $db_host            "localhost";
     
    $db_user            "root";
     
    $db_pass             "pass";
     
    $db_name            "test";
     
    // end of database info
     
     //database connection
     
    mysql_connect($db_host,$db_user,$db_pass);
     
    mysql_select_db($db_name);
     
    //database connected
     
     //set variables
     
    $score_result mysql_query("SELECT name,score FROM score order by score desc limit 1");
     
    $score mysql_fetch_assoc($score_result);
     
    //no other vatiables
     
     
    echo("<p>The person with the highest score is: ".$score["name"]." his/her score is: ".$score["score"]."</p>");
     
     
    ?>
    Marc Gugliuzza
    marc.gugliuzza.com



  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THANKS SOOOOOO MUCH, ill email you about money

  12. #12
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem with this solution is that it doesn't work if there are more than one user with the very same score.
    If that could happen, you first have to get how many users have the highest score and then use it for the LIMIT number.

    Another drawback is that if the database expands and it's getting large(er) this query is damn slow since all entries
    first have to be ordered - and that takes its time. At least indexing the score field is necessary to speed the query up.

    And please: use error checking - makes life easier
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  13. #13
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frezno
    And please: use error checking - makes life easier
    PLEASEEE
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  14. #14
    011110010110000101111001 jabird's Avatar
    Join Date
    Aug 2004
    Location
    U.S.
    Posts
    593
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I retyped the whole thing...

    PHP Code:
    <?php
    $db_host 
    "localhost";
    $db_user "root";
    $db_pass "";
    $db_name "paintballscores";

    mysql_connect($db_host,$db_user,$db_pass);
    mysql_select_db($db_name);

    $name "SELECT name FROM score WHERE score=MAX()";
    if(!
    mysql_query($name)) {
    echo(
    'There was an error retrieving the top scorer from the database. The error we got was:<br />'.mysql_error().'');
    } else {
    $score "SELECT score FROM score WHERE score=MAX()";
    }
    if(!
    mysql_query($score)) {
    echo(
    'There was an error retrieving the top score from the database. The error we got was:<br />'.mysql_error().'');
    }
    echo(
    'The top scorer is:'.$name.' his/her score is:'.$score.'');
    ?>
    see how that works, and if it doesn't work, give me the error you got...
    ~Jabird
    Jabird.com
    If I were binary... I'd be all 1's for you.
    BBCode trouble?

  15. #15
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jabird
    PLEASEEE
    you could do something like this:
    PHP Code:
    //-- Set to true if working in development environment
    //-- Set to false on your production server to not to give to many details to the user
    define(DEBUGtrue);
     
    $db = @mysql_connect($db_host,$db_user,$db_pass);
    if (! 
    $db)
    {
        if (
    DEBUG)
        {
            echo 
    "<h2>Cannot connect to $db_host as $d_buser</h2>";
            echo 
    "<b>MySQL Error</b> "mysql_errno() . ": " mysql_error();
        }
        else
        {
            echo 
    "<h2>There's an error #1, script haltet.<br>Please check back a little later.</h2>";
            exit();
        }
    }
     
    if (! @
    mysql_select_db($db_name))
    {
        if (
    DEBUG)
        {
            echo 
    "<h2>Cannot select database $dbname.</h2>";
            echo 
    "<b>MySQL Error</b> "mysql_errno() . ": " mysql_error();
        }
        else
        {
            echo 
    "<h2>There's an error #2, script haltet.<br>Please check back a little later.</h2>";
            exit();
        }
    }
     
    $sql "SELECT name, score FROM score ORDER BY score DESC LIMIT 1";
     
    $score_result mysql_query($sql);
    if (! 
    $score_result)
    {
        if (
    DEBUG)
        {
            echo 
    "<h2>There's an error in the query.</h2>";
            echo 
    "<b>MySQL Error</b> "mysql_errno() . ": " mysql_error();
            echo 
    "<pre>"htmlspecialchars($sql) ."</pre>";
        }
        else
        {
            echo 
    "<h2>There's an error #3, script haltet.<br>Please check back a little later.</h2>";
            exit();
        }

    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.


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
  •