Jump to content


Photo

One for ye gurus


  • Please log in to reply
13 replies to this topic

#1 TEENFRONT

TEENFRONT
  • Members
  • PipPipPip
  • Advanced Member
  • 338 posts

Posted 10 October 2006 - 06:58 PM

How do work with and retreive data from 2 tables ( how to i retrieve, order, etc ) 

any tuts about?
Teenfront.co.uk : Free Teen Chat -  FunkySmileys.co.uk : MSN Smileys -  ArcadeMonkey.co.uk : Free Flash Games
8Baller.co.uk : Free Multiplayer Pool [url=http://8Baller.co.uk]

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 10 October 2006 - 07:00 PM

How do work with and retreive data from 2 tables ( how to i retrieve, order, etc ) 

any tuts about?


do the tables relate in any way, or is it just two random tables? :D
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 TEENFRONT

TEENFRONT
  • Members
  • PipPipPip
  • Advanced Member
  • 338 posts

Posted 11 October 2006 - 09:05 AM

both tables have identical columns :)
Teenfront.co.uk : Free Teen Chat -  FunkySmileys.co.uk : MSN Smileys -  ArcadeMonkey.co.uk : Free Flash Games
8Baller.co.uk : Free Multiplayer Pool [url=http://8Baller.co.uk]

#4 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 11 October 2006 - 09:40 AM

UNION
<?php

mysql_query("(select * from table WHERE field = '$what') UNION ALL (select * from table WHERE field = '$what') order by field desc");

?>


#5 TEENFRONT

TEENFRONT
  • Members
  • PipPipPip
  • Advanced Member
  • 338 posts

Posted 11 October 2006 - 10:20 AM

oooooooooooooh

il try it straight away... il let you know my progress.

but heres another question first.... needs some explaining..

i have the 2 tables... they have colums "played" and "won" among others.

i want to order it by the players rating, BUT i dont work out the rating untill i have got the played and won values. get my point?

so...simply put..

sql query pulls username, played, won.
i then figure out the players rating ( a ratio of the 2 values) it returns 1,2,3,4 or 5.
i THEN want to show only the top 10 players with a 5 rating.. how on earth do i do that?

if you need me to explain a little further, i will :)


Teenfront.co.uk : Free Teen Chat -  FunkySmileys.co.uk : MSN Smileys -  ArcadeMonkey.co.uk : Free Flash Games
8Baller.co.uk : Free Multiplayer Pool [url=http://8Baller.co.uk]

#6 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 11 October 2006 - 11:23 AM

It's possible, and ideally you'd be better processing it in MySQL and then using an ORDER BY clause.

Can you provide the method of working out the rating based on ratio?

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#7 TEENFRONT

TEENFRONT
  • Members
  • PipPipPip
  • Advanced Member
  • 338 posts

Posted 11 October 2006 - 12:01 PM

yes sure...2secs. i use a custom function

function get_rating($played, $won){
if($played<10){
return 0;
}
$p = round($won/($played/5));
if($p>5){
$p=5;
}
if($p<=0){
$p=0;
}
return $p;
}

$rating = get_rating($played, $won);

echo $rating


Teenfront.co.uk : Free Teen Chat -  FunkySmileys.co.uk : MSN Smileys -  ArcadeMonkey.co.uk : Free Flash Games
8Baller.co.uk : Free Multiplayer Pool [url=http://8Baller.co.uk]

#8 TEENFRONT

TEENFRONT
  • Members
  • PipPipPip
  • Advanced Member
  • 338 posts

Posted 11 October 2006 - 05:43 PM

any further help HuggieBear ?
Teenfront.co.uk : Free Teen Chat -  FunkySmileys.co.uk : MSN Smileys -  ArcadeMonkey.co.uk : Free Flash Games
8Baller.co.uk : Free Multiplayer Pool [url=http://8Baller.co.uk]

#9 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 11 October 2006 - 11:19 PM

I've edited this post as I made a stupid commen t, as for the solution of letting MySQL do the work for you, akitchin has shown you how to do that below

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#10 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 11 October 2006 - 11:49 PM

alright, this sounds like a job for the CASE operator.  it works much like a switch structure:

SELECT
  table1.something AS something,
  table2.played AS played,
  table2.won AS won,
  ROUND((5*won)/played) AS calculated_rating,
  CASE WHEN played < 10 THEN 0 WHEN calculated_rating > 5 THEN 5 ELSE calculated rating END AS actual_rating
FROM table1, table2
WHERE table1.linking_column=table2.linking_column
ORDER BY actual_rating DESC, table1.something_else ASC

some MySQL gurus out there might optimize it, but that SHOULD do what you're after.  it seems a little daunting, but hopefully the spacing and linebreaks help make it clearer.  look up the manual entry for CASE structures (in the functions and operators chapter i think), and give that a whirl.

i should note that i've simplified the "actual" rating determination a little bit.  it should reduce anyone with less than 10 games under their belt to 0, and should simplify anyone with a rating greater than 5 to 5.  however, the rating will never be negative (unless you've got a negative played or won number, which you theoretically shouldn't, unless they REALLY suck) so there's no need to have the <= 0 clause in there.  every other rating (between 0 and 5) is kosher to send back directly.

#11 TEENFRONT

TEENFRONT
  • Members
  • PipPipPip
  • Advanced Member
  • 338 posts

Posted 12 October 2006 - 09:12 PM

holy crap, now thats a query lol.

Il look up the CASE info and il get back to you guys if i get stuck lol. Cheers!
Teenfront.co.uk : Free Teen Chat -  FunkySmileys.co.uk : MSN Smileys -  ArcadeMonkey.co.uk : Free Flash Games
8Baller.co.uk : Free Multiplayer Pool [url=http://8Baller.co.uk]

#12 TEENFRONT

TEENFRONT
  • Members
  • PipPipPip
  • Advanced Member
  • 338 posts

Posted 12 October 2006 - 09:35 PM

iv just thought... maybe theres an easier way...

ok, so columns username, played and won in table1, table2 and table3 ( if you look up you see i need to pull info from multiple tables..table1,table2,table3 all 3 tables have username,played and won columns)

i want to displayed top 10 players. so if username has played 700 and won 700, hes the best. so hes number 1. if username2 has played 650 and won 649, hes number 2 etc. i can forget the rating.

How do i do a query that works that out for me?
Teenfront.co.uk : Free Teen Chat -  FunkySmileys.co.uk : MSN Smileys -  ArcadeMonkey.co.uk : Free Flash Games
8Baller.co.uk : Free Multiplayer Pool [url=http://8Baller.co.uk]

#13 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 13 October 2006 - 03:25 AM

as far as i can tell, you'd want to do something like this:

SELECT table.username AS username, table.played AS played, table.won AS won, ROUND(won / played, 2) AS win_ratio FROM tables_used WHERE stuff ORDER BY win_ratio DESC, played DESC, username ASC

this will grab all users with the highest win ratio first, and in the case of matching win ratios, will choose the one with the highest number of games played first (since i assume seniority ranks).  then it'll sort by username.  this may be oversimplified since it's easy to have a 1.00 win ratio when you're 1 for 1, so you may want to weight it by the number of games played somehow - nothing comes to me off the top of my head (i haven't done stats for a bit), but i'm sure a helpful statistician will pop up soon enough.

EDIT:  after some thought, unless you normalize all your ratios (that is, turn it all into a gaussian), i think the only way to ensure a relatively fair comparison of win ratios is to add a WHERE clause that makes sure the users have played more than x games.

#14 ridiculous

ridiculous
  • Members
  • PipPipPip
  • Advanced Member
  • 175 posts

Posted 13 October 2006 - 05:46 AM

From a learner's standpoint, this is a great thread. Thanks!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users