php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #5680 problems with SQL instruction "group by" in PHP+iODBC (Openlink)
Submitted: 2000-07-19 20:29 UTC Modified: 2002-06-16 08:25 UTC
From: alex at zaraplast dot com dot br Assigned:
Status: Not a bug Package: ODBC related
PHP Version: 3.0.16 OS: Linux kernet 2.0.36
Private report: No CVE-ID: None
 [2000-07-19 20:29 UTC] alex at zaraplast dot com dot 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>

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-06-16 08:25 UTC] sander@php.net
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.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Thu Sep 24 11:01:24 2020 UTC