Bug #5680 Updated: problems with SQL instruction "group by" in PHP+iODBC (Openlink)

ID: 5680 Updated by: sander@php.net Reported By: alex@zaraplast.com.br -Status: Open +Status: Bogus Bug Type: ODBC related Operating System: Linux kernet 2.0.36 PHP Version: 3.0.16 New Comment: Thank you for taking the time to report a problem with PHP. Unfortunately, PHP 3 is no longer supported. Please download the latest version of PHP 4 from http://www.php.net/downloads.php If you are able to reproduce the bug with one of the latest versions of PHP, please change the PHP version on this bug report to the version you tested and change the status back to "Open". Again, thank you for your continued support of PHP. Previous Comments: ------------------------------------------------------------------------ [2000-07-19 20:29:32] alex@zaraplast.com.br I'm using PHP 3.0.16 with iODBC(Openlink), and when I use the clause "group by" in an SQL instruction I receive an error message. I noted that when I use all the fields of the table in the "group by", I don't receive the message, but the result isn't what I want. I've tested the SQL instruction directely in the program odbctest of iODBC and it works perfectely. DBMS: DB2/NT UDB 5.0.0 running with NT 4.0 My Table: pcaear01 field type cod_filial integer cod_item integer cod_pro integer cod_maq integer some data from the table: cod_filial cod_item cod_pro cod_maq 1 515510242 1 6 1 515510242 2 15 1 515510242 2 29 1 515510242 1 3 1) Select without group by: $result = odbc_do($conn,"select cod_pro from pcaear01 where cod_item = 515510242"); Result: COD_PRO 1 1 2 2 2) Select with group by: $result = odbc_do($conn,"select cod_pro from pcaear01 where cod_item = 515510242 group by cod_pro"); result: Warning: SQL error: [OpenLink][ODBC][IBM][CLI Driver][DB2/NT] SQL0119N An expression starting with "COD_MAQ" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY cla, SQL state S1000 in SQLExecDirect in /home/httpd/html/apontamento/monta_processo.php3 on line 18 expected: COD_PRO 1 2 #Note that I did't use the field cod_maq in the select 3) Select with group by of all fields: $result = odbc_do($conn,"select cod_pro from pcaear01 where cod_item = 515510242 group by cod_pro, cod_maq, cod_item, cod_filial"); Result: COD_PRO 1 1 2 2 ------------------My PHP script <HTML> <HEAD> <TITLE>Lista Agenda Pessoal</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#000080" ALINK="#FF0000"> <? putenv("LD_LIBRARY_PATH=/usr/lib"); putenv("ODBCINSTINI=/usr/local/openlink/bin/odbcinst.ini"); putenv("ODBCINI=/usr/local/openlink/bin/odbc.ini"); $conn = odbc_connect("ZARATEST","db2admin","db2admin"); if(!$conn){ print "<BR>Nao foi possivel conectar-se com o Banco de dados\n"; exit; } $result = odbc_do($conn,"select cod_pro from pcaear01 where cod_item = 515510242 group by cod_pro"); if(!$result){ print "<BR>Nao foi possivel pesquisar o Banco de dados.\n"; exit; } $num_linhas=odbc_num_rows($result); $num_cols=odbc_num_fields($result); echo '<form method="POST" action="monta_maquina.php3" >'; echo '<input type="hidden" name="cod_produto" value="'.$cod_produto.'">'; echo '<p><select name="cod_processo" size="1">\n'; for($i=1;$i<=$num_linhas;$i++){ odbc_fetch_row($result,$i); echo "<option value=".odbc_result($result,1).">".odbc_result($result,2)."</option>\n"; } echo '</select><input type="submit" value="Ok" name="enviar"></p>'; echo '</form>'; odbc_free_result($result); odbc_close($result); ?> </BODY> </HTML> ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=5680&edit=1