SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    North East, England
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with mysql queries

    Hi, I am having trouble getting what I am after from mysql. Here is what I have got and this much works.

    PHP Code:
    $sql = mysql_query("select player.id, player_name, position, team " .
       "from player, team where tid=team.id " .
       "order by team, position");
       
       if(!$sql){
          echo("<p>Error retrieving list of players: " .
          mysql_error() . "</p>");
          exit();
          }


       
    ?>

    <TABLE width="100%">
       <TR align=left>
          <TH>Player</TH>
          <TH>Position</TH>
          <TH>Team</TH>
          <TH>Points</TH>
          <TH>Update</TH>
       </TR>
          
    <?php

          $row 
    1;

       while(
    $list mysql_fetch_array($sql)){
           
    $id=$list["id"];
           
    $player_name=$list["player_name"];
           
    $position=$list["position"];
           
    $team=$list["team"];

           if(
    $row == 0){
               
    $color "#CCCCCC";
           }
           else{
               
    $color "#ffffCC";
           }
           echo(
    "<TR bgcolor=\"$color\">\n<TD>$player_name</TD>\n<TD>$position</TD>\n<TD>$team</TD>\n<TD$points</TD>\n<TD align=center>");
           echo(
    "</TD>\n<TD><a href=\"addplayerpoints.php?id=$id\">[Update]</a></TD>\n");
           
    $row=$row+1;
    }

    ?>

       </TR>
    </TABLE>
    I have another table, player_weekly_points where I need to get the points depending on the players id from the first query. So I would have a second query along the lines of

    "select points from player_weekly_points where pid=$id"

    $id being the id of the player in the first query. I have tried to fit this into the while loop so that the points end up in the table next to the players name but to no avail.

    I would apreciate any help.

    Also, some players may not yet have any points for this week which means there will be no record in the points table. Any ideas how I can overcome this. Will I need to give every player a value of 0 points to start with for every week or what.

    Thanks for any help

    Lee

  2. #2
    PHP warrior dkode's Avatar
    Join Date
    Sep 2001
    Location
    Planet Namek
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would help alot more if you listed what columns are in each of your tables.

    From what I understand you have a total of 3 tables:
    player
    team
    player_weekly_points

    Is this correct?

    If that is the correct then I would simply do 1 query on all 3 tables and match up the pid in each table. It would look something like this:

    Code:
       $query = "SELECT t1.id, t1.player_name, t2.position, t2.team, t3.points
                 FROM player t1, team t2, player_weekly_points t3 WHERE t1.tid=t2.id AND t3.pid=t1.id";
    It would be something along those lines. Again, you didn't specify what your columns are so its harder to understand.
    "Mankind cannot define memory, yet it defines mankind"
    -- Project 2501, Ghost in the Shell

    Smarty | PEAR | PHP Manual | MySQL Manual

  3. #3
    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)
    i'm just trying to figure why your username is michaelsmith, but you put Lee at the end of your post, and you have Chip and Sarah in your signature!
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    North East, England
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My tables are as follows

    player
    id | player_name | position | value | tid

    team
    id | team

    player_weekly_points
    week_no | pid | player points

    The week_no will be set by the referrer page and I have the rest working with this
    PHP Code:
    $sql mysql_query("select player.id, player_name, position, team " .
       
    "from player, team where tid=team.id " .
       
    "order by team, position"); 
    The bit I can't get working is the points. I tried doing it in one query but I couldn't get it to work. Maybe my code stinks or my player_weekly_points table may not be very good.
    Basicly what I am after is, every player will have 0 points for every week until I add points to some players. I want any points belonging to the players, if any, to be displayed with their other details then I can update individual players using their id,s.
    I hope this is clearer.
    Thanks Lee

    i'm just trying to figure why your username is michaelsmith, but you put Lee at the end of your post, and you have Chip and Sarah in your signature!
    My name is Lee. When I registered I figured lee would be taken for a user name so I used my middle and lastname. Chip is representative of every bloke everywhere who likes to watch football but is constantly at odds with his other half (Sarah). This was a campain started by a tv football show on a Saturday morning and my signature is support for the cause.

    Again hope this clears things up
    Lee

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey this query should work, although I have no data to test it with.

    Code:
    select p.id, p.player_name, p.position, t.team, 
    pw.player_points 
    from player p, team t, player_weekly_points pw 
    where p.tid=t.id AND pw.pid = p.id 
    order by t.team, p.position
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    North East, England
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, this seems better. Although I haven't tried it on my script yet I tried it on the dos command line.
    What it did was brought up the only player that has any points. What I am after is for it to bring up every player in the db, not only those with points.

    Also I have never seen something like "p.player" before. Is this just shorthand for the name of the table. Could you give me a quick explanation or maybe a URL to the info.

    Thanks Lee

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes its a way of aliasing your tables. So your from clause looks like
    Code:
    select t1.field, t2.field from table1 t1, table2 t2
    or
    Code:
    select t1.field, t2.field from table1 as t1, table2 as t2
    The as is optional. I like it because its easier to write an alias than that whole tablename everytime. Here is more information.

    http://www.mysql.com/documentation/m...ce.html#SELECT


    On your second question. Now that I think about it, I don't think you can do it with your current tabel structure. Some SQL stud may come along and prove me wrong but I don't think you can.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    North East, England
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok Freddy, if I can't get it to do what I want then its time for a new approach.
    Supposing I get the id from the list of players and use the following query.

    PHP Code:
    $sql mysql_query("select player_name, position, player_points " .
       
    "from player, team, player_weekly_points " .
       
    "where player.id=$id and pid=player.id and team.id=tid"); 
    If I am getting this correctly this query will give me the players' name, position and weekly points.
    If however the player has no points then it will produce an empty set.

    I know how to get the details useing a while loop and mysql_fetch array but what I need to know is how can I get the script to know it is an empty set so that I can use an if, esle statement like

    if(players has points){//do this;
    }
    esle //do that;
    }

    I tried this

    PHP Code:
    <?php

       $dbcnx 
    mysql_connect("host""username""password")
       or die(
    "Unable to connect to the database");
       
       
    mysql_select_db("football");
       
       
    // find out if player has points with his id
       // if so print them.
       
    $sql mysql_query("select player_name, position, player_points " .
       
    "from player, team, player_weekly_points " .
       
    "where player.id=$id and pid=player.id and team.id=tid");
       
       if(!
    $sql){
           echo(
    "<p>Error getting player details. " .
           
    mysql_error() . "</p>");
           exit();
       }
       
       while(
    $details mysql_fetch_array($sql)){
           
    $player_name=$details["player_name"];
           
    $position=$details["position"];
           
    $player_points=$details["player_points"];
           
           if(
    $player_points<1){
               echo(
    "<p>Player has no points</p>");
           }
           else{ 
    // print player details
               
    echo($player_name $position $player_points);
           }
       }
       
    ?>
    When the player had points it printed the players details but otherwise it didn't print anything. I don't know why.

    Thanks for your help
    Lee


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
  •