$result4 = mysql_query("
SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID
AND P.OwnerName = 'Player 2'
UNION
SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID
AND (SELECT Fleets.PID FROM Fleets WHERE Fleets.OwnerName = 'Player 2')
SORT BY PID, FID");
Top half runs fine, havent tested bottom half yet (i have to go out) but all my problems have started when i added the union
i get
Warning: Supplied argument is not a valid MySQL result resource in ...
whenever i do a fetch array
any ideas? i will be testing the bottom half soon as i get back but this is driving me nuts so i posted here to keep me sane til then
ta.
<?php
$result4 = mysql_query("
SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID
AND P.OwnerName = 'Player 2'
UNION
SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID
AND (SELECT Fleets.PID FROM Fleets WHERE Fleets.OwnerName = 'Player 2')
SORT BY PID, FID");
echo mysql_error();
?>
And what's UNION doing between "P.OwnerName = 'Player 2'" and "SELECT P.*"?
<?php
$result4 = mysql_query("
SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID
AND ( P.OwnerName = 'Player 2' or F.Ownername = 'Player 2')
SORT BY PID, FID");
echo mysql_error();
?>
but when you want to use it, try this :
<?php
$result4 = mysql_query("
SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID
AND P.OwnerName = 'Player 2'
UNION
SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID
AND F.PId IN (SELECT Fleets.PID FROM Fleets WHERE Fleets.OwnerName = 'Player 2')
SORT BY PID, FID");
tried it with statements in brackets didnt work...
basically (what i was trying to avoid explaining,) the player has fleets and he has lands.... i need to display ALL fleets (his or otherwise) at any location that he has a fleet at or that he controls.
I have location details in on table, fleet id, owner and location in another...
i cant cobine fleet details into the location table cos more than 1 fleet can be on a location and i cant be arsed trying to code that... thing is that the player can control a place with no fleets and can see a place he doesnt control... i can manage that but it doesnt show other players fleets :'(
<?php
$result4 = mysql_query("
SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID
AND ( P.OwnerName = 'Player 2' or F.Ownername = 'Player 2')
SORT BY PID, FID");
echo mysql_error();
?>
doesnt show other players fleets at places the player DOESNT control (i tried it already )
<?php
$result4 = mysql_query("
(SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID AND P.OwnerName = 'Player 2') UNION( SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID AND (SELECT Fleets.PID FROM Fleets WHERE Fleets.OwnerName = 'Player 2') SORT BY PID, FID)");
?>