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...
| SitePoint Sponsor |
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...
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.
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
Ok, I'll crack on with this during the week.
Thanks again for all your fantastic help r397, it's much appreciated.
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.
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
> 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.
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 --
we could've looked up the tournament id with an additional join just as easily as we looked up the golfer idCode: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
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.
one question at a time, okay?
what is the exact error you get?
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
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
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.
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
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
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
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 :-)
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.
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 ');
Yes thats done it....thanks swdev.....much appreciated
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.
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
1) Now working, thanks.
2) Great...I'll use Coalesce() to replace the NULL's with a dash
Once again, many thanks.
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?
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
Posting Permissions
Bookmarks