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

&lt;?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(); 
?&gt;
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

&lt;?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)");
?&gt;

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
:cry:
i havent a clue where or why
:cry:

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
8O

:cry:

MySQL 3.23.49a-log running on localhost as ...


*CRYS*

Posted: Tue Sep 10, 2002 9:09 am
by mikeq
Okay then,

No UNION :cry:
No SubQueries :cry: :cry: