Subquery in PHP
Solution 1
Something like this should work.
<?
$sql = "SELECT Team from PLAYERS
JOIN USERS on (Id_player=Id)
WHERE Number BETWEEN $minID AND $maxID
AND City='London'
GROUP BY Team";
$results=mysql_query($sql) or die(mysql_error());
// $results contain all the teams from London
// Use like normal..
echo "<ul>\n";
while($team = mysql_fetch_array($results)){
echo "\t<li>{$team['Team']}</li>\n";
}
echo "</ul>";
Solution 2
SELECT PLAYERS.*, USERS.City FROM PLAYERS, USERS WHERE USERS.City='London' AND PLAYERS.Number = $i
Not the best way to do it; maybe a LEFT JOIN
, but it should work. Might have the syntax wrong though.
James
EDIT
WARNING: This is not the most ideal solution. Please give me a more specific query and I can sort out a join
query for you.
Taking your comment into account, let's take a look at another example. This will use PHP to make a list we can use with the MySQL IN
keyword.
First, make your query:
$res1 = mysql_query("SELECT Id from USERS where City='London'");
Then, loop through your query and put each Id
field one after another in a comma seperated list:
$player_ids = ""; while($row = mysql_fetch_array($res1)) { $player_ids .= $row['Id'] . ","; } $player_ids = rtrim($player_ids, ",");
You should now have a list of IDs like this:
12, 14, 6, 4, 3, 15, ...
Now to put it into your second query:
for($i = 0; $i<something; $i++) { $res2 = mysql_query("SELECT Team from PLAYERS WHERE Number=$i AND Id_Player IN $player_ids"); }
The example given here can be improved for it's specific purpose, however I'm trying to keep it as open as possible.
If you want to make a list of strings, not IDs or other numbers, modify the first while
loop, replacing the line inside it with
$player_ids .= "'" . $row['Id'] . "',";
If you could give me your actual query you use, I can come up with something better; as I said above, this is a more generic way of doing things, not necessarily the best.
Solution 3
Placing SQL quires in loops can be very slow and take up a lot of resources, have a look at using JOIN in you SQL. It's not that difficult and once you've got the hang of it you can write some really fast powerful SQL.
Here is a good tutorial worth having a look at about the different types of JOINs:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php
Admin
Updated on June 04, 2022Comments
-
Admin almost 2 years
Let's put an easy example with two tables:
USERS (Id, Name, City) PLAYERS (Id_Player, Number, Team)
And I have to do a query with a subselect in a loop, where the subselect is always the same, so I would like to divide it into two queries and put the subselect outside the loop.
I explain. What works but it is not optimize:
for($i=0;$i<something;$i++) { $res2=mysql_query("SELECT Team from PLAYERS WHERE Number=$i AND Id_Player IN (SELECT Id FROM USERS WHERE City='London')"); }
What I would like to do but it doesn't work:
$res1=mysql_query("SELECT Id from USERS where City='London'"); for($i=0;$i<something;$i++) { $res2=mysql_query("SELECT Team from PLAYERS WHERE Number=$i AND Id_Player IN **$res1**"); }
Thanks!