Lost Data in Array

rob_n

I have to extract a dataset/reorder it, delete the existing dataset from the table and then insert the newly ordered set back in.

However, I "lose" the data after the delete statement, for example:

$result = sqlanywhere_query( $bhbr_conn, "SELECT number(), chlst_val_id, number(), chlst_val_seq_nbr, chlst_cde, chlst_cde_tranl_txt from tb_chl_codetrans where chlst_id = 200298 order by chlst_cde_tranl_txt");

$del_result = sqlanywhere_query( $bhbr_conn, "DELETE FROM TB_CHL_CODETRANS WHERE CHLST_ID = 200298");

while($data = sqlanywhere_fetch_row( $result ) ){

$string2 = "INSERT INTO TB_CHL_CODETRANS (CHLST_ID,CHLST_VAL_ID,CHLST_VAL_SEQ_NBR,CHLST_CDE,CHLST_CDE_TRANL_TXT,CHLST_VAL_SELBL_IND) VALUES ( 200298,{$data['0']},{$data['2']},{$data['4']},'{$data['5']}','N')";

echo $string2;

}

So, in this example, echo $string2 fails to output anything. If I comment out the delete statement $string2 echoes everything correctly.

How can make sure PHP isn't throwing away the data in my first array?

Thanks!

-Rob

eli03

base from your script you select the data, and delete it. it make no sense. why do you want to extract the data? you can order the data using
example
Select from tablename ORDEY BY CHLST_ID ASC; or
Select from tbalename ORDEY BY CHLST_ID ASC;

rob_n

Well, I have to re-insert the data after it is ordered.

So I insert a record, select the whole data set (order by), delete the data set from the table and then insert the newly ordered set.

So:

1) INSERT
2) SELECT (ORDER BY)
3) DELETE
4) INSERT

Thanks!

-Rob

eli03

do you want to process th insert,select, delete at the same time? if that is what you want , you need to declare the session variable for each data, so that the data still remain even the data is deleted in the database. And that session variable will be inserted to the second insert statement.

rob_n

hmmm, that sounds like the right track, here is what I tried for debugging purposes:

$_SESSION['result'] = sqlanywhere_query( $bhbr_conn, "SELECT number(), chlst_val_id, number(), chlst_val_seq_nbr, chlst_cde, chlst_cde_tranl_txt from tb_chl_codetrans where chlst_id = 200298 order by chlst_cde_tranl_txt");

$_SESSION['del_result'] = sqlanywhere_query( $bhbr_conn, "DELETE FROM TB_CHL_CODETRANS WHERE CHLST_ID = 200298");

while($SESSION['data'] = sqlanywhere_fetch_row( $SESSION['result'] ) ){

while( $_SESSION['bar'] = each ($_SESSION['data']) ) print " ".$_SESSION['bar'][0]."=>".$_SESSION['bar'][1];

}

I still get nothing if the delete statement is executed before the while statements.

Thanks!

eli03

=) sorry i forgot, even using session don't work cause you are still using data coming from database and that is uqual to each session variable. why do want to this function? why not using update function, since your inserting the same data in one table

bodzan

...in delete statement's WHERE part...

it should look like:
WHERE CHLST_ID = '200298'

If I'm right, I think because your stetement misses quotes the where condition cannot be met and it will delete all the data from your databse because of it...

rob_n

Unfortuanately no. I wish though!

CHLST_ID is an integer, so it works without quotes.

I still cannot get $_session variables to work at holding onto the result set after the delete.

rob_n

eli03, thanks for update.

I cannot update because two of the columns are indexes and I cannot remove the constraints (permissions).

In fact, a thick client front end does indeed delete the the data set from the table and then re-inserts the newl ordered set.

I'm attempting to mimick this in PHP, using dynamic db connection strings a sql since we have over 90 of these databases on a wan and using the gui client is totally unrealistic.

Thanks!

eli03

I think the only way is to create a temporary database for storage

rob_n

eli03,

Temp table for storage was the way to do this, thank you.

I'm populating the INSERT statement dynamically and have a " ' " in the data.

How can I get around that?

For example:

data = 'o'to'

"INSERT INTO TB_CHL_CODETRANS (CHLST_ID,CHLST_VAL_ID,CHLST_VAL_SEQ_NBR,CHLST_CDE,CHLST_CDE_TRANL_TXT,CHLST_VAL_SELBL_IND) VALUES (200298,{$data2['0']},{$data2['2']},'{$data2['4']}','{$data2['5']}','N')"

So, in the case

'{$data2['5']}'

gets 'o'to' and the statement fails.

Thanks!

-Rob

bodzan

\'{$data2['5']}\'

Sorry, I've missed the escape them as I wanted to show you...

Try this...

rob_n

That is what i have.

I think addslashes() might work, but I'm having difficulty finding the right combination.

bodzan

I forgot escape signs...

Edited my previous post to look like I wanted...

rob_n

heh, that's funny were talking about escaping!

Anyway, with your suggestion I get:
PHP Warning: SQLAnywhere: [-131] Syntax error near '\'

I think because the variable needs to be enclosed by a " ' "

bodzan

How come you wrote this with no " ' " around it?

{$data2['0']}

Try to avoid " ' " and you'll see if this is the right way to write it. If it is I would like to know it for one...

rob_n

Again, here is the literal string used in my query:

INSERT INTO TB_CHL_CODETRANS (CHLST_ID,CHLST_VAL_ID,CHLST_VAL_SEQ_NBR,CHLST_CDE,CHLST_CDE_TRANL_TXT,CHLST_VAL_SELBL_IND) VALUES (200298,{$data2['0']},{$data2['2']},'{$data2['4']}','{$data2['5']}','N')"

If use escaping:\ \'{$data2['5']}\'

it will fail, because the ' needs to enclose the variable.

bodzan

"INSERT INTO TB_CHL_CODETRANS (CHLST_ID,CHLST_VAL_ID,CHLST_VAL_SEQ_NBR,CHLST_CDE,CHLST_CDE_TRANL_TXT,CHLST_VAL_SELBL_IND) VALUES (200298,{$data2['0']},{$data2['2']},' ".{$data2['4']}
." ' ".",' ".{$data2['5']}." ' ".",'N')"

Or something similar...

rob_n

The solution, in the end, was to use str_replace() and find all occurences of ' and replace with ''.

This allows the escaping of the ' and lets single quotes be entered in the db.

I would like to say that motivation and quality of responses from this community continues to amaze me. I am leading the charge in bringing PHP and other opensource technologies into a very large financial insitution (>4,000 users). Your efforts continue to show the positive shift in philosophy and culture that is entering the mainstream corporate world.

Thank you.

-Rob