[PHP-DEV] Bug #5680: problems with SQL instruction "group by" in PHP+iODBC (Openlink) From: alex <email protected>
Date: 07/19/00

From: alex <email protected>
Operating system: Linux kernet 2.0.36
PHP version: 3.0.16
PHP Bug Type: ODBC related
Bug description: problems with SQL instruction "group by" in PHP+iODBC (Openlink)

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> 

-- 
PHP Development Mailing List <http://www.php.net/>
To unsubscribe, e-mail: php-dev-unsubscribe <email protected>
For additional commands, e-mail: php-dev-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>