SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hello all,


    With the following query construct:

    PHP Code:
    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. #2
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try number II


    With the above:

    Code MySQL:
    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. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You can't do that in MySQL (or any DBMS as far as I know).
    What I always use is something like:

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

    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    You can't do that in MySQL (or any DBMS as far as I know).
    What I always use is something like:

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

    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. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oikram View Post
    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?
    Exactly

    Quote Originally Posted by oikram View Post
    With this, you don't need strange (or I'm just ignorant) things like unset.
    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).

    Quote Originally Posted by oikram View Post
    Do you think that a DO WHILE will be of some use on this scenario?
    I don't follow. Could you explain that a little differently?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    ... 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).
    Yes... If I got you right, it's true! 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).


    Quote Originally Posted by ScallioXTX View Post
    I don't follow. Could you explain that a little differently?
    hhm...
    PHP Code:
    foreach($rows as $row) {
     do {
        echo 
    $row['lastname'];
     } while (
    $row['lastname'] .... ); 
    arrgghh - bed time already.

    nvm.

    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.

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oikram View Post
    Yes... If I got you right, it's true! 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).
    Spot on! That's exactly why it has to be sorted

    Quote Originally Posted by oikram View Post
    hhm...
    PHP Code:
    foreach($rows as $row) {
     do {
        echo 
    $row['lastname'];
     } while (
    $row['lastname'] .... ); 
    arrgghh - bed time already.

    nvm.
    Indeed, better leave such trains of thought for when you're really rested

    Quote Originally Posted by oikram View Post
    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.
    Thanks for your kind words

    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •