Querying multiple SQL tables in one Query

[doodlebug]

Hi guys,

I think this sounds complicated, but there may be an easy solution to it.

I run a football prediction league which has various leagues and within those leagues, mini leagues as well. I have been asked if it is possible to show the mini league table standings on a separate page, so it can be incorporated into other websites as an <iframe> (or preferable rss, but i haven't got that far yet!!).

I have written a piece of code which extracts the top ten standings for the leagues, but i cannot get it to work for the mini leagues. The problem i can't seem to get around, is that the user id's for the mini leagues is stored in a different table to the main league. With me so far???

Here's my code so far:

[code]
$querystand="select lid, userid, position, pld, won, drawn, lost, gfor, gagainst, points from plstandings where lid='2'
order by position asc
limit $num_fixtures";
$resultstand = mysql_query($querystand,$db);

while ($latest_array = mysql_fetch_array($resultstand)) {

$leaugeid= $latest_array["lid"];
$user= $latest_array["userid"];
$position= $latest_array["position"];
$played= $latest_array["pld"];
$won= $latest_array["won"];
$drawn= $latest_array["drawn"];
$lost= $latest_array["lost"];
$gfor= $latest_array["gfor"];
$gagainst= $latest_array["gagainst"];
$points= $latest_array["points"];

[/code]

This code works fine for extracting the necessary information just for league id 2.

Unfortunately, the private league ids and the private members user ids are in a separate table "plprivmembers". What i am trying to get it to do (in logical english) is:

If the league id(lid - in plstandings) =2 and the private league id(pid - in plprivmembers) =6 then select the user ids (userid - from plprivmembers) and extract the users stats from plstandings (as per my code above)

I hope i have made some sort of sense, and am not talking complete gobbledegook!!!!!

Thanks in advance
Neil

[Caesar]

But football season hasn't even started! Do you mean that cute little sport where the guys throw themselves to the ground in hopes of drawing a foul? ;)

On topic, I may have to reread your post to see the logic you're trying to achieve. The complication is probably due to poor database planning.

[doodlebug]

[quote]But football season hasn't even started! Do you mean that cute little sport where the guys throw themselves to the ground in hopes of drawing a foul?

On topic, I may have to reread your post to see the logic you're trying to achieve. The complication is probably due to poor database planning.[/quote]

:D Thats the sport :D :D

I know the season hasn't started but i already have plenty of active users, who always want something different.

Regarding the database, i quite agree that it's not the most logically laid out, but i cheated........the basic bare bones and database structure was a pre-built module, so i can't change it unfortunately.

I hope my 'logical' explanation may be of some help. That's the way i can see what i need to do.......in (my) laymans terms  ;)  I can post some urls (if i'm allowed) to show what the site looks like, and the example of the script that i wrote that works (and the one that doesn't  :()

Neil

[Ninjakreborn]

[code]Regarding the database, i quite agree that it's not the most logically laid out, but i cheated........the basic bare bones and database structure was a pre-built module, so i can't change it unfortunately.[/code]
lesson learned, don't cheat.
Also don't use pre-built databases.
Also no database can't be changed.
If htere is a database, and you can get into it, then you can change it.

[doodlebug]

Too right, lesson learned. As for changing it around, i am still fairly new to php and mysql, and the pre-built part of the script is all defined by variables, and at the moment, i don't think i'm quite up to altering the structure and being able to keep the site working. In the future, once i have a better handle on things, then it pretty much near the top of my list to amalgamate all "17" different tables into something a lot more manageable.

For now though, i have to live with what i have  :(

[Ninjakreborn]

Just use a joint query then

[url=http://www.hudzilla.org/phpbook/read.php/9_9_0]http://www.hudzilla.org/phpbook/read.php/9_9_0[/url]

[doodlebug]

@businessman332211 - Many thanks for the link. Read it a couple of times, it made sense.......and best of all it worked.

Thanks again!!

Neil
[url=http://football-prediction-league.a22.biz]http://football-prediction-league.a22.biz[/url]