#1
  1. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,164
    Rep Power
    220

    Multiple sql in a loop.


    Hi;

    How can I get this sql in line 9 of view file out of the loop?Model
    PHP Code:
    public function list_groups($member_id)
        {
            
    $sql "
            SELECT
                * 
                    FROM  groups
                        WHERE fkUserId = ?
                    ORDER BY name"
    ;
            
    $query $this->db->query($sql, array($member_id));
            return 
    $query->result();
        } 
    Controller
    PHP Code:
    $data['user_groups'] = $this->model_auth->list_groups($this->session->userdata['logged_data']['member_id']); 
    View
    php Code:
    <?php
        foreach($user_groups as $val=>$row)
          {
            ?> 
              <h4><?php echo html_escape(ucwords($row->name));?></h4>
              <table>
     
                 <?php
                 $sql = "SELECT * FROM  cardDetails WHERE fkUserId = ".$this->session->userdata['logged_data']['member_id']." AND fkGroupId = ".$row->id;
                    $query = $this->db->query($sql);
                    foreach ($query->result() as $row_details)
                    {
                       ?>
                       <tr>
     
                        <td><?php echo html_escape(ucwords($row_details->firstName));?>&nbsp; <?php echo html_escape(ucfirst($row_details->lastName));?></td>
                        <td>
                           <?php
                            echo form_dropdown('groups', $groups_dropdown,'', 'class="form-control selectwidthauto"');   
                           ?>
                        </td>
     
                      </tr>
                       <?php
                    }
                 ?>
     
     
            </table> 
      <?php } ?>
    Last edited by English Breakfast Tea; February 12th, 2014 at 09:16 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,118
    Rep Power
    537
    Originally Posted by English Breakfast Tea
    How can I get this sql in line 9 of view file out of the loop
    By JOINing it to your query where you get $user_groups. Spend time figuring out how to properly get your data before displaying it, and you will be much happier (hopefully happier, but no guarantees, but I can guaranty you will have much more time).

    Comments on this post

    • Jacques1 agrees
  4. #3
  5. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,164
    Rep Power
    220
    Originally Posted by NotionCommotion
    By JOINing it to your query where you get $user_groups. Spend time figuring out how to properly get your data before displaying it, and you will be much happier (hopefully happier, but no guarantees, but I can guaranty you will have much more time).
    I used to know how to do this. Takes some times to get back in to group concat etc. I was hoping someone could help.

    From memory ManiacDan and/or Kicken are experst on this.

    Comments on this post

    • requinix disagrees : there are many other people here who are capable of helping you
    • sir_drinxalot disagrees : NotionCommotion gave a perfectly acceptable answer. You need to remember that this community seems to spend a lot of time teaching you for free so that you can deliver on what you sold your clients.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1017
    Originally Posted by English Breakfast Tea
    I was hoping someone could help.
    NotionCommotion did help you. I find it pretty disrespectful to brush him off like that.

    You need experts for what? Joining two tables? Yeah, only a very small elite of super-programmers is capable of understanding this highly sophisticated technique.
    Last edited by Jacques1; February 13th, 2014 at 08:52 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,884
    Rep Power
    6354
    People can make themselves experts by reading the manual page on JOIN

    You seem to have everything you need here.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2003
    Posts
    802
    Rep Power
    104
    All of that SQL should be able to be replaced with something like this before the for...each loop:
    SQL Code:
    SELECT cardDetails.*
    FROM groups JOIN cardDetails ON groups.fkUserId = cardDetails.fkUserId
    WHERE groups.fkUserId = ?
    ORDER BY groups.name

    That should select all of the groups for the specified user ID.
  12. #7
  13. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,164
    Rep Power
    220
    Originally Posted by ManiacDan
    People can make themselves experts by reading the manual page on JOIN

    You seem to have everything you need here.
    Hello ManiacDan.

    This doesn't make much sense to me. I need a "manual" on "how to use MYSQL manual" first, then get into this. Don't you agree?
    table_references:
    escaped_table_reference [, escaped_table_reference] ...

    escaped_table_reference:
    table_reference
    | { OJ table_reference }

    table_reference:
    table_factor
    | join_table

    table_factor:
    tbl_name [[AS] alias] [index_hint]
    | table_subquery [AS] alias
    | ( table_references )

    join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
    | table_reference STRAIGHT_JOIN table_factor
    | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
    | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
    | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

    join_condition:
    ON conditional_expr
    | USING (column_list)

    index_hint:
    USE {INDEX|KEY} [FOR JOIN] (index_list)
    | IGNORE {INDEX|KEY} [FOR JOIN] (index_list)
    | FORCE {INDEX|KEY} [FOR JOIN] (index_list)

    index_list:
    index_name [, index_name] ...
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,884
    Rep Power
    6354
    After the technical definition of the JOIN syntax, there's paragraphs upon paragraphs of information. If you want to really, actually learn, you're going to have to try to struggle through some information. The JOIN syntax CAN be complex, but as you see above, your solution is relatively trivial. But if you don't learn, you'll never become better. So go read and try to come to some kind of understanding of the JOIN syntax. It's the reason databases are better than flat files, and the entire concept behind normal forms, a critical component in database design.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  16. #9
  17. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,072
    Rep Power
    4101
    Originally Posted by English Breakfast Tea
    I need a "manual" on "how to use MYSQL manual"
    1.2. Typographical and Syntax Conventions

    Most technical books or manuals have a page dedicated to explaining what conventions they use and how to interpret different diagrams.

    A lot of conventions are similar across multiple manuals as well so once you learn how to read one you can typically apply that same meaning to other manuals.

    For example text within [ ] is usually considered optional, eg:
    Code:
    escaped_table_reference [, escaped_table_reference] ...
    means you have one escaped_table_referenced optionally followed by a comma and another escaped_table_reference.

    Once you know the conventions and have an understanding of the syntax definition, you read the rest of the information on JOINs to get further details about how they work and such.

    Comments on this post

    • ManiacDan agrees : You are the man
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

IMN logo majestic logo threadwatch logo seochat tools logo