Comparing data from 2 tables and putting data to third

PHP
1

Hello,

I am working on local project and cant figure out how to do this part. I just started
learning php and its hard for me to write a code from zero so i am looking for
some help here. I appreciate any help about this. Also apologize for my bad

English.

Ok some info about what i want to create.
I have 2 mysql tables. Table1 contains name, sports they like, and number that
indicate how good they are at it. Table2 contains all the sport centers we have with
trainers name and their phone numbers.
I need to create table3 which takes persons sports from table1 but only those who
are in table2 ( e.g Tom can like tennis, but we don’t have trainer for that) and
choose the one with the highest number. Then write to table3 person name, trainer
name of that sport, and phone number.

I hope i explained everything clear, if not ask me. I really need to get this working so thanks for help.
Here are the examples of the tables:

Table1
Name Sport Number
Tom Basketball 88
Tom Diving 48
Tom Driving 82
Tom Volleyball 82
Tom Tennis 99
John Volleyball 75
John Diving 76
Mike Tennis 65
Mike Basketball 70
Paul Football 92

Table2
Sport Contactperson Phone
Basketball Koby 865222111
Diving Tyler 655222444
Swimming Susan 874999555
Voleyball Megie 874333555
Box Tyson 800000000
Football Messi 899999999

Table3
Name Contactperson Phone
Tom Koby 865222111
John Tyler 655222444
Mike Koby 865222111
Paul Messi 899999999

2

I managed to figure out this code. It works, but now i need somehow to choose sport that has biggest number for each person. because now it only display first one listed. Any ideas? I think about MAX() but don’ know how to use it.


$sql = "SELECT table1.name, table1.sport, table2.sport, table2.phone

FROM table1
INNER JOIN table2
ON table1.sport=table2.sport
GROUP BY table1.name
ORDER BY table1.name";
$result2 = mysql_query($sql);
while ($row2 = mysql_fetch_array($result2)) {
echo "name:".$row2{'name'}." sport:".$row2{'sport'}." phone:".$row2{'phone'}."<br>";
}