SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to go about this... (MySQL related)

    I am pulling in users from Active Directory in a for loop such as..

    for ($i=0; $i<info["count"]; $i++) {
    $username=$info[$i]["cn"][0];
    }

    Now, what I want to do is also pull info from my mySQL database that pertains to each user. I can obviously put a MySQL get statement in the for loop and search for a user every time, but this seems like a waste of CPU resources as there may be over 100 users, resulting in 100 different MySQL gets.

    If I select all the information beforehand, is there a way to just search that information for my $username? Meaning, in my for loop I wont have to actually lookup info from the actual databse, rather just search the info I retreived from it beforehand?

    does this make sense to anyone, or am i talkin double dutch here

  2. #2
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,196
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I am not exactly sure what you intend to archive with the for loop.

    Though what you can most probably do is to run a query after you have found out which usernames you need information from (after the for loop). Then in the query limit it to those usernames (i.e. usernames IN ('usernam1', 'username2') etc)

    Then finally in the query while you tie the required data to the username.

  3. #3
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TheRedDevil View Post
    I am not exactly sure what you intend to archive with the for loop.

    Though what you can most probably do is to run a query after you have found out which usernames you need information from (after the for loop). Then in the query limit it to those usernames (i.e. usernames IN ('usernam1', 'username2') etc)

    Then finally in the query while you tie the required data to the username.
    The for loop is just an example, I am binding to Active Directory and grabbing all user accounts. I am then outputting the account names to screen, and beside them is where I want to put the information.

    I wasnt sure if there was a way I could just get all the info from my database beforehand, and then somehow reference it for each user.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your query to retrieve information from mysql should be driven by the list of usernames

    this way you get only one query result row from mysql per user
    Code:
    select desiredcolumns
      from users
     where username in ( 'r937', 'tdob', 'TheRedDevil' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just build an array of usernames by whatever process it is that you're getting them.
    Turn it into a string and use in the query as already explained.
    Where the usernames come from doesn't really matter.

    PHP Code:
    //Get array of usernames here. ActiveDirectory, loop, whatever
    $usernames = array('bob''dick''harry');

    $usernames "'" implode("', '"$usernames) . "'"
    $query "SELECT fields FROM users WHERE username IN ($usernames)"
    (This assumes usernames don't need escaping)

  6. #6
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't make assumptions... Escape the data anyways, just to be sure.

  7. #7
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cranial-bore View Post
    Just build an array of usernames by whatever process it is that you're getting them.
    Turn it into a string and use in the query as already explained.
    Where the usernames come from doesn't really matter.

    PHP Code:
    //Get array of usernames here. ActiveDirectory, loop, whatever
    $usernames = array('bob''dick''harry');

    $usernames "'" implode("', '"$usernames) . "'"
    $query "SELECT fields FROM users WHERE username IN ($usernames)"
    (This assumes usernames don't need escaping)
    Thanks, I guess the hole in my thinking is the following.

    Once I have an array of usernames, and I have selected the values I want from MySQL how do I then output the MySQL results based on the username?

    Is there way to do something like this?

    For each user in my array {
    check the SQLData I have stored for that user, and output results; }

    I know I could do this..

    For each user in my array {
    Get from MySQL database the info pertaining to that user; }

    However, I would prefer to only connect to the DB one time. I am not sure how to refernce the MYSql data I have stored? I am only used to outputting EVERYTHING in a loop.


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
  •