DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

[HELP] problem with oci8 in php

959674Oct 14 2012 — edited Oct 14 2012

Hello expert,i have a problem when install oci8.so on ubuntu 12,i'm trying with tutuariol
https://help.ubuntu.com/community/PHPOracle

but when i finished,i was creating a file with content
*<?php*
*$db = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=192.168.1.17)*
*(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=apporcl)*
*(SERVER=DEDICATED)))';*
*$username = 'system';*
*$password = '123456';*
oci_connect($username, $password, $db );

echo 'a';
*?>*
but i don't know why ? althought true or false syntax,it still not show error message,in general,it can't echo result alter i call oci_connect function

OS : Oracle Linux
DB : 11g
Thank

NicloeiW

a simple solution

QL> 
SQL> With t As
  2  (
  3    Select 1 s, 'reni'Name , 1 test From dual Union All
  4    Select 2, 'arun'  , 2           From dual Union All
  5    Select 3, 'jithu' , 3           From dual Union All
  6    Select 4, 'rajesh', 4           From dual Union All
  7    Select 5, 'shiju' , 1           From dual Union All
  8    Select 6, 'Thomas', 1           From dual Union All
  9    Select 7, 'Thomas', 2           From dual Union All
 10    Select 8, 'Thomas', 3           From dual Union All
 11    Select 9, 'Thomas', 4           From dual
 12  )
 13  Select a.Name,
 14         a.s,
 15         a.test
 16  From
 17  ( Select *
 18    From t
 19  ) a,
 20   (
 21    Select Name,
 22           Count(Name) cnt
 23    From t
 24    Group By Name
 25    Having Count(Name) = 4
 26   ) b
 27  Where a.Name = b.Name
 28  /

NAME            S       TEST
------ ---------- ----------
Thomas          6          1
Thomas          7          2
Thomas          8          3
Thomas          9          4

SQL>

500237

Hi,

You can try something like this

SELECT count(*),name from TEST group by name having count(test)>3;

Rgds,
Aashish S.

479546

Thanks Nicloei ofr ur help

I have more than 1000 rows like that and many more to come.So that query will not be feasible i think.Thank u once agin

479546

Thanks Aashish

But the test no is not in order in exact case

NicloeiW

so use that query and modify your query for 100, 1000 or 10000 rows,,,,

479546

Thanks

You mean to write the 1000 names like tht

526547

What do you mean?

I think Aashish solution is correct. A slight variation if "names can retake tests"

select name, count (distinct testno)
from   <yourtable>
group  by name
having count( distinct testno) = <no. of tests>

In your example, no. of tests will be 4.

NicloeiW

Thanks

You mean to write the 1000 names like tht

omg ??? this is silly question ??

use table name, in my query instead of t,
and use column name from that table

or i should tell you that also ??

Message was edited by:
Nicloei W

ushitaki

with t as
(
  select 1 s, 'reni' name , 1 test from dual union all
  select 2,   'arun'      , 2      from dual union all
  select 3,   'jithu'     , 3      from dual union all
  select 4,   'rajesh'    , 4      from dual union all
  select 5,   'shiju'     , 1      from dual union all
  select 6,   'Thomas'    , 1      from dual union all
  select 7,   'Thomas'    , 2      from dual union all
  select 8,   'Thomas'    , 3      from dual union all
  select 9,   'Thomas'    , 4      from dual)
select a.name
      ,a.s
      ,a.test
from t a
where exists  
  ( select 't'  
      from t b
    where b.test in (1,2,3,4)
      and b.name = a.name
    having count(distinct b.test) = 4
  )
;

nurhidayat

a more generic solution...

with t as  (
  select 1 slno, 'reni' name, 1 testno from dual union all
  select 2, 'arun', 2 from dual union all
  select 3, 'jithu', 3 from dual union all
  select 4, 'rajesh', 4 from dual union all
  select 5, 'shiju', 1 from dual union all
  select 6, 'Thomas', 1 from dual union all
  select 7, 'Thomas', 2 from dual union all
  select 8, 'Thomas', 3 from dual union all
  select 9, 'Thomas', 4 from dual 
)
select *
from (
  select t1.*, sys_connect_by_path(testno,'/') path
  from ( select t.*, lag(slno) over(partition by name order by testno) slno_lag from t ) t1
  start with slno_lag is null connect by slno_lag = prior slno
)
where path like '%/' || to_char(1) || '/' || to_char(2) ||
                '/' || to_char(3) || '/' || to_char(4)

just modify the where clause to match your criteria.
note: not tested for performance.

474126

Many ppl have given you solutions and suggestions. Did you try those?

Anyway, check whether this helps or not?

SQL> create table t as
  2  select 1 slno,'reni' name, 1 testno from dual union all
  3  select 2,'arun',2  from dual union all
  4  select 3,'jithu',3 from dual union all
  5  select 4,'rajesh',4 from dual union all
  6  select 5,'shiju',1 from dual union all
  7  select 6,'Thomas',1 from dual union all
  8  select 7,'Thomas',2 from dual union all
  9  select 8,'Thomas',3 from dual union all
 10  select 9,'Thomas',4 from dual ;

Table created.

SQL> select * from t
  2  where name in
  3          (select name from t
  4          group by name
  5          having sum(decode(testno,1,1,2,1,3,1,4,1,0))=4);

      SLNO NAME       TESTNO
---------- ------ ----------
         6 Thomas          1
         7 Thomas          2
         8 Thomas          3
         9 Thomas          4

SQL> select slno,name,testno
  2  from
  3  (select slno,name,testno,count(1) over(partition by name) cnt from t)
  4  where cnt =4;

      SLNO NAME       TESTNO
---------- ------ ----------
         9 Thomas          4
         6 Thomas          1
         7 Thomas          2
         8 Thomas          3

SQL>

474126

When you try dont include the WITH clause. Since we dont have ur table and data, we showed an example with subset of data. Also change the "t" with yout table name and try. Solutions should work provided you try the proper query

NicloeiW

well i guess that to much of spoon feeding ;-)

i am not sure if he is trying that or not ?

474126

Nothing wrong in helping. Seems he is desperate for the help.
Does helping going to harm us? :-)

NicloeiW

no..
everyone needs help , it doesnt harm us, but if he cant use the query with as given to him, then i guess he should put an effort, like this he can learn better, 4 solutions are given to him,

that would benefit him in long run

Avinash Tripathi

Hi Mohan,

I think these solution won't work if any person has attampted more than once for a single test.

SQL> select * from t;

      SLNO NAME       TESTNO
---------- ------ ----------
         1 reni            1
         2 arun            2
         3 jithu           3
         4 rajesh          1
         5 shiju           1
 6 Thomas 1
7 Thomas 2
8 Thomas 3
9 Thomas 1

9 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select slno,name,testno
  2  from
  3   (select slno,name,testno,count(1) over(partition by name) cnt from t)
  4* where cnt =4
SQL> /

    SLNO NAME TESTNO
---------- ------ ----------
9 Thomas 1
6 Thomas 1
7 Thomas 2
8 Thomas 3
SQL>


So the modified version of your query would be:

SQL> ed
Wrote file afiedt.buf

  1  SELECT slno,name,testno FROM (
  2  SELECT slno,name,testno, count(1) OVER (PARTITION BY NAME ORDER BY NAME) cnt
  3  FROM (
  4  select slno,name,testno
  5  from
  6   (select slno,name,testno,
  7    ROW_NUMBER() OVER(PARTITION BY NAME, TESTNO ORDER BY NAME) RN
  8   from t)
  9  where RN =1
 10* )) WHERE cnt=4
SQL> /

no rows selected

SQL>
SQL> ed
Wrote file afiedt.buf

  1  SELECT slno,name,testno FROM (
  2      SELECT slno,name,testno, count(1) OVER (PARTITION BY NAME ORDER BY NAME) cnt
  3      FROM (
  4      select slno,name,testno
  5      from
  6       (select slno,name,testno,
  7        ROW_NUMBER() OVER(PARTITION BY NAME, TESTNO ORDER BY NAME) RN
  8      from t)
  9      where RN =1
 10* )) WHERE cnt=4
SQL> /

      SLNO NAME       TESTNO
---------- ------ ----------
         6 Thomas          1
         7 Thomas          2
         8 Thomas          3
         9 Thomas          4

SQL> select * from t;

      SLNO NAME       TESTNO
---------- ------ ----------
         1 reni            1
         2 arun            2
         3 jithu           3
         4 rajesh          1
         5 shiju           1
         6 Thomas          1
         7 Thomas          2
         8 Thomas          3
         9 Thomas          4

9 rows selected.

[PRE]


Regards

ushitaki

If that was not unique on combining name and testno, then it gets wrong results.
But, you have goot point.

SQL> with t as(
  2  select 1 slno,'reni' name, 1 testno from dual union all
  3  select 2,'arun',2  from dual union all
  4  select 3,'jithu',3 from dual union all
  5  select 4,'rajesh',4 from dual union all
  6  select 5,'shiju',1 from dual union all
  7  select 6,'Thomas',1 from dual union all
  8  select 7,'Thomas',2 from dual union all
  9  select 8,'Thomas',3 from dual union all
 10  select 9,'Thomas',4 from dual union all
 11  select 10,'Thomas Jr.',1 from dual union all
 12  select 11,'Thomas Jr.',1 from dual union all
 13  select 12,'Thomas Jr.',2 from dual union all
 14  select 13,'Thomas Jr.',2 from dual union all
 15  select 20,'Thomas 3rd',1 from dual union all
 16  select 21,'Thomas 3rd',2 from dual union all
 17  select 22,'Thomas 3rd',3 from dual union all
 18  select 23,'Thomas 3rd',4 from dual union all
 19  select 24,'Thomas 3rd',5 from dual
 20  )
 21  select * from t
 22  where name in
 23          (select name from t
 24          group by name
 25          having sum(decode(testno,1,1,2,1,3,1,4,1,0))=4)
 26  ;

      SLNO NAME           TESTNO
---------- ---------- ----------
         9 Thomas              4
         8 Thomas              3
         7 Thomas              2
         6 Thomas              1
        13 Thomas Jr.          2
        12 Thomas Jr.          2
        11 Thomas Jr.          1
        10 Thomas Jr.          1
        24 Thomas 3rd          5
        23 Thomas 3rd          4
        22 Thomas 3rd          3
        21 Thomas 3rd          2
        20 Thomas 3rd          1

13 rows selected.

SQL> edit
Wrote file afiedt.buf

  1  with t as(
  2  select 1 slno,'reni' name, 1 testno from dual union all
(snip)
 19  select 24,'Thomas 3rd',5 from dual
 20  )
 21  select * from t
 22  where name in
 23          (select name from t
 24          group by name
 25*         having count(distinct (case when testno in (1,2,3,4) then testno else null end))=4)
SQL> /

      SLNO NAME           TESTNO
---------- ---------- ----------
         9 Thomas              4
         8 Thomas              3
         7 Thomas              2
         6 Thomas              1
        24 Thomas 3rd          5
        23 Thomas 3rd          4
        22 Thomas 3rd          3
        21 Thomas 3rd          2
        20 Thomas 3rd          1

9 rows selected.

By the way, I don't know whether getting 'Thomas 3rd' is correct or not.

585319

There is an elegant and (of course) mathematical solution... try

select name, sum(testno) from tests_record group by name having sum(testno) >= (1+2+3+4+...+n)

n = whatever the number of tests have been applied
n=2 s=3, n=3 s=6, n=4 s=10, n=5 s=15, n=6 s=21...

hope it helps

Aketi Jyuuzou

SQL> with t as(
  2  select 1 as slno,'reni' as name, 1 as testno from dual
  3  union select 2,'arun',2  from dual
  4  union select 3,'jithu',3 from dual
  5  union select 4,'rajesh',4 from dual
  6  union select 5,'shiju',1 from dual
  7  union select 6,'Thomas',1 from dual
  8  union select 7,'Thomas',2 from dual
  9  union select 8,'Thomas',3 from dual
 10  union select 9,'Thomas',4 from dual
 11  union select 10,'Thomas Jr.',1 from dual
 12  union select 11,'Thomas Jr.',1 from dual
 13  union select 12,'Thomas Jr.',2 from dual
 14  union select 13,'Thomas Jr.',2 from dual
 15  union select 20,'Thomas 3rd',1 from dual
 16  union select 21,'Thomas 3rd',2 from dual
 17  union select 22,'Thomas 3rd',3 from dual
 18  union select 23,'Thomas 3rd',4 from dual
 19  union select 24,'Thomas 3rd',5 from dual)
 20  select slno,name,testno
 21  from (select slno,name,testno,
 22        max(case when testno=1 then 1 else 0 end) over(partition by name) as "has1",
 23        max(case when testno=2 then 1 else 0 end) over(partition by name) as "has2",
 24        max(case when testno=3 then 1 else 0 end) over(partition by name) as "has3",
 25        max(case when testno=4 then 1 else 0 end) over(partition by name) as "has4"
 26        from t)
 27  where "has1" * "has2" * "has3" * "has4" = 1;

SLNO  NAME           TESTNO
----  ----------  ---------
   9  Thomas              4
   8  Thomas              3
   7  Thomas              2
   6  Thomas              1
  23  Thomas 3rd          4
  24  Thomas 3rd          5
  22  Thomas 3rd          3
  21  Thomas 3rd          2
  20  Thomas 3rd          1

select slNO,name,testNO
  from t a
 where name in(select b.name
                 from t b
                where b.testNO in(1,2,3,4)
               group by b.name
               having count(distinct b.testNO) = 4)
order by slNO;

580343

may be this may work.

SELECT * FROM TABLENAME WHERE TESTNO IN ('1', '2', '3', '4');

430321

OP has requirement of finding whether all the tests have been attempted. Your solution will return result if student attempts any one of 1,2,3,4

587012

Hi,

This will work for you

select name,count(testno) from <table name>
group by name
having count(test)=4

587012

Hi,

Or u can try this for detail

select name,test
from xx
where name in (select name
from xx
group by name
having count(test)=4
)
order by name,test

1 - 23

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 11 2012

Added on Oct 14 2012

#php

1 comment

1,337 views

DevOps, CI/CD and Automation

[HELP] problem with oci8 in php

Comments

Post Details