A complex query with and without Zend Database Adapter - a 'almost' mySQL question

PHP
1

Hello all,

With the following query construct:

public function listachallengesPendentes()
{
  $select = $this->getAdapter()->select();
  $select->from(array("e"=>"teams"),array('name'));
  $select->join(array("de"=>"challengeperteam"),"e.cod_team = de.cod_teamFk",array());
  $select->join(array("d"=>"challenges"),"d.cod_challenge = de.cod_challengeFk",array('title'));

  $select->columns(array("e.cod_team"
                         ,"name_team"=>"e.name"
          ,"d.cod_challenge"
          ,"name_challenge"=>"d.title"
          ,"d.details"
          ,"d.score"
          ,"category"=>"d.cod_categoryFk"
          ,"de.proof"
          ,"de.date_concluded"
          ,"de.cod_challenge_team"
       ));

$select->where("de.status = 0");
$select->order(array('e.cod_team DESC', 'de.cod_challenge_team DESC'));

 return $this->getAdapter()->fetchAll($select);
 }

I get something like:
Team A
blabla 1

Team A
blabla 4

Team A
blabla3

Team B
blabla9

Team B
blabla8

But I would like to have, instead:
Team A
blabla1
blabla4
blabla2

Team B
blabla9
blabla8

so that we could have one occurrence of Team A and one occurrence of Team B… instead of having them repeated again and again on the loop.

Can this be done on the query side?

If so, can I have a clue about how can we do it?

If this can be done on the loop side, what would a proper way for doing so be?

Thanks in advance,
Márcio

2

Try number II

With the above:

SELECT e.name
,d.cod_team
,d.cod_challenge
,d.title
,d.details
,d.score
,de.proof
,de.date_concluded
,de.cod_challenge_team
FROM teams e
INNER JOIN challengeperteam de ON de.cod_teamFk = e.cod_team
INNER JOIN challenges d ON d.cod_challenge = de.cod_challengeFk
WHERE  de.status = 0 
ORDER BY  e.cod_team DESC, de.cod_challenge_team DESC;

I get something like:
Team A
blabla 1

Team A
blabla 4

Team A
blabla3

Team B
blabla9

Team B
blabla8

But I would like to have, instead:
Team A
blabla1
blabla4
blabla2

Team B
blabla9
blabla8

3

You can’t do that in MySQL (or any DBMS as far as I know).
What I always use is something like:


$lastName='';
foreach($rows as $row) {
  if ($row['name'] != $lastName) { 
    echo $row['name'];
  }
  // echo more info
  // ...
  $lastName=$row['name'];
}

:slight_smile:

4

Thanks a lot. So, at the end of the interaction you fill $lastName with the present value, and, by doing so, you make sure that That name, will appear only once. Yes?

With this, you don’t need strange (or I’m just ignorant) things like unset.

Do you think that a DO WHILE will be of some use on this scenario?

Thanks a lot again,
Márcio

5

Exactly

Nope, all you need is the idea of the code I provided. Which does assume the data is sorted by $row[‘name’] btw! If it isn’t the code won’t work (think about it).

I don’t follow. Could you explain that a little differently?

6

Yes… If I got you right, it’s true! :slight_smile: Because if we do not order it by name, then the last variable will have another value at the end of the interaction but, IF later on, that value appears again, we will have it twice when, only once was requested.
(I’m to tired to properly express myself - hope this makes sense. O.o).

hhm…

foreach($rows as $row) {
 do {
	echo $row['lastname'];
 } while ($row['lastname'] .... );

arrgghh - bed time already.

nvm. :smiley:

Tomorrow, I will try to implement this according to my needs and see what I get. You absolutely rock with your dialectic method, cheers to that. :slight_smile:

7

Spot on! That’s exactly why it has to be sorted :tup:

Indeed, better leave such trains of thought for when you’re really rested :wink:

Thanks for your kind words :blush:

:slight_smile: