One for ye gurus

[TEENFRONT]

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

any tuts about?

[obsidian]

[quote author=TEENFRONT link=topic=111119.msg450114#msg450114 date=1160506717]
How do work with and retreive data from 2 tables ( how to i retrieve, order, etc ) 

any tuts about?
[/quote]

do the tables relate in any way, or is it just two random tables? :D

[TEENFRONT]

both tables have identical columns :)

[alpine]

UNION
[code]
<?php

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

?>
[/code]

[TEENFRONT]

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

[HuggieBear]

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

[TEENFRONT]

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]

any further help HuggieBear ?

[HuggieBear]

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

[akitchin]

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

[code]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[/code]

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.

[TEENFRONT]

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]

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?

[akitchin]

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

[code]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[/code]

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.

[b]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.[/b]

[ridiculous]

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