Comparing data from 2 tables and putting data to third. Is it posible?
Results 1 to 9 of 9

Thread: Comparing data from 2 tables and putting data to third. Is it posible?

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    5

    Comparing data from 2 tables and putting data to third. Is it posible?

    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
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,874
    you could use a JOIN query to connect table 1 to table 2:
    Code:
    SELECT t1.Name, t2.Contactperson, t2.Phone, COUNT(t1.Name) AS num
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t2.Sport=t1.Sport
    GROUP BY t1.Name
    ORDER BY num DESC
    Not positive that will actually work, but I don't think you need to define another table. I imagine that 3rd table might get out of date quickly.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  3. #3
    Junior Member
    Join Date
    Apr 2013
    Posts
    5
    [syntax="php" ]
    $sql = "SELECT table1.name, table1.sport, table2.sport
    FROM table1
    INNER JOIN table2
    ON table1.sport=table2.address
    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'}." lat:".$row2{'phone'}."<br>";
    }
    [ /syntax]

    Thanks. With your help i got this part working but now i still need somehow to choose sport with highest number for each person. Because now its choose the first one listed. Any ideas? i am thinking about MAX() but not use if its right and how to use it for every person.

  4. #4
    Junior Member
    Join Date
    Apr 2013
    Posts
    5
    Thanks. I tried what you said and got 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>";


  5. #5
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,874
    You are using a different query than I did. I tried to write my query to take into account the counting and grouping and your query does not.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  6. #6
    Junior Member
    Join Date
    Apr 2013
    Posts
    5
    PHP Code:
    $sql "SELECT t1.name, t1.sport, t2.sport, t2.Contactperson, t2.phone, COUNT(t1.Name) AS num
    FROM table1 t1
    INNER JOIN table2 t2
    ON t1.sport=t2.sport
    GROUP BY t1.name
    ORDER BY num DESC"
    ;
    $result2 mysql_query($sql);
    while (
    $row2 mysql_fetch_array($result2)) {
    echo 
    "name:".$row2{'name'}." Contactperson:".$row2{'Contactperson'}." phone:".$row2{'phone'}."<br>";


    Oh sorry i took counting away because it wasn't working for me but now i found a mistake and now its working fine and its like in your code given. Maybe you can help me again. I still need to choose sport with the highest number because now it gives the first one listed. (I think i repeating myself sorry)

  7. #7
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,874
    Quote Originally Posted by Hossu View Post
    Oh sorry i took counting away because it wasn't working for me but now i found a mistake and now its working fine and its like in your code given. Maybe you can help me again. I still need to choose sport with the highest number because now it gives the first one listed. (I think i repeating myself sorry)
    I'm not sure what is wrong from what you posted there.

    I'm surprised that this query would give only one result, but that might be possible depending on the data in your database. If you have multiple names in table1 that have matches in table 2, you should get multiple records back. It should list names in order of how many sports that are liked by that name.

    if you want to sort by the sports with the most likes, change COUNT(t1.Name) to COUNT(t1.sport) and GROUP BY t1.name to GROUP BY t1.sport.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  8. #8
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,885
    Incidentally, the insertion could be done at the same time as the selection by using a INSERT ... SELECT statement.

    COUNT(t1.Name) AS num
    Shouldn't the ordering be on the 'Number' field in table2? That's how I'm interpreting "and
    choose the one with the highest number." And something that hasn't been defined is, which record should be returned if there are two with the highest number?
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  9. #9
    Junior Member
    Join Date
    Apr 2013
    Posts
    5
    Quote Originally Posted by Weedpacket View Post
    Shouldn't the ordering be on the 'Number' field in table2? That's how I'm interpreting "and
    choose the one with the highest number." And something that hasn't been defined is, which record should be returned if there are two with the highest number?
    Yes it should choose sport with highest number but that sport has to be in table2.
    e.g. Tom have tennis with number 88, basketball with number 77, and volleyball with number 66 . So we need to choose basketball because there is no tennis in table2. And if there are two with the highest number is it possible to return random 1 of them?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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