Struggling to get my query right, can you help?

[PHP_Idiot]

Hi Freaks

 

I'm trying to create a query that will show the results of a poker game.

I have the following tables & Fields in my mysql database:

Player                                Position                                        Results                                      Venue

-MembershipNo                    -Position                                      -ResultID                              - VenueID

-FirstName                          -Points                                        -VenueID                              - VenueName

-NickName                                                                              -MembershipNo                     

-LastName                                                                              -Date

-Address                                                                                  -Position

-Town           

-Postcode         

-Email

 

I have several tables showing total points per player, number of times played, average number of players at each venue, but I can't seem to get this one right.

 

The plan is to have a dropdown list where you can select a date & Venue Name (some venues play on the same date so both checks are needed) then on the selected date I need just the players names, finishing position and points by order of position for that particular venue.

 

So far all I've managed to get is the same players name repeated for ever venue?!

I know this shouldn't be that hard, but I'm stuck on it!

 

Any suggestions would be greatly received!

 

Cheers

 

PHP_Idiot

[premiso]

Can you post the current query you are attempting to use?

[roopurt18]

Your 'Position' table seems extraneous to me.

 

<?php
$date = mysql_real_escape_string( $_POST['date'] );
$venue_id = mysql_real_escape_string( $_POST['venue_id'] );
$select = "
SELECT
  p.`FirstName`, p.`NickName`, p.`LastName`,
  po.`Position`, po.`Points`
FROM `Player` p
INNER JOIN `Results` r ON p.`MembershipNo`=r.`MembershipNo`
INNER JOIN `Venue` v ON v.`VenueID`=r.`VenueID`
INNER JOIN `Position` po ON po.`Position`=r.`Position`
WHERE
    r.`Date`={$date}
    AND v.`VenueID`={$venue_id}
ORDER BY po.`Position`
";
?>

[PHP_Idiot]

Hi Ruupurt

 

Thats absolutely perfect just what I was needing, thanks so much :-)

 

Not got the hang of the joins yet!!

but that really helps

 

Cheers

PHP_Idiot