Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post

    query inside a query

    i have this code that connects to a table and finds the 100 rows with the players names in them and their rank #s... but when i connect to another table to find that player's ID# for their official website profile entry, it doesnt show all 100 people at the end... just the last result (#100).... can someone help?

    PHP Code:
    <?php
    $con 
    mysql_connect("localhost","user","pass");
    mysql_select_db("user_pp",$con);
    echo 
    "<font face='arial' size='2'>";

    $sql mysql_query("SELECT * FROM `newtop100` ORDER BY `rank` ASC") or die(mysql_error());
    while(
    $row mysql_fetch_array($sql)){
        
    $name $row['name'];
        
    $sqlv mysql_query("SELECT * FROM `pp_players` WHERE `name` = '$name' LIMIT 1") or die(mysql_error());
        while(
    $result mysql_fetch_array($sqlv)){
            if(empty(
    $number) || $number == ""){
                
    $number 1;
            }else{
                
    $number $number+1;
            }
            echo 
    "#$number -> <b><a href='player.php?type=pp&id=".$result['player_id']."'>$name</a></b><br>";
        }
    }

    echo 
    "</font>";
    ?>

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    why don't you simply join the two tables to do it with one select instead of your current 101 selects?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New to the CF scene
    Join Date
    Sep 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool

    hehe..exactly!


    Paul
    _________________________________________
    We Make IT Simple!
    Email: paul@niveux.net
    Last edited by paulsiew; 09-27-2004 at 06:59 PM.

  • #4
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    because they are 2 separate systems... the table that has all the players' data is set aside for PP/PPPs and AllPlayers database... the Top100 is just a selection of 100 of those 1000 players... and i want it so that i have the names and 1-100 rank # in the top100 table and it finds the players' college info, position, height, weight, etc from the allplayers table

  • #5
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    well, ive also tried making my own "resync" thing because what we do is update a txt file, upload it to the server and i made a script that gets the new top100 list from the txt file and inserts into the new table... and i tried adding the fields for height, weight, school, etc in it and stuff, but it still only puts in the data from the new queries for the #100 for all of them

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i don't understand your problem.

    what do you mean by
    it still only puts in the data from the new queries for the #100 for all of them
    Puts in the data? where?

    and i also don't understand why you shouldn't be able to use a join, since
    PHP Code:
    $sql mysql_query("SELECT newtop100.player_id, newtop100.name FROM newtop100 INNER JOIN pp_players ON newtop100.name=pp_players.name ORDER BY newtop100.rank ASC") or die(mysql_error()); 
    $number '0';
    while(
    $row mysql_fetch_assoc($sql)){ 
       
    $number ++;
       echo 
    '#' $number ' -> <b><a href="player.php?type=pp&id="' $row['player_id']. '">' $row['name']. '</a></b><br>'

    would deliver exactly the same (only a whole lot more efficiently)
    Last edited by raf; 09-28-2004 at 10:12 AM.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #7
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    ok, here's what happens... it selects all 100 names and stuff correctly from newtop100 but the other query for each row is supposed to get the info from the other database inside that where($row = mysql_fetch_array($result)){} area... but it just shows all 100 names with their player info and height weight etc is all only the info for #100... for all of them

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Did you even try the code i posted?
    it selects all 100 names and stuff correctly from newtop100 but the other query for each row is supposed to get the info from the other database inside that where($row = mysql_fetch_array($result)){} area...
    info from the other database? You don't select a second database. Both selects run on two joinable tables from the same database. So there realy is no need to have a nested query.
    but it just shows all 100 names with their player info and height weight etc
    must be some kind of miracle because your code doesn't output players info like the height and weight
    etc is all only the info for #100... for all of them
    you don't output the info in the code you posted. you only output the name inthere (which apparantly is correct) so how should we know? You're probably outputting the info after the code you posted --> it needs to be echoed inside the while-loop, else $result will contain the info from the last player.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #9
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    yep all it said was "could not connect to table.field. user not allowed"

  • #10
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I'll try once more : in your code, you only open one connection and you only select one database and the two tables are inside the sam database, so there is no reason to have a nested query.

    Did you by any chance forget to include your connectionstring? The code i posted is for the querying and displaying of the results, but you still need the connectionstrings of course. Complete code would be:
    PHP Code:
    <?php 
    $con 
    mysql_connect('localhost','user','pass'); 
    mysql_select_db('user_pp',$con); 
    echo 
    '<font face="arial" size="2">'
    $sql mysql_query("SELECT newtop100.player_id, newtop100.name FROM newtop100 INNER JOIN pp_players ON newtop100.name=pp_players.name ORDER BY newtop100.rank ASC") or die(mysql_error());  
    $number '0'
    while(
    $row mysql_fetch_assoc($sql)){  
       
    $number ++; 
       echo 
    '#' $number ' -> <b><a href="player.php?type=pp&id="' $row['player_id']. '">' $row['name']. '</a></b><br>';  
    }  

    echo 
    '</font>'
    ?>
    + whatever code you use to display the height and weight
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #11
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    grr... that code returns
    "#1 -> Boomer Grigsby"

    and Boomer Grigsby is #100 in the database

  • #12
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    nvm... that was my problem... now it says "Not unique table/alias: 'pp_players'"


  •  

    Posting Permissions

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