easy php/oracle update query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
total
Forum Newbie
Posts: 10
Joined: Thu Mar 24, 2005 8:21 am

easy php/oracle update query

Post by total »

this is my 1st post, but i plan to come back often because of some new stuff i'm doing... please be gentle ;)

our server has recently been moved from php 3 to php 5. the code differences are enough, but on top of that, i'm going to an Oracle db instead of mysql. i'm learing it fairly quickly, but for some crazy reason, the code below won't work and gives this error:

Warning: ociexecute() [function.ociexecute]: OCIStmtExecute: ORA-00904: invalid column name in C:\Inetpub\wwwroot\faciltst\HOMSstatchng.php on line 72

why is it blowing up on the ociexecute statement when it works just fine for a select statement about 10 lines up?


here is the code - i just want to insert into a field (simple enough)

Code: Select all

<?php 
71  $updateqry1 = OCIParse($Link, "UPDATE ASSIGNMENTS SET TERM = '$term' WHERE ASSIGNMENTS_STUDENTS_ID = '$sid'");
72  OCIExecute($updateqry1);
73  OCILogoff($Link);
?>



thankx
Shane
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Sounds like your SQL command is wrong. It's a long time since I played with Oracle but are the column/table names case sensitive ? Check the column names and case.

Also if you want to insert shouldn't you use the SQL command INSERT rather than UPDATE ?

Finally just a notice. I would change the variable $sid to something like $student_id as $SID if often used when referencing session information and I like to avoid any possible confusion.
total
Forum Newbie
Posts: 10
Joined: Thu Mar 24, 2005 8:21 am

Post by total »

oops... actually, i'm not inserting - i do want to update.

i don't know if the table/column names are case sensitive or not, but i believe they are with Oracle, so that's why i have everything uppercase (since the column names are uppercase in the db). they should mesh.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

please review how line offsets work in [syntax=php]: viewtopic.php?t=31382[/syntax]
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Can you access the DB directly and run the SQL command,obviously replacing variables with dummy data, outside of PHP. Does the SQL command work. If not correct your SQL first then worry about the PHP.
total
Forum Newbie
Posts: 10
Joined: Thu Mar 24, 2005 8:21 am

Post by total »

i did get it to connect and work using a SELECT statement... but when i change to UPDATE, it still blows up on the execute line.

this works

Code: Select all

$term = $_REQUEST['term'];
    $sid = $_REQUEST['sid'];

    $updateqry1 = OCIParse($Link, "SELECT * FROM ASSIGNMENTS WHERE ((ASSIGNMENTS_TERM = '$term') AND (ASSIGNMENTS_STUDENTS_ID = '$sid'))");

    OCIDefineByName($updateqry1, "ASSIGNMENTS_TERM", $term2);
    OCIExecute($updateqry1);
    OCIFetch($updateqry1);

    OCIFreeStatement($updateqry1);
    OCILogoff($Link);

echo $term2;

this does not???

Code: Select all

$term = $_REQUEST['term'];
    $sid = $_REQUEST['sid'];

    $updateqry1 = OCIParse($Link, "UPDATE ASSIGNMENTS SET ASSIGNMENTS_TERM = '$term' WHERE ASSIGNMENTS_STUDENTS_ID = '$sid'");
    OCIExecute($updateqry1);
    OCILogoff($Link);
total
Forum Newbie
Posts: 10
Joined: Thu Mar 24, 2005 8:21 am

Post by total »

i always post my answers..... 1) for others that may have the problem. 2) incase i have the problem again.

there were 2 problems
1) the "term" coming in from the form is $newterm and the old term was $term... so i had to fix that
2) $sid is a number field and didn't require the single quote (') around it. i didn't know that?

here is the correct code

Code: Select all

$updateqry1 = OCIParse($Link, "UPDATE ASSIGNMENTS SET ASSIGNMENTS_TERM = '$newterm' WHERE ASSIGNMENTS_STUDENTS_ID = $sid");
    OCIExecute($updateqry1);
    OCILogoff($Link);
Post Reply