Need help Inserting/deleting from a database

[orfiyus]

whats good

I am working on a php script. It takes in user input from a text field and then queries the database for it. If the input is found in the database then script performs a delete query to get that input out of the database. If it does not exist in the db then script inserts it. The problem I am having is concerns the variable that is used to store the users input from the text field. If I input something it inserts it into the database however when I try to insert something else right after the previously inserted input gets deleted in favor of the new input. This script is designed to only be able to delete what it has inserted. So data entered into the same db thru sql commands, other scripts etc. cant be touched. Can someone give me an idea on what I can do correct this bug?


Thanks

Heres some code pieces (the script is too big to post the whole thing):

//create text field
echo("<input type=text name=opt_qty value='".$_SESSION['opt_qty_vals']."' onBlur='submit();'  class='qty_input'> ");
//variable to store user input
$user_input_strike = $_REQUEST['opt_qty'];
 
 
 
//Counts number of times input appears in db depending on specified details.
$query_count = "SELECT COUNT(RAW_VOLS.STRIKE) FROM SMIRK_USR.RAW_VOLS RAW_VOLS WHERE (RAW_VOLS.STRIKE = $user_input_strike) AND (RAW_VOLS.ASOF_DATE=".$_SESSION['aod_val'].") AND (RAW_VOLS.SRC=".$_SESSION['src_val'].") AND (RAW_VOLS.SHARE_NAME=".$_SESSION['undl_val'].") AND (RAW_VOLS.CALL_PUT='".$_SESSION['opt_cp_vals']."') AND (RAW_VOLS.EXPIRE_DATE=".$_SESSION['opt_exd_vals'].")";
 
//code to retrieve actual result of the query aka the count
$parsed1 = ociparse($db_conn, $query_count);
$succ1 = ociexecute($parsed1);
//store count in array
$result = oci_fetch_array($parsed1);
$count_result = $result[0];
 
//If count returns zero insert into DB.
if ($count_result < 1)
{
    $insert_query = "insert into SMIRK_USR.RAW_VOLS (ASOF_DATE, SHARE_NAME, EXPIRE_DATE, CALL_PUT, STRIKE, OPEN_INT, SRC, PROCESSED) values($AOD_value, $UNDL_value, $expire_date_value, '$call_put_value',  $user_input_strike, $open_interest, $Source_value, '$processed_column')";
    $parsed_insert = ociparse($db_conn, $insert_query);
    $succ_insert = ociexecute($parsed_insert);
}
 
//else if count is greater then zero and the input has been inserted by this script
//delete the input.  
elseif (($count_result > 0) && ($rset_results_element0 < 0))
{
    $delete_query = "DELETE FROM SMIRK_USR.RAW_VOLS WHERE ( (ASOF_DATE = $AOD_value) AND (SHARE_NAME = $UNDL_value) AND (EXPIRE_DATE = $expire_date_value) AND (CALL_PUT = '$call_put_value') AND (STRIKE = $user_input_strike) AND (OPEN_INT = $open_interest) AND (SRC = $Source_value) )";
    $parsed_delete = ociparse($db_conn, $delete_query);
    $succ_delete = ociexecute($parsed_delete);
}
 
//else do nothing.  
else
{
    //echo("Do nothing");
}
 
 

.

[pbmods]

Heya, orfiyus.

Actually, we usually like it better when people don't post their entire scripts. It shows that:

1. You actually tried to fix the problem yourself first before asking for help, and
2. You're knowledgeable enough about your own code to know where the problem should be.

In regards to your problem, does your table have a primary key? Try SELECTing that when you first check to see if the entry exists, then use it to DELETE the existing row if necessary.

[orfiyus]

Itd probly help if I further explained what this script does. It queries the db and outputs a list of numbers. Then it comes out with the input field. When the user inputs a number that isnt currently in the database the page refreshes and the new number is added to the list. The problem I am having is that when I try to input another number that doesnt exist the page refreshes and the first input number is gone and replaced with the second input number. I think its a problem with either the variable I am using to store the user input or its a problem with my logic for the Inserting/deleting part. I tried using unset() to destroy the variable after its done being used but this didnt work. I dont know what the pk is because I dont have a db schema and I dont have direct access to the database anyway. I can only do things to the db through php. Any one else have any suggestions?

Thanks for reading though all of this.

[pbmods]

Heya, orfiyus.

Try running a DESCRIBE query on the table to determine its structure:

$_res = mysql_query('DESCRIBE `SMIRK_USR`.`RAW_VOLS`);
echo '<table>';
while($row = mysql_fetch_row($_res))
{
    echo '<tr>';
    foreach($row as $val)
    {
        echo "<td>$val</td>";
    }
    echo '</tr>';
}
echo '</table>';
 

[orfiyus]

I am using a oracle database so I basically I converted it to the best of my ability. The result I get for the output says its null. Which is weird because I actually see numbers on the screen and i am able to work with my insert and delete code portions.

Here is wut I said for the describe

echo("<table>");
//describe query
$query_describe = "DESCRIBE SMIRK_USR.RAW_VOLS";
//parse the sql code
$Describe_parse = ociparse($db_conn, $query_describe);
//execute it just incase
$Describe_execute = ociexecute($Describe_parse);
//store the results of the execute in array
$describe_array = oci_fetch_array($Describe_parse);
 
echo("<br><br>");
//var_dump it because its just easier 
var_dump($Describe_array);
 
echo("</table>");
 
 

[pbmods]

Heya, orfiyus.

Ah. Well then. I think in Oracle, the proper syntax is 'DESC `TableName`' instead of MySQL's 'DESCRIBE `TableName`'.

[orfiyus]

I changed it but I still get null outputted to the screen.

[pbmods]

Heya, orfiyus.

I'm going to go ahead and move this thread to the Oracle forum, where our resident Experts will be better able to help you out.

[orfiyus]

Allright Thanks for trying man

Im gonna reevaluate the logic cuz I think the problem is in there

[orfiyus]

Mangaged to fix the insert part. But now the delete has the same problem as before.

Here a code sample maybe someone can see what is wrong with it. Maybe its logic or syntax cant figure it out. I tried using unset on the variable which stores the text field input but the whole script just went blank whenever I did that. Any help would be appreciated.

 
//else if the query for the count is 1 or greater and the
//option number is less then 0
//Delete the number out of the db with appropriate parameters.
elseif (($count_result > 0) && ($rset_results_element0 < 0))
{
 
        $open_interest = -1;
        $delete_query = "DELETE FROM SMIRK_USR.RAW_VOLS WHERE ( (ASOF_DATE = $AOD_value) AND (SHARE_NAME = $UNDL_value) AND (EXPIRE_DATE = $expire_date_value) AND (CALL_PUT = '$call_put_value') AND (STRIKE = $user_input_strike) AND (OPEN_INT = $open_interest) AND (SRC = $Source_value) )";
        $parsed_delete = ociparse($db_conn, $delete_query);
        $succ_delete = ociexecute($parsed_delete);
        var_dump($succ_delete);
 
} 
 
 
 
 

[MMcCarthy]

Ar you trying to delete one field value from a larger table? If so you will need to use the UPDATE command instead of the DELETE command.

DELETE is only used when deleting full records from the table.

[orfiyus]

I am trying to delete it like this. The script queries with a count to see if the input exists. If the count is greater than zero and another part of the record is negative 1 then delete the whole thing where the user input number is.

For example take this make shift table

Thanks

1. Number::other_field1::other_field2
2. 136::-1::buddy
3. 137::65::whooo

The first line is the name of each row seperated by "::"
The lines after are what is in the db.


If I type in 136 into the webpage. I want everything deleted that is next to it. The -1 and the buddy. I want the whole record for 136 deleted. If I type 137 then it wont delete cuz the other field isnt -1.

More or less its doing what I want. The problem is I had to split it into two text fields. One text field does the inserting and the other text field does the deleting. I need the one text field to do both.

Hope you can help me out.

Thanks