SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 58
  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i was hoping you would show the data in your text file for the finishing positions

    you know, the data that you thought you'd have to manually insert the golfer id into...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about that, I misunderstood you.

    This is what I have in my .txt files for the finishing position at the moment, but I created this a few weeks ago before I was sure about table structure so I could change as necessary. The fields are tab delimited...is that ok or should I make it comma delimited?

    Brent Geiberger 1 Chrysler Classic of Greensboro 2004-10-17
    Michael Allen 2 Chrysler Classic of Greensboro 2004-10-17
    Chris Smith 3 Chrysler Classic of Greensboro 2004-10-17
    David Toms T4 Chrysler Classic of Greensboro 2004-10-17
    Tom Lehman T4 Chrysler Classic of Greensboro 2004-10-17

    In the second (finishing positions) column T4 stands for Tied 4th btw
    Hope this helps....thanks again.

  3. #28
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, declare a table for this data, and load your data into it

    note that it will have golfer names and tournament names instead of ids

    once it's loaded, you can use joins to pull the ids out of the golfer and tournament tables in order to populate the proper tbl_finish_pos table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #29
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I'll crack on with this during the week.

    Thanks again for all your fantastic help r397, it's much appreciated.

  5. #30
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok so I've now created a temporary table called tbl_temp with a structure:-

    tmp_ID int(10) UNSIGNED No auto_increment
    tmp_TournamentID smallint(5) UNSIGNED No
    tmp_Golfer varchar(40) No
    tmp_Position varchar(10) Yes NULL


    and I've inserted the following details (note I've just used TournamentID rather than the Tournament name and date fields) for the first golf tournament


    tmp_ID tmp_TournamentID tmp_Golfer tmp_Position
    1 1 Andre Stolz 1
    2 1 Harrison Frazar T2
    3 1 Tag Ridings T2
    4 1 Tom Lehman T2
    5 1 Carl Pettersson T5
    6 1 Dicky Pride T5
    7 1 Danny Ellis T7
    8 1 David Frost T7
    9 1 Lee Janzen T7
    10 1 Tim Petrovic T7
    11 1 Bob Estes T11
    12 1 Jim Furyk T11
    13 1 Brian Gay T11
    14 1 Duffy Waldorf T11

    To recap on what I'm trying to do here, I want to be able to get the correct GolferID into the tbl_finish_pos table without having to add the Golfer ID manually to my tournament finishing positions .txt file. The tbl_temp above contains the finishing position results from my .txt file for one golf tournament.

    I know I need to JOIN data from the tbl_Golfers, tbl_Tournaments and tbl_temp tables and then add the results into tbl_finish_pos. I think I need to use a SELECT query and then INSERT the results of the SELECT query into tbl_finish_pos but I can't work out how I use the results in my INSERT query.

    I've been searching through books but can't seem to find a similar example. Once again, I'm sorry if this is a real noddy question but I'm a little unsure about how to go this.

    Any advice much appreciated.

  6. #31
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, an insert statement fed by a select

    why did you bother converting the tournament name to 1?

    you're not going to do a manual conversion for every tournament, are you?

    what happened to the file in post #27?

    that was perfectly usable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #32
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > what happened to the file in post #27?

    I just figured it would be simpler to have a tmp_TournamentID use the TournamentID number (which I'll have already have once I've added the Tournament details into tbl_Tournaments) instead of the Tournament name and date. Does it matter...should I just use my original format for the temp table:-

    Brent Geiberger 1 Chrysler Classic of Greensboro 2004-10-17
    Michael Allen 2 Chrysler Classic of Greensboro 2004-10-17
    Chris Smith 3 Chrysler Classic of Greensboro 2004-10-17
    David Toms T4 Chrysler Classic of Greensboro 2004-10-17
    Tom Lehman T4 Chrysler Classic of Greensboro 2004-10-17

    > yes, an insert statement fed by a select
    Ok, maybe I'm missing something though but I couldn't see how to use the result of a Select query from 3 tables to Insert into a fourth table. I'm sure I'm probably just missing something simple but I'm not sure how to work the syntax. I can do the select
    I think, it's just how to Insert the result of the select query I'm struggling with.

  8. #33
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let's go with the latest --

    tbl_temp
    tmp_ID int(10)
    tmp_TournamentID smallint(5)
    tmp_Golfer varchar(40)
    tmp_Position varchar(10)

    and let's assume we're inserting into --

    tbl_finish_pos
    GolferID smallint
    TournamentID smallint
    Position varchar(10)

    and we have this table to refer to --

    tbl_golfers
    ID smallint
    Golfer varchar(40)

    so here's the query --
    Code:
    insert 
      into tbl_finish_pos
         ( GolferID 
         , TournamentID
         , Position )
    select g.ID
         , t.tmp_TournamentID
         , t.tmp_Position
      from tbl_temp as t
    inner
      join tbl_golfers as g
        on t.tmp_Golfer
         = g.Golfer
    we could've looked up the tournament id with an additional join just as easily as we looked up the golfer id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #34
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've just got back to working on this again after being sidetracked by work on another site I'm building. I wonder if I could pick your brains again:-

    I've now successfully imported all my golf finishing positions data into tbl_finish_pos so that entries look like this:-

    GolferID TournamentID Position
    11 1 1
    143 1 2
    363 1 3
    185 2 1

    ....etc

    In post #5 of this thread R937 gave this syntax for displaying the last 5 tournament finishes for a particular golfer on the US PGA Golf Tour.

    select F.Position
    , T.Tournament
    , T.T_Date
    from tbl_tournaments as T
    left outer
    join tbl_fp as F
    on T.ID
    = F.TournamentID
    and F.GolferID = 937
    where T.Tour = 'US'
    and 5
    <= ( select count(*)
    from Tournaments
    where Tour = 'US'
    and T_Date >= T.T_Date )

    Actually I've amended tbl_fp to tbl_finish_pos (the table name I'm using now) and Tournaments to Tournament as thats the field name in tbl_tournaments:-

    select F.Position
    , T.Tournament
    , T.T_Date
    from tbl_tournaments as T
    left outer
    join tbl_finish_pos as F
    on T.ID
    = F.TournamentID
    and F.GolferID = 937
    where T.Tour = 'US'
    and 5
    <= ( select count(*)
    from Tournament
    where Tour = 'US'
    and T_Date >= T.T_Date )


    but when I run this query in phpmyadmin I just get "error in syntax" messages returned.

    Does anyone have any thoughts on what's going wrong here?

    I'm also a little unclear about the final part:-
    and 5
    <= ( select count(*)
    from Tournament
    where Tour = 'US'
    and T_Date >= T.T_Date )

    I mean Tournament or Tour don't have a table allocated to them (ie T.Tournament or T.Tour) How does mysql know which table you are referring to when you just use field names like this? My knowledge of the syntax is pretty basic at the moment so I'm just trying to understand why it's written that way.

    I'd also like to be able to display results for a group of golfers alphabetically by first name where the user selects either US or EURO tours from a drop-down-down menu and then either A-C, D-F, G-J etc from another dropdown for the first name of the golfer.

    Thanks in advance for any help you can provide...I really appreciate it.

  10. #35
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one question at a time, okay?

    what is the exact error you get?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #36
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure, sorry I was getting ahead of myself!

    Actually it says:-

    "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count( * ) FROM Tournament WHERE Tour = 'US' AND T_D

    and it stops there coz the error is output all on one line and it's like the phymyadmin can't print any more characters.

    I should have read this properly, once I saw the "error in syntax" thing I assumed it was just a generic error message and didn't follow the text along. So atleast that gives me something to go on. My webhost has MYSQL server v4.0.15 and phpmyadmin v2.3.3 pl1. Any thoughts?

    I'll have a look through the docs and see if I can find anything.

    Thanks for getting back so quickly r937

  12. #37
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aha, well, that particular error message indicates that mysql can't handle the subquery

    likely you are not on the current production release 4.1 yet?

    if you can't upgrade, you'll have to re-think what you want to retrieve
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #38
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks.

    Well thats not good news. I need to be able to retrieve last 5 tournament results on say the US Tour for all players whose first name begins with the letter A-C or D-F for example.

    Alternatively, or ideally in addition, I'd like the user to be able to enter two golfers names in seperate form fields and the page then display each golfers finishing position for the last 5 tournaments on say the European Tour. A "-" or something similar would signify that the player(s) selected didn't appear in a particular tournament

    Another option might be to display the last 5 tournament finishing positons for the two golfers entered, where both golfers played in all 5 tournaments.

    The above is just to give you an idea of the sort of things I'm looking to do over time. Initially just being able to display one of these criteria would be fine. Would anyone of these be possible do you think based on that error in syntax message if my webhost won't upgrade?

    I'll email my webhost to find out - if they will upgrade mysql for me, would my existing tables still be usable or would I need to start from scratch?

    Cheers.

  14. #39
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your table design is not related to the version of mysql you're on

    not being on 4.1 just means you can't use subqueries

    this in turn means you have to re-think how you go about approaching the data retrieval for certain types of request

    without going back over this thread and reviewing all the table designs and queries (that's too much like work, and free answers should not require work), i would imagine that "last 5 tournament results on say the US Tour for all players whose first name begins with the letter A-C or D-F" is not a simple SELECT query (it requires the "last 5" subquery), but listing all tournament results for each player selected is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #40
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately I need to limit the number of tournaments, I figure about 5 is the max I can display on the width of page I'm working with.

    I'll see what my webhost has to say about an upgrade. I'm not hugely optimistic to be honest but you never know. If they don't then I guess I'll have to switch webhost :-(

    Thank you very much for all your time and effort r937

  16. #41
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, retrieving all tournaments for a given golfer in a given year cannot really tax your server all that much

    rather than jump through hoops with multiple queries and temp tables, i'd be inclined to retrieve them all and just show the first 5

    query is simple, and looping over the results you can pull off the first 5 easily

    as for showing 5 horizontally, i'd show them vertically

    not everyone is on a 2400*1800 monitor, you know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #42
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok that sounds good....thanks for that.

    What I was thinking regarding display was this. User selects US Tour and Golfers A-C.
    I now want to pull up the last 5 touraments on the US Tour. Any golfer whose first name begins with the letter A, B or C will be listed down vertically (ie in rows) down the left side of the html table and the 5 tournament names will be listed horizontally in columns across the top of the table. A listed golfer will be marked with a dash (or similar) if they did not play in any of the 5 tournaments listed, otherwise their finishing position will display in the table cell

    So basically as there could be dozens of players in the A-C range I need to display the golfers vertically and tournaments (as there's only 5 max) horizontally.

    Now here's where I'm at. I've written a php page:-

    http://www.golfbettingguide.com/grahamgolftest.php

    It connects to the database fine and I've successfully dispayed some very basic queries like listing every golfer in the golfer table. Now I've run into an error message when trying to run a query to list the previous 5 US Tour tournament results for V J Singh (GolferID 391). In phymyadmin this query executes successfully:-

    SELECT Position, Tournament, T_Date
    FROM tbl_tournaments
    LEFT OUTER JOIN tbl_finish_pos ON tbl_tournaments.ID = tbl_finish_pos.TournamentID AND tbl_finish_pos.GolferID = 391
    WHERE tbl_tournaments.Tour = 'US'
    LIMIT 0, 30

    producing result:-
    Position Tournament T_Date
    NULL Michelin Championship 2004-10-10
    NULL Chrysler Classic of Greensboro 2004-10-17
    T2 Funai Classic 2004-10-24
    1 Chrysler Championship 2004-10-31
    NULL Shinhan Korea Championship 2004-11-28

    NULL appears for tournaments Singh didn't play in.

    However when I run the same query in my php page:

    $result = @mysql_query('SELECT Position, Tournament, T_Date
    FROM tbl_tournaments
    LEFT OUTER JOIN tbl_finish_pos ON tbl_tournaments.ID = tbl_finish_pos.TournamentID AND tbl_finish_pos.GolferID = 391
    WHERE tbl_tournaments.Tour='US' LIMIT 0, 30 ');
    if(!$result){
    die('<p>Error performing query: ' . mysql_error() . '</p>');
    }

    //Display result on page in a paragraph
    while ($row = mysql_fetch_array($result) ){
    $fp = $row['F.Position'];
    $Tournament = $row['T.Tournament'];
    $TournamentDate = $row['T.T_Date'];

    echo("<p>$fp<br />$Tournament<br />$TournamentDate</p>");
    }

    I get :-
    Parse error: parse error in /pub/home/gbg141/htdocs/grahamgolftest.php on line 24

    Line page 24 being :-
    WHERE tbl_tournaments.Tour='US' LIMIT 0, 30 ');

    Btw, I've only removed your table aliases because I was clutching at straws and wondered if I needed to name the tables explictily in my Select Query.

    Do you have any idea why this query is producing this syntax error? As I say it works fine in phpmyadin and I've tried several changes but no dice.

    Thank you for bearing with me on this...I'm sorry to be such a pain :-)

  18. #43
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just noticed that I'm still using your aliases in my code to display the results. This isn't what's causing the error though as I still had the line 24 error before I removed the aliases in the select query.

    Just thought I'd mention that to avoid any confusion.

    Cheers.

  19. #44
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you neeed to escape the single quotes around 'US' like this

    PHP Code:
     $result = @mysql_query('SELECT Position, Tournament, T_Date
      FROM tbl_tournaments 
      LEFT OUTER JOIN tbl_finish_pos  ON tbl_tournaments.ID = tbl_finish_pos.TournamentID AND tbl_finish_pos.GolferID = 391
      WHERE tbl_tournaments.Tour= \'US\' LIMIT 0, 30 '
    ); 

  20. #45
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes thats done it....thanks swdev.....much appreciated

  21. #46
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another question to bug you database wizards with I'm afraid :-)

    In post #42 above the results from the sql query contain some tournaments the golfer (in this case, V J Singh) didn't play in and NULL is output. Now when I run

    http://www.golfbettingguide.com/grahamgolftest.php

    the page executes ok but nothing is displayed. I'm guessing this is because of the NULL's for the finishing position - they won't be displayed and so nothing is output. It's like the 3 variables $fp, $Tournament and $TournamentDate aren't written to with any data. Here's the php:-

    $result = @mysql_query('SELECT Position, Tournament, T_Date
    FROM tbl_tournaments
    LEFT OUTER JOIN tbl_finish_pos ON tbl_tournaments.ID = tbl_finish_pos.TournamentID AND tbl_finish_pos.GolferID = 391
    WHERE tbl_tournaments.Tour= \'US\' LIMIT 0, 30 ');

    while ($row = mysql_fetch_array($result) ){
    $fp = $row['F.Position'];
    $Tournament = $row['T.Tournament'];
    $TournamentDate = $row['T.T_Date'];

    echo("<p>$fp<br />$Tournament<br />$TournamentDate</p>");
    }

    So I have a couple of questions here:-
    1) Are the NULL's the reason nothing is output between the html tags?
    2) Is it possible in php to replace any NULL with say a hyphen and output that instead?

    Just to refresh, I want to display the last 5 tournament finishing positions on the US Tour for a particular golfer. If the golfer didn't play in any of the last 5 tour events, then I need to be able to display a hyphen or zero in the finishing position part of the results for the tournament the player was absent from.

    Also, just in case this is relevant, when I enter the finishing position data for a particular tournament in the database, I only enter those golfers who have actually played in the tournament.

    I hope this makes sense and thanks in advance to anyone who can assist.

  22. #47
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1) no

    i don't write php but i can see you are referencing table prefixes in the $row array

    table prefixes are not part of the result set


    2) yes, use the coalesce function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #48
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) Now working, thanks.

    2) Great...I'll use Coalesce() to replace the NULL's with a dash

    Once again, many thanks.

  24. #49
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could someone look at the following code please and let me know where I'm going wrong:-

    if(isset($_POST['golferentry']))
    {


    $golferentry = $_POST['golferentry'];
    $tour = $_POST['tour'];
    $result = @mysql_query('SELECT G.Golfer, T.Tournament, T.T_Date, F.Position FROM tbl_tournaments AS T,
    tbl_golfers AS G LEFT OUTER JOIN tbl_finish_pos AS F ON T.ID = F.TournamentID AND F.GolferID = G.ID
    WHERE T.Tour = $tour AND G.Golfer = $golferentry');

    if(!$result)
    {
    die('<p>Error performing query: ' . mysql_error() . '</p>');
    }

    //Display result on page in a paragraph
    while ($row = mysql_fetch_array($result) )
    {
    $Golfer = $row['Golfer'];
    $fp = $row['Position'];

    if(!isset($fp))
    {
    // $var is null (does not exist at this time) and the execution goes here
    $fp = "-";
    }

    $Tournament = $row['Tournament'];
    $TournamentDate = $row['T_Date'];

    echo("<p>$Golfer<br />$fp<br />$Tournament<br />$TournamentDate</p>");
    }

    }


    The $tour and $golferentry values are entered in a rough-and-ready form I've created at:-

    http://www.golfbettingguide.com/content/golfer-form.php

    If you enter a golfer's name (I've been using "Tiger Woods" (without quotes) ) then I get the error message

    Error performing query: Unknown column '$tour' in 'where clause'

    I've looked through some books on this and I've seen this syntax used in the WHERE clause, and I can't figure out what's wrong with it.

    Anyone have any ideas?

  25. #50
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here's your problem --

    ... WHERE T.Tour = $tour AND G.Golfer = $golferentry

    whatever you put in for $tour, unless it's a number, the database thinks it's a column name

    you probably want single quotes around $tour

    that would make it a string, and you can then search for any rows where T.Tour has that string value

    might want quotes around $golferentry too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •