code country1 country
(1) Japan China
(2) France China
(3) Germany France
(4) Germany Japan
(5) China Japan
(6) France Germany
(7) Japan France[/code]I have a table like the above.
I like to produce my target result below with “France”
Japan
Germany
China
The code below produces the result below.
code
SELECT * FROM (SELECT 'France', country2 as country
FROM myTable
WHERE country1='France'
ORDER BY id) t1
UNION
SELECT * FROM (SELECT 'France', country1 as country
FROM myTable
WHERE country2='France'
ORDER BY id) t2
result
China
Germany
Japan
I expect my target result above with the code below.
SELECT * FROM (SELECT 'France', country2 as country
31 FROM myTable
32 WHERE country1='France'
33 ORDER BY id desc) t1
34 UNION
35 SELECT * FROM (SELECT 'France', country1 as country
36 FROM myTable
37 WHERE country2='France'
38 ORDER BY id desc) t2
But the code above produces the target below.
Germany
China
Japan
I want my target result below.
Japan
Germany
China
How can I get my target result above?
[code]$list=mysql_query(“select min(id) as id,
country
from (select id,
case when country1 = ‘Japan’ then country2 else country1 end
from myTable
where ‘Japan’ in (country1,country2)) dt(id,country)
group
by country
order
by id”);
while($rows=mysql_fetch_array($list)) { $id=$rows[‘id’];
echo $id.‘ ’;
}[/code]I tried the code above which is applied with swampBoogie’s solution.
But it produces the error "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource ".
How can I fix it?
And I don’t understand the meaning of dt in dt(id, country).
I don’t know the way how to run the query outside of php to see the actual mysql error.
How can I make the way to run the query outside of php to see the actual mysql error?
[quote=“r937, post:6, topic:263225”]
how many years have you been working with mysql?
that might sound harsh to anyone not familiar with your history here on sitepoint but sheesh, come on, man
[/quote]I am afraid that I don’t clearly understand your thought and feeling by the quote above due to maybe I am not a English native.
Although I have my own reason for your criticism, I won’t tell the excuses fully. just tell “I am sorry”.
By the way,
[quote=“joon1, post:3, topic:263225”]
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
[/quote]About the Warning above,
You said,
[quote=“r937, post:4, topic:263225”]
this is a php error
[/quote]I think the php error is coming from mySQL result resource,
I have experienced It always occur when SQL has problems.
AND
I like to know the meaning of dt in dt(id, country).
if swampBoogie named it
why did he use the word dt?
What is the full word of it?
The following is one of my trials without id from @swampBoogie’s solution.
[code]$list=mysql_query(“select
country
from (select id,
case when country1 = ‘Japan’ then country2 else country1 end
from myTable
where ‘Japan’ in (country1,country2)) dt(country)
group
by country
order
by id”);
while($rows=mysql_fetch_array($list)) {
}[/code]But the code above still produces “Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource”