How to get unique items only?

tito

I've got a complex query with many JOIN where I cannot invoke the DISTINCT condition.

In the while loop I retieve many items.

publication name
publication description
publication period
Issue name
Issue description

and all articles in that issue.

The results are

publication1 description1 article 1
publication1 description1 article 2
publication1 description1 article 3

publication2 description2 article 5
publication2 description2 article 4
publication2 description2 article 6

etc ....

While obviously I need all articles, but how do I make it so it displays only like that?

publication1 description1 article 1
article 2
article 3

publication2 description2 article 5
article 4
article 6

The group by displays only one article per publication.

Thanks.

Piranha

You don't do that in the query. You should get all the rows just as you get it now and solve the problem in the PHP code. Something like the pseudocode below.

$result = mysql_query($sql);
$publ = "";   // Use this variable to see if it is a new row or not
foreach ($result as $row) {
  if ($publ == $row['publ'] {  // If it is the same publication
    echo $row['article'];
  } else { // If it is a new publication
    echo $row['publ'] . $row['desc'] . $row['article'];
    $publ = row['publ'];   // Added, forgot this row before
  }
}

tito

Thanks, but I can't get it.

This is how the problem code which displays all looks like. It's vbulletin

Piranha

Well, I made a little mistake with the code above. I have corrected it now, so it should be possible to see how to do this.

tito

Thanks. Will try again.

tito

hummm that gives me one row with publication and article, the next one with article only, which is good, but the third one gets publication again, not the fourth one and so on alternating them

Piranha

Let's step through it with the rows above:

[b]description           $row['publ']      $row['article']    $publ     if/else    [/b]
before the loop       non existent      non existent       ""        -----   

First time in loop    publ1             art1               ""        else
$publ is set          ----              ----               "publ1"   ----

Second time in loop   publ1             art2               "publ1"        if

Third time in loop    publ1             art3               "publ1"        if

Forth time in loop    publ2             art4               "publ1"        else
$publ is set          ----              ----               "publ2"   ----

Fifth time in loop    publ2             art5               "publ2"        if

Sixth time in loop    publ2             art6               "publ2"        if

As you see the value of $publ won't disapear just because you do another iteration in the loop. A good idea if you don't understand what is happening in a loop is to do as above and just take a piece of paper and write it down step by step. Then you get a feeling for what is happening. If you don't understand the steps above do it yourself and take it one row at a time.