SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Apr 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Comparing data from 2 tables and putting data to third.

    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. #2
    SitePoint Member
    Join Date
    Apr 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    $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>";



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •