Page 1 of 1
Bad Union
Posted: Mon Sep 09, 2002 11:42 am
by Coco
anything wrong with this? :
Code: Select all
$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.
Posted: Mon Sep 09, 2002 11:46 am
by Takuma
Try this and tell us what it saids
Code: Select all
<?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.*"?
Posted: Mon Sep 09, 2002 11:49 am
by Coco
Posted: Mon Sep 09, 2002 11:59 am
by sugarmen
I don't understand why you are use UNION !?
<?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");
echo mysql_error();
?>
Posted: Mon Sep 09, 2002 12:14 pm
by Takuma
Don't you have to bracket the two SELECT statement?
Posted: Mon Sep 09, 2002 5:06 pm
by Coco
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 :'(
Posted: Mon Sep 09, 2002 5:13 pm
by Coco
You have an error in your SQL syntax near '(SELECT P.*, F.* FROM Planet AS P, Fleets AS F WHERE P.PID = F.PID AND P.Owner' at line 2....
*stares at line 2*
sugarmen...
Code: Select all
<?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

)
Posted: Tue Sep 10, 2002 1:21 am
by Takuma
How about this?
Code: Select all
<?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)");
?>
Posted: Tue Sep 10, 2002 3:52 am
by Coco
ok i finally tested the second part on its own and it didnt work...
so there seems to be a problem with the subquery
i havent a clue where or why

Posted: Tue Sep 10, 2002 4:51 am
by mikeq
Which version of MySQL do you have? UNION was only introduced as of version 4.
Posted: Tue Sep 10, 2002 4:55 am
by mikeq
I take it they have also introduced sub-queries in version 4, as you couldn't use them in previous versions.
Just checked and it is version 4.1 which will support them.
Posted: Tue Sep 10, 2002 8:37 am
by Coco
MySQL 3.23.49a-log running on localhost as ...
*CRYS*
Posted: Tue Sep 10, 2002 9:09 am
by mikeq
Okay then,
No UNION
No SubQueries
