Bug #13931 Updated: unexpected SQL error using SQL_CUR_USE_ODBC in odbc_pconnect()

ID: 13931 User updated by: g.sollis@syntegra.nl Reported By: g.sollis@syntegra.nl Status: Open Bug Type: ODBC related Operating System: Windows NT4 SP6a PHP Version: 4.0.6 New Comment: Also, i have seen a similar error if i reference a field which has a relationship with a primary key in another table - something like projects.userid, which is in the users table - but don't get any fields from that related table. consider this: -------------- SELECT projects.proj_num, projects.status, clients.name, projects.user_id FROM projects INNER JOIN clients ON projects.clientID=clients.clientID WHERE projects.proj_status='Open'; The ODBC error is 'AMBIGUOUS COLUMN NAME user_id' This must defenately be a bug, because user_id is only referenced once in the query, with source table 'projects'. I must add that there is also a field 'user_id' in the 'clients' table, but i am not querying that field. Also, there is no relationship between the 'clients.user_id' and the 'projects.user_id'. They are related to the users table, so i can set the 'owner' of a client or project. Previous Comments: ------------------------------------------------------------------------ [2001-11-05 11:19:53] g.sollis@syntegra.nl oops...missed a ')' in my code here at the end of the 'do' loop. I have got this right in my original code ;) ------------------------------------------------------------------------ [2001-11-05 06:57:41] g.sollis@syntegra.nl I am using the pre-compiled version of PHP 4.06 on Win32. The web server is IIS-4 on WinNT with Option Pack and the database is MS SQL-Server 6.5. Connection is made via ODBC using a system DSN. I am using the latest MDAC(2.6) and JET(4) drivers on this DSN. What i want to do is the following: * the user creates a query (by form) that results in 1 or more projects being retrieved. * if more than 1 projects are found i want to display a shortlist of projects. The user should be able to sort the result on all displayed columns. When a project is selected, the form submits and retrieves the details for that project. The project is retrieved using the primary key in the WHERE clause and there is no ORDER BY clause: $qry="SELECT projects.proj_num, projects.status, clients.name FROM projects INNER JOIN clients ON projects.clientID=clients.clientID WHERE projects.proj_num=12345"; * if one project is found, the form should display the project details. projects.proj_num and clients.clientID are the table's primary key. The code I am running is basically this: $qry="SELECT projects.proj_num, projects.status, clients.name FROM projects INNER JOIN clients ON projects.clientID=clients.clientID WHERE projects.proj_status='Open'"; $conID=ODBC_PCONNECT(DSN,USER,PASSWORD); $res =ODBC_DO($conID,$qry); $rowCnt=0; while(odbc_fetch_row($res)) { $rowCnt=$rowCnt+1; } odbc_fetch_row($res,1) if($rowCnt==1) { //display the detailed info function here //because we only have found one project. } else { do { //this section displays a list of found projects //display some fields using odbc_result() here. } while(odbc_fetch_row($res); } situation 1: ------------ Using the default cursor by specifying ODBC_PCONNECT(DSN,USER,PASSWORD) and then performing the query by i get a result set which i can display. Then a user selects a column to sort and the query gets the following addition (example): " ORDER BY clients.name ASC" The form is submitted using the POST method. Trying to sort on a different column than the primary key returns only the last row, if more than one project is found. The query has only changed in that an ORDER BY clause has been added. I understand this behaviour is due to the driver not supporting row numbers in ODBC_RESULT(), even though it does work from MS-Access or the MS-Query. In short, i can get one or more projects, but cannot sort on any column except the primary key. situation 2: ------------ In this situation i have changed the connection by specifying it to use an ODBC cursor: $conID=ODBC_PCONNECT(DSN,USER,PASSWORD,SQL_CUR_USE_ODBC); Now i can sort through the list to my heart's content, but when a project is selected it does not work. I get the following error from SQL in this situation: "AMBIGUOUS COLUMN NAME clientID". I don't understand how this can happen as i have specified which fields it should retrieve from each table. Also, i do not get this error using any other software. I have made a workaround by querying twice using the default CURSOR-type from situation 1 and storing the results in seperate variables. I don't really want to do this due to wasted resources, so please get it fixed, or re-educate me ;) ------------------------------------------------------------------------ Edit this bug report at http://bugs.php.net/?id=13931&edit=1