mary07
Posts: 16
|
| Posted: 07/27/2007, 6:36 AM |
|
I am working on processing user uploaded csv files by creating a temporary table, using that as the source of an editable grid so the user can make any corrections neccessary - and easy validation for me - then after the user submits changes - a series of sql executes to update the appropriate parts of the database. But, I'm stuck! Each portion of my code works individually, I'm just running into a problem with where to execute the last portion - the portion that runs the series of sql. Here is how it is set up currently.. Files Upload GOOD ... then Temp Table Creates (on Before Build Select of the Grid) GOOD ... then Editable Grid displays from temp table GOOD ... then AfterSubmit.. run the sql PROBLEM the temp table no longer exists..
So, I'm guessing the session is ended on submit?
I am re-directing to a page that just says "Upload Successful".. so I could possibly run the sql before show of that page.. but I need to maintain the session across those pages...
I have tried everything I can think of. I already read the example for multi-page form submission & searched the forums and couldn't come up with a solution. Help!! Any and all help would be greatly appreciated!!
|
 |
 |
DonB
|
| Posted: 07/27/2007, 7:43 AM |
|
If your page or the form 'redirects', then the database connection is closed
(see the Operation() method). This causes an implict DROP TABLE on the temp
table.
I suppose the easy way around this is to add After Insert code to copy the
data from temp table to persistent table, which would be one query doing
(more or less)
INSERT permtable SELECT * FROM temptable
--
DonB
http://www.gotodon.com/ccbth
"mary07" <mary07@forum.codecharge> wrote in message
news:546a9f4f82e25a@news.codecharge.com...
> I am working on processing user uploaded csv files by creating a temporary
> table, using that as the source of an editable grid so the user can make
any
> corrections neccessary - and easy validation for me - then after the user
> submits changes - a series of sql executes to update the appropriate parts
of
> the database. But, I'm stuck! Each portion of my code works
individually, I'm
> just running into a problem with where to execute the last portion - the
portion
> that runs the series of sql. Here is how it is set up currently.. Files
Upload
> GOOD ... then Temp Table Creates (on Before Build Select of the Grid) GOOD
....
> then Editable Grid displays from temp table GOOD ... then AfterSubmit..
run the
> sql PROBLEM the temp table no longer exists..
>
> So, I'm guessing the session is ended on submit?
>
> I am re-directing to a page that just says "Upload Successful".. so I
could
> possibly run the sql before show of that page.. but I need to maintain the
> session across those pages...
>
> I have tried everything I can think of. I already read the example for
> multi-page form submission & searched the forums and couldn't come up
with a
> solution. Help!! Any and all help would be greatly appreciated!!
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
mary07
Posts: 16
|
| Posted: 07/27/2007, 8:58 AM |
|
hmmm.. thank you for the suggestion..
I had originally used a temp table because I have multiple users who could be uploading at the same time (likely will be.) The procedure I'm using now, each user can upload up to 9 csv files.. then it loops through each file -read it - creates tmp table - displays grid - updates permanent tables.. drop tmp table.. until all files are read..
I wanted to make sure I didn't have problems with multiple users trying to upload to the same table at once.. I thought temp table would be the perfect solution. But.. it drops on submit.. I want to run my procedure for updating the permanent tables after the temp table is updated but before the session is ended.. is there such a time? and where is it?
If I need to use a permanent table.. I suppose I could make the name of the table dependent on the user or something to make it unique.. I just want to make sure I don't have conflicts with multiple users.. any thoughts?
|
|
|
|
DonB
|
| Posted: 07/27/2007, 9:45 AM |
|
What I was saying is, the temp table is not dropped on 'submit', it's
dropped when the connection your form opens gets closed. You have
opportunity to either bypass that 'close' or to process the temp table data
before the close happens. Follow the Operation() method code to see the
details of what's happening. The key is making sure the form, and the page
containing it, do not redirect to another page, as this invokes the
connection's Close() method; or by processing the temp table sooner (e.g.,
the AfterInsert event) if you still want to do the redirect.
--
DonB
http://www.gotodon.com/ccbth
"mary07" <mary07@forum.codecharge> wrote in message
news:546aa1635c7f4b@news.codecharge.com...
> hmmm.. thank you for the suggestion..
>
> I had originally used a temp table because I have multiple users who could
be
> uploading at the same time (likely will be.) The procedure I'm using now,
each
> user can upload up to 9 csv files.. then it loops through each file -read
it -
> creates tmp table - displays grid - updates permanent tables.. drop tmp
table..
> until all files are read..
>
> I wanted to make sure I didn't have problems with multiple users trying to
> upload to the same table at once.. I thought temp table would be the
perfect
> solution. But.. it drops on submit.. I want to run my procedure for
updating
> the permanent tables after the temp table is updated but before the
session is
> ended.. is there such a time? and where is it?
>
> If I need to use a permanent table.. I suppose I could make the name of
the
> table dependent on the user or something to make it unique.. I just want
to make
> sure I don't have conflicts with multiple users.. any thoughts?
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 07/27/2007, 2:49 PM |
|
Mary,
The temp table is persistent for the duration of the connection.
I would approach it slightly different.
I would create a PERMANENT table. This table would have as one of its
fields the sessionid - this way the records cab easily be extracted.
YOu would do the same thing you are doing now except you queries would take
into consideration the new column in the table (the sessionid), and on
completion you would delete all records form the "temporary table" where the
session id is the current sessio id.
|
|
|
|
mary07
Posts: 16
|
| Posted: 07/31/2007, 3:33 PM |
|
Thank you so much for your help! I have almost got it working.. but of course have another problem.. I used the loginid to identify the person and the file name - those two together are unique for sure.. so anyway.. I am stuck on my last sql.. it works fine when i use my gui for mysql and just type in the userid.. when I use my script, it has an error saying there's an error in my sql - it drops the ' . Say the user is mary, then it looks like it prints out userid=mary' instead of userid='mary' I'm sure I'm just doing something stupid.. but I would really appreciate any help you could give me!!! Here's my code:
$user=CCGetSession("UserLogin");
$import="INSERT into tblWorkers(WorkerID,FirstName,etc..) SELECT WorkerID,FirstName,etc... FROM tmptblpalmexl Where tmptblpalmexl.userid='$user'
on duplicate key update tblWorkers.FirstName=tmptblpalmexl.FirstName,etc...;
";
|
|
|
|
wkempees
|
| Posted: 07/31/2007, 9:05 PM |
|
$user=CCGetSession("UserLogin");
$import="INSERT into tblWorkers(WorkerID,FirstName,etc..)
SELECT WorkerID,FirstName,etc...
FROM tmptblpalmexl
Where tmptblpalmexl.userid=" . $user
. " on duplicate key update
tblWorkers.FirstName=tmptblpalmexl.FirstName,etc...";
Change is at the line starting with Where, note the: " . $user . " on
construction.
Walter
"mary07" <mary07@forum.codecharge> schreef in bericht
news:546afb8b7e2b3a@news.codecharge.com...
> Thank you so much for your help! I have almost got it working.. but of
> course
> have another problem.. I used the loginid to identify the person and the
> file
> name - those two together are unique for sure.. so anyway.. I am stuck on
> my
> last sql.. it works fine when i use my gui for mysql and just type in the
> userid.. when I use my script, it has an error saying there's an error in
> my sql
> - it drops the ' . Say the user is mary, then it looks like it prints out
> userid=mary' instead of userid='mary' I'm sure I'm just doing something
> stupid.. but I would really appreciate any help you could give me!!!
> Here's my
> code:
>
> $user=CCGetSession("UserLogin");
> $import="INSERT into tblWorkers(WorkerID,FirstName,etc..) SELECT
> WorkerID,FirstName,etc... FROM tmptblpalmexl Where
> tmptblpalmexl.userid='$user'
> on duplicate key update
> tblWorkers.FirstName=tmptblpalmexl.FirstName,etc...;
> ";
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
mary07
Posts: 16
|
| Posted: 08/01/2007, 6:31 AM |
|
I tried your suggestion Walter, but now I get error message that mary is not a column. I need the end result to be userid='mary' so that mysql knows it is text and not a field.
I tried making that line read:
userid=" . $user . " - error in sql - 'mary' is not a column
or
userid='" . $user . "' - error in sql near userid=mary'
or
userid=".'$user'." - error in sql near userid='$user'
or
userid=' .$user. ' - error in sql near Insert into tblWorkers(WorkerID, FirstNa
but those didn't work either.
Any more suggestsions?? Thanks you!
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/01/2007, 11:17 AM |
|
Where it says double quote dot and dot double quote
add a single quote just before and just after the double quotes
like:
userid='". $user ."'
walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mary07
Posts: 16
|
| Posted: 08/01/2007, 11:33 AM |
|
I tried that.. I get a syntax error message near userid=mary'
it prints out in the error without single quotes around the text.. But when I take the whole thing and just run it in Navicat.. using userid='mary' the sql works fine.. so I know this is just a quote issue with my php.. how frustrating!! Thank you for your help!! Any other suggestions??
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/01/2007, 12:54 PM |
|
DISREGARD, read next post!
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/01/2007, 1:06 PM |
|
The original reworked:
$user=CCGetSession("UserLogin");
$import='INSERT into tblWorkers(WorkerID,FirstName,etc..)
SELECT WorkerID,FirstName,etc...
FROM tmptblpalmexl
Where tmptblpalmexl.userid=' . $DBConnection->ToSQL( $user , ccsText)
. ' on duplicate key update
tblWorkers.FirstName=tmptblpalmexl.FirstName,etc...';
Yes, I use single quotes, tested.
returns:
admin
INSERT into tblWorkers(WorkerID,FirstName,etc..) SELECT WorkerID,FirstName,etc... FROM tmptblpalmexl Where tmptblpalmexl.userid='admin' on duplicate key update tblWorkers.FirstName=tmptblpalmexl.FirstName,etc...
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/01/2007, 1:53 PM |
|
The reason for using single quotes and not double quotes:
http://www.php.net/types.string
also
remember to always use $DB<connection>->ToSQL( var, type)
when inserting data into a SQL string.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mary07
Posts: 16
|
| Posted: 08/01/2007, 2:07 PM |
|
THANK YOU!!! THANK YOU!!! THANK YOU!!!
It works perfect! I was ready to pull my hair out, I was so frustrated. You made my night!! Thank you again!
|
|
|
|
wkempees
|
| Posted: 08/01/2007, 2:13 PM |
|
Which brings up several more questions.
How to make your day.
What color hair and how much before show.
Just kidding, glad to be of service.
Walter
"mary07" <mary07@forum.codecharge> schreef in bericht
news:546b0f5fbd8b48@news.codecharge.com...
> THANK YOU!!! THANK YOU!!! THANK YOU!!!
>
> It works perfect! I was ready to pull my hair out, I was so frustrated.
> You
> made my night!! Thank you again!
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
