SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2004
    Location
    Beijing
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ADODB and multiple table select statements

    I am using ADODB to connect to a database and query multiple tables with a single select statement. The problem is that in the two tables (Users and Groups) I am selecting from contain the same field name called 'id', and I am using an associative array fetch mode (i.e. $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC). The problem I am experiencing is that I am unable to use $result->fields['users.id'] to obtain the ID from the Users table instead of the Groups table.

    Here is the code:

    Code:
    $sql = "select * from users, groups";
    $dbConn = ADONewConnection('mysqlt');
    
    $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
    
    $dbConn->Connect("db1", "root", "root", "nat");
    
    $results = $dbConn->Execute($sql);
    
    while (!$results->EOF)
    {
      echo $results->fields['id'];
      echo $results->fields['users.id'];
    
      $results->MoveNext();
    }
    The problem here is that echo $results->fields['id'] returns the ID for the Groups table, and echo $results->fields['users.id'] returns an "undefined index on users.id" error. I've searched through the ADODB manual and the web for a solution, but can only seem to find a way to do this using a fetch mode of "ADODB_FETCH_NUM". Does anybody have any idea how to do this using ADODB_FETCH_ASSOC mode?

    Thanks!

  2. #2
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what about doing:
    PHP Code:
    $sql "select u.id as userid, g.id as groupid, u.*, g.* from users u, groups g"
    to remove the ambiguity.
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  3. #3
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if this is the full SQL statement you posted but you should add a where clause such as

    Code:
    WHERE u.groupid = g.id;

  4. #4
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to place all your column names in the SELECT clause of your query and give the users.id column an alias:
    Code:
    SELECT users.id AS userID, users.name, users.etc, groups.id AS groupID, groups.name, groups.etc
    ...
    And add the WHERE clause like suggested in the above posts, unless you want to display each user coupled to each group.


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
  •