how to insert data into oracle table using php

Question

I need help to insert value to oracle with PHP using post method. Not just that, I also need help to insert values to oracle from other tables. I did some coding but errors occurred such as

"Undefined index: PET_NAME", "oci_execute(): ORA-00911: invalid character", oci_fetch_array(): ORA-24374: define not done before fetch or execute and fetch.

Here I attached all my code and ERD in the link. Please help me. Thank you. I want to insert data to the treatment table from HTML form using post method and from other tables such as pet_id.

https://drive.google.com/drive/folders/1Cad2fiTwJRobBPxZf2rmopeENLjcPqIP?usp=sharing

Hi, interesting, perhaps there's a syntax error in the sql? Perhaps try printing the sql command and then running it in the console... — IronMan
– IronMan, Commented Jul 8, 2020 at 22:21
already did. a syntax error was right. I wonder if my code is wrong — Namikaze
– Namikaze, Commented Jul 9, 2020 at 1:58
Can you explain in more details about it. I'm not quite understand — Namikaze
– Namikaze, Commented Jul 9, 2020 at 7:46

score 1 · Accepted Answer · 2020-07-09 16:36:31Z

Treatment Table

Treatement (treat_id,
            staff_id,
            pet_id,
            treat_date,
            total_price)

Your insert statement is

 insert into TREATMENT(TREAT_ID.nextval,  --you should use sequence next val in values
                        PET_ID,
                        ;TREAT_DATE, ---why you have a semi colon here???
                       TREAT_TIME,   --- this column does not exist in Treatment table
                        TOTAL_PRICE)'. 'values
                       (:TREAT_DATE_bv,
                        (select p.pet_id,p.pet_name from treatment t join pet p on t.PET_ID =p.pet_id where p.pet_name=:PET_NAME),-- you didn't use place holder for pet_name and why do you need p.pet_name here???
                        :TREAT_TIME_bv,
                        :TOTAL_PRICE_bv)

Above insert statement column names are not in order with values.

You need staff_name place holder to get staff_id and Your insert statement should be like this

INSERT INTO TREATMENT(treat_id,
                staff_id,
                pet_id,
                treat_date,
                total_price)
values 
          (treat_id.nextval,
          (select staff_id from staff where staff_name=:STAFF_NAME_bv),
          (select pet_id from pet where pet_name=:PET_NAME_bv),
           :TREAT_DATE_bv,
           :TOTAL_PRICE_bv)

Keep in mind above is standard sql adjust as per your php code.And on another note what happens if 2 or 3 pet_id's share same pet_name? It's best to have cust_id place holder to get pet_id i.e select pet_id from pet where pet_name=:PET_NAME_bv and cust_id=:CUST_ID_bv

Collectives™ on Stack Overflow

how to insert data into oracle table using php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related