...

View Full Version : query inside a query



boeing747fp
09-25-2004, 02:41 PM
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
$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>";
?>

raf
09-27-2004, 09:23 AM
why don't you simply join the two tables to do it with one select instead of your current 101 selects?

paulsiew
09-27-2004, 06:53 PM
hehe..exactly! :)


Paul
_________________________________________
We Make IT Simple! (http://www.niveux.net)
Email: paul@niveux.net

boeing747fp
09-27-2004, 08:41 PM
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

boeing747fp
09-28-2004, 12:21 AM
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 :confused:

raf
09-28-2004, 10:06 AM
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


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

boeing747fp
09-28-2004, 09:39 PM
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

raf
09-29-2004, 07:36 AM
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.

boeing747fp
09-29-2004, 09:02 PM
yep all it said was "could not connect to table.field. user not allowed"

raf
09-30-2004, 07:05 AM
:confused:
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
$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

boeing747fp
10-01-2004, 08:18 PM
grr... that code returns
"#1 -> Boomer Grigsby"

and Boomer Grigsby is #100 in the database :confused:

boeing747fp
10-01-2004, 08:33 PM
nvm... that was my problem... now it says "Not unique table/alias: 'pp_players'"



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum