Jump to content


Photo

Querying multiple SQL tables in one Query


  • Please log in to reply
6 replies to this topic

#1 doodlebug

doodlebug
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 28 July 2006 - 08:13 PM

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:

$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"];


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

#2 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 28 July 2006 - 09:11 PM

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.
PHP Ninja

#3 doodlebug

doodlebug
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 28 July 2006 - 09:22 PM

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.


: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

#4 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 28 July 2006 - 09:24 PM

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.
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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#5 doodlebug

doodlebug
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 28 July 2006 - 09:31 PM

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  :(

#6 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 28 July 2006 - 09:33 PM

Just use a joint query then

http://www.hudzilla..../read.php/9_9_0

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#7 doodlebug

doodlebug
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 28 July 2006 - 10:06 PM

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

Thanks again!!

Neil
http://football-pred...-league.a22.biz




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users