[SOLVED] INNER JOIN - Rows

[DeanWhitehouse]

Can you use a query that uses an inner join and still loop through the rows.

 

Here is my attempt

<?php
$query = mysql_query("SELECT * FROM city ORDER BY city DESC");
while($row = mysql_fetch_assoc($query))
{
$sql = "
SELECT 
blackjack.max_bet,blackjack.owner_id,blackjack.city_id,
coinflip.max_bet,coinflip.owner_id,coinflip.city_id,
keno.max_bet,keno.owner_id,keno.city_id,
roulette.max_bet,roulette.owner_id,roulette.city_id,
slots.max_bet,slots.owner_id,slots.city_id,
war.max_bet,war.owner_id,war.city_id,
airport.crew_id,airport.id,
bulletfactory.crew_id,bulletfactory.price,bulletfactory.city_id,
ironworks.price,ironworks.crew_id,ironworks.city_id
FROM blackjack 
INNER JOIN coinflip ON coinflip.city_id = '".$row['id']."'
INNER JOIN keno ON keno.city_id = '".$row['id']."'
INNER JOIN roulette ON roulette.city_id = '".$row['id']."'
INNER JOIN war ON war.city_id = '".$row['id']."'
INNER JOIN slots ON slots.city_id = '".$row['id']."'
INNER JOIN bulletfactory ON bulletfactory.city_id = '".$row['id']."'
INNER JOIN airport ON airport.id = '".$row['id']."'
INNER JOIN ironworks ON ironworks.city_id = '".$row['id']."'
";
$sql = mysql_query($sql) or die(mysql_error());
while($rw = mysql_fetch_assoc($sql))
{
	print_r($rw);
}
}
?>

 

That prints nothing :s

[.josh]

Nothing wrong with your code.  Must be one of your queries.

 

echo $sql;

 

in your outer loop, see if it's holding what you expect (the $row vars).  c/p it directly into your db, see if it returns expected results.

 

 

[.josh]

actually, I looked at your first query:

 

$query = mysql_query("SELECT * FROM city ORDER BY city DESC");

 

That query is probably wrong.  I doubt your table AND column names are both named 'city'

[DeanWhitehouse]

They are

 

I found the problem, in the big query using the joins not all the tables have anything in therefore it will return no results :s

[DeanWhitehouse]

Another problem, most of the tables in the query have the same column names

 

How do i properly reference them later on?

[RussellReal]

ok your query is messy..

 

try this.. I'm not sure it'll work.. but it looks like it should:

 

$sql = "SELECT *.city_id, *.max_bet, *.owner_id
FROM blackjack
JOIN (coinflip,keno,roulette,war,slots,bulletfactory,airport,ironworks)
ON (
coinflip.city_id = keno.city_id,
keno.city_id = keno.city_id,
roulette.city_id = keno.city_id,
war.city_id = keno.city_id,
slots.city_id = keno.city_id,
bulletfactory.city_id = keno.city_id,
airport.city_id = keno.city_id,
ironworks.city_id = keno.city_id
)
WHERE keno.city_id = '{$row['id']'";