Jump to content

Archived

This topic is now archived and is closed to further replies.

doodlebug

Querying multiple SQL tables in one Query

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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  :(

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
@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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.