0
TopicName    | TopicUrl | MainTopicName
------------------------------
1stSubtopic  | url1     | MainOne
2ndSubtopic  | url2     | MainTwo
3rdSubtopic  | url3     | MainOne

As result want to get 2 php arrays

one array $MainOne 

(
[TopicName1] => 1stSubtopic
[TopicUrl1] => urll
)
(
[TopicName1] => 3rdSubtopic
[TopicUrl1] => url3
)

another array $MainTwo 

(
[TopicName2] => 2ndSubtopic
[TopicUrl2] => url2
)

Latter access to values like $array_from_mysql[1]['TopicName1'], $array_from_mysql[2]['TopicName2'] and so on

May write two SELECT statements, like SELECT TopicName, TopicUrl from table WHERE MainTopicName = ?.

But if there are many MainTopicName, like MainOneHundred, then SELECT would not good solution.

But how to do it with CASE or in other way?

The below (may be) is not correct, but I just want to understand idea

CASE MainTopicName

WHEN MainTopicName = MainOne THEN SELECT TopicName AS TopicName1
WHEN MainTopicName = MainOne THEN SELECT TopicUrl AS TopicUrl1

WHEN MainTopicName = MainTwo THEN SELECT TopicName AS TopicName2
WHEN MainTopicName = MainTwo THEN SELECT TopicUrl AS TopicUrl2

What would be correct statement with CASE to use instead of many SELECT statements?

Andris
  • 1,434
  • 1
  • 19
  • 34

2 Answers2

0

I'm not totally sure what you're asking, but maybe this is what you want:

SELECT MainTopicName, GROUP_CONCAT(TopicName) AS TopicNames, GROUP_CONCAT(TopicUrl) AS TopicUrls
FROM YourTable
GROUP BY MainTopicName

TopicNames and TopicUrls will be comma-separated lists of names and URLs.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Edited question. Mean if in column `MainTopicName` value is `MainOne`, then Select `TopicName AS TopicName1` and latter with php access to the value like `echo $array[0]['TopicName1']` – Andris Aug 16 '14 at 07:52
  • 1
    So you want a pivot table, where you get different columns in the output depending on the value in the input. See the duplicate question I just linked to. – Barmar Aug 16 '14 at 07:58
0

So you want all even or all odd rows?

You could add an id column as a surrogate key and then get every even or odd row: 

SELECT TopicName, TopicUrl FROM table WHERE id % 2 = 0

(results in all even rows. = 1 would give you the odd ones).

Quoting Eddie
  • 1,501
  • 1
  • 11
  • 14