Page 1 of 1

easy php/oracle update query

Posted: Thu Mar 24, 2005 8:33 am
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

Posted: Thu Mar 24, 2005 8:43 am
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.

Posted: Thu Mar 24, 2005 8:49 am
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.

Posted: Thu Mar 24, 2005 8:50 am
by feyd
please review how line offsets work in [syntax=php]: viewtopic.php?t=31382[/syntax]

Posted: Thu Mar 24, 2005 8:51 am
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.

Posted: Thu Mar 24, 2005 9:32 am
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);

Posted: Thu Mar 24, 2005 12:28 pm
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);