Bad Union

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Bad Union

Post 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.
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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.*"?
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post by Coco »

sugarmen
Forum Newbie
Posts: 3
Joined: Fri Sep 06, 2002 4:53 pm

Post 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();
?>
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

Don't you have to bracket the two SELECT statement?
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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 :'(
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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 :()
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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;
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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:
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Which version of MySQL do you have? UNION was only introduced as of version 4.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post by Coco »

8O

:cry:

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


*CRYS*
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Okay then,

No UNION :cry:
No SubQueries :cry: :cry:
Post Reply