Oracle and PHP question

Hello everyone,

I am trying to display in an array the result of a query. Unfortunately I
cannot concatenate 2 fields
in 1, it makes an error :

Warning: ociexecute() [function.ociexecute]: ORA-00911: Caractère non valide
in
c:\pageWeb\tasks_to_do.php on line 103

Fulfiller Request Type Task Name Start Date End Date Expected Delivery date
Client
Warning: ocifetchinto() [function.ocifetchinto]: ORA-24374: définition non
exécutée
après extraction ou exécution et extraction in c:\pageWeb\tasks_to_do.php on
line 117

if I use query without the concatenate operator it works... but I need to
concatenate
those fields though...

Can anyone help me out?

here is my code:


<?php


// connexion
$conn = ocilogon("stephane","Stef1975",$bdtest05);
// préparation du Select
$query="select a.fulfiller_firstname|| ' '||a.fulfiller_lastname
,b.request_type_name
,c.task_name
,c.task_start_date
,c.task_end_date
,d.request_wished_delivery_date
,e.client_firstname||' '||e.client_lastname
from fulfillers a
,request_types b
,tasks c
,requests d
,clients e
where c.task_fulfiller_id = a.fulfiller_id
and c.task_request_id = d.request_id
and d.request_request_type_id = b.request_type_id
and d.request_client_id = e.client_id;";

$ordre = OCIParse ($conn, $query);
OCIExecute ($ordre);
$ncols = OCINumCols($ordre);
// affiche les lignes tant qu'il y en a
// et les colonnes une par une
print "<TABLE BORDER=1> ";
print "<TR>";
print "<TD>Fulfiller</TD>";
print "<TD>Request Type</TD>";
print "<TD>Task Name</TD>";
print "<TD>Start Date</TD>";
print "<TD>End Date </TD>";
print "<TD>Expected Delivery date</TD>";
print "<TD>Client</TD>";

while (OCIFetchInto ($ordre, $ligne, OCI_NUM)) {
print "<TR> ";
for ( $i=0;$i < $ncols; $i++) {
print "<TD> $ligne[$i] </TD>" ;
}
print "</TR> ";
}// libere les ressources
OCIFreeCursor ($ordre);
OCILogoff($conn);
?>

Steff wrote:

> Hello everyone,

> I am trying to display in an array the result of a query. Unfortunately I
> cannot concatenate 2 fields
> in 1, it makes an error :

> Warning: ociexecute() [function.ociexecute]: ORA-00911: Caractère non valide
> in
> c:\pageWeb\tasks_to_do.php on line 103

> Fulfiller Request Type Task Name Start Date End Date Expected Delivery date
> Client
> Warning: ocifetchinto() [function.ocifetchinto]: ORA-24374: définition non
> exécutée
> après extraction ou exécution et extraction in c:\pageWeb\tasks_to_do.php on
> line 117

> if I use query without the concatenate operator it works... but I need to
> concatenate
> those fields though...

> Can anyone help me out?

> here is my code:


> <?php


> // connexion
> $conn = ocilogon("stephane","Stef1975",$bdtest05);
> // préparation du Select
> $query="select a.fulfiller_firstname|| ' '||a.fulfiller_lastname
> ,b.request_type_name
> ,c.task_name
> ,c.task_start_date
> ,c.task_end_date
> ,d.request_wished_delivery_date
> ,e.client_firstname||' '||e.client_lastname
> from fulfillers a
> ,request_types b
> ,tasks c
> ,requests d
> ,clients e
> where c.task_fulfiller_id = a.fulfiller_id
> and c.task_request_id = d.request_id
> and d.request_request_type_id = b.request_type_id
> and d.request_client_id = e.client_id;";

> $ordre = OCIParse ($conn, $query);
> OCIExecute ($ordre);
> $ncols = OCINumCols($ordre);
> // affiche les lignes tant qu'il y en a
> // et les colonnes une par une
> print "<TABLE BORDER=1> ";
> print "<TR>";
> print "<TD>Fulfiller</TD>";
> print "<TD>Request Type</TD>";
> print "<TD>Task Name</TD>";
> print "<TD>Start Date</TD>";
> print "<TD>End Date </TD>";
> print "<TD>Expected Delivery date</TD>";
> print "<TD>Client</TD>";

> while (OCIFetchInto ($ordre, $ligne, OCI_NUM)) {
> print "<TR> ";
> for ( $i=0;$i < $ncols; $i++) {
> print "<TD> $ligne[$i] </TD>" ;
> }
> print "</TR> ";
> }// libere les ressources
> OCIFreeCursor ($ordre);
> OCILogoff($conn);
> ?>


Try changing:
$query="select a.fulfiller_firstname|| ' '||a.fulfiller_lastname

to

$query="select a.fulfiller_firstname|| ' '||a.fulfiller_lastname as
fulfillnm

and
,e.client_firstname||' '||e.client_lastname
to
,e.client_firstname||' '||e.client_lastnam as clientname

On Fri, 10 Mar 2006 19:32:29 +0100, Steff wrote:

> // connexion
> $conn = ocilogon("stephane","Stef1975",$bdtest05);
> // préparation du Select
> $query="select a.fulfiller_firstname|| ' '||a.fulfiller_lastname
> ,b.request_type_name
> ,c.task_name
> ,c.task_start_date
> ,c.task_end_date
> ,d.request_wished_delivery_date
> ,e.client_firstname||' '||e.client_lastname
> from fulfillers a
> ,request_types b
> ,tasks c
> ,requests d
> ,clients e
> where c.task_fulfiller_id = a.fulfiller_id
> and c.task_request_id = d.request_id
> and d.request_request_type_id = b.request_type_id
> and d.request_client_id = e.client_id;";
>
> $ordre = OCIParse ($conn, $query);
> OCIExecute ($ordre);
> $ncols = OCINumCols($ordre);
> // affiche les lignes tant qu'il y en a
> // et les colonnes une par une
> print "<TABLE BORDER=1> ";
> print "<TR>";
> print "<TD>Fulfiller</TD>";
> print "<TD>Request Type</TD>";
> print "<TD>Task Name</TD>";
> print "<TD>Start Date</TD>";
> print "<TD>End Date </TD>";
> print "<TD>Expected Delivery date</TD>";
> print "<TD>Client</TD>";
>
> while (OCIFetchInto ($ordre, $ligne, OCI_NUM)) {
> print "<TR> ";
> for ( $i=0;$i < $ncols; $i++) {
> print "<TD> $ligne[$i] </TD>" ;
> }
> print "</TR> ";
> }// libere les ressources
> OCIFreeCursor ($ordre);
> OCILogoff($conn);
> ?>

Stefane, you didn't say which version of PHP are you using, but this
definitely looks like OCI and not OCI8. Unfortunately, I don't understand
French so I cannot help you with the comments, but here is an analogous
code in PHP 5.1.2 and OCI8:

<?php
$conn = oci_connect("scott", "tiger", "local");
// php_beautifier->setBeautify(false);
$query = "SELECT empno||' '||ename ,job ,hiredate
FROM emp
WHERE deptno>0";
// php_beautifier->setBeautify(true);
$sth = oci_parse($conn, $query);
oci_execute($sth);
$ncols = oci_num_fields($sth);
while ($row = oci_fetch_array($sth)) {
for ($i = 0;$i<$ncols;$i++) {
printf("%s\t", $row[$i]);
}
print ("\n");
}
?>

As you can see, I use concatenation as well and it works like a charm:

$ ./ttt
7369 SMITH CLERK 17-DEC-80
7499 ALLEN SALESMAN 20-FEB-81
7521 WARD SALESMAN 22-FEB-81
7566 JONES MANAGER 02-APR-81
7654 MARTIN SALESMAN 28-SEP-81
7698 BLAKE MANAGER 01-MAY-81
7782 CLARK MANAGER 09-JUN-81
7788 SCOTT ANALYST 19-APR-87
7839 KING PRESIDENT 17-NOV-81
7844 TURNER SALESMAN 08-SEP-81
7876 ADAMS CLERK 23-MAY-87
7900 JAMES CLERK 03-DEC-81
7902 FORD ANALYST 03-DEC-81
7934 MILLER CLERK 23-JAN-82


--
http://www.mgogala.com