...

View Full Version : SELECT and then INSERT in a for loop



jeskel
10-26-2003, 12:40 PM
Hi,

here I'm facing some problems I can't solve myself... I've been trying to make google searchs to find answers to my problems
and went on w3school but no way... I can't make it. So here it goes: my users should be able to send each other messages. They enter
the names of the people to which they wanna send their message manually (each name being separated by a ",") or using a form
with checkboxes (recordset moveNext/wend showing all their contact's names). Then I request.form all the names entered and split them.
Then, for each name in my array, I must get the id of this name in the "member" table and record the id, the sender_name, the message etc... in the "messages" table...

I know how to join two tables in order to SELECT infos,
I can INSERT data in a for loop, but I can't make the two work together...



sender_name = Session("sender")
sender_id = Session("sender_id")
subject = request.form("subject")
message = request.form("message")
size = len(message)
receiver_names= split(request.form("receivers"), ",")


for x = lbound(receiver_names) to ubound(receiver_names)

'(***no code, just the idea***)
'select the id of each receiver in my array in the "member" table where user_name = receiver
'receiver_id=rs("id")
'and then for each receiver, record all the infos in the "messages" table
'something like (the sql variable being written before the for loop of course...):
'sql="INSERT INTO messages (receiver_name, receiver_id, sender_name, sender_id, subject, message, size) VALUES ('" & replace(receiver_names(x),"'","''") & "', " & receiver_id & ", '" & replace(sender_name,"'","''") & "', " & sender_id & ", '" & replace(subject,"'","''") & "', '" & replace(message,"'","''") & "', " & size & ")"
'MyConn.execute sql

next

if you want me to post some more of my code, just ask... but that would be pretty much irrelevant...

any help is welcome....

Mhtml
10-26-2003, 12:59 PM
Well my ASP is a little rusty since I converted to PHP so I'll just give you algorithmic sort of advice. :D

Personally I don't see what is so complicated about what you want to do.

To keep it simple why not have a function to return the unique id for each of the users depending on the string name you send as the argument?

SELECT id FROM users WHERE name = '"&strName&"'

That'd be pretty easy, just cycle through the member name array replacing the name with the id using the function to return the id.

Once you have the array done, just cycle through the array again and everytime you hit an integer (use IsNumeric()) insert the message into the database using another function.

INSERT into messages SET msg = '"&message&'', from = '"&from&"', user = '"&toID

Conceptually that'd work, it's simplistic yet it's also complex. I'd go for a more complex yet simple approach. By that I mean you can do it quicker but the concept may be more complex to understand.

[edit:] Added the IsNumeric() thing.

raf
10-26-2003, 01:00 PM
why do you split up the receivernames?

if you use the IN operator, then it's just perfect that you have the names as a commadelimited list. The only thing you would need to do is add quotes around each receivers name. (so you need to write a function for this)

then you can use
select ID from member where user name IN (your commadelimeted and quoted list)

To do the insert, you could use a select into.

Like
INSERT INTO messages (var1, var2) SELECT members.var1, members.var2 FROM members WHERE ID In (SELECT ID FROM member WHERE user_name In (your commadelimeted and quoted list)

So in one query, you select the ID's as a subselect, get the variables from these records and insert them in the memberstable
The variables in the selectlist need to be in the same order as the ones from the insertlist (like in a regular insertquery) so that you insert the values in the right column.

But can't you just insert the memberID in the message table ? And then, whenn you need to show or proces them, join the messages and members table. Your current setup looks like some unnescecry data-redundency .... (but then igain, i don't know your app.)

jeskel
10-26-2003, 01:18 PM
thank you guys for answering that quick (you also have rainy boring sunday where you come from?;)) I'll think about your advices, try some stuff and re-post something if I'm still in trouble. But thanx again for your ideas :thumbsup:

Mhtml
10-26-2003, 01:20 PM
I am unfamiliar with In, a stored procedure in access?

[edit:] Rainy? Nope, boring? Yes. :p Spent the whole day bashing my head on my desk with problems writing my game engine.

jeskel
10-26-2003, 01:25 PM
Originally posted by raf
The only thing you would need to do is add quotes around each receivers name. (so you need to write a function for this)
I'm a bit confused. how would you do that?

<edit>

Originally posted by raf
But can't you just insert the memberID in the message table ? And then, whenn you need to show or proces them, join the messages and members table. Your current setup looks like some unnescecry data-redundency .... (but then igain, i don't know your app.)

since the user will enter the names of the people to whom they want to send the message, I can get their ID only after checking for the names... I could avoid re-enter the receiver_name, but I would then link two tables in my display page... I'll do that I think, that will save me a column in one of my tables.

I could also disable the text field and let the users choose their contacts only with checkboxes and get their ID (receiver_id) in an hidden field, which would avoid a lot of problems (wrong name, typing error, coma forgotten...). But I would prefer to trust my users and give them flexibility (I'll probably regret it one day or the other)...


Originally posted by Mhtml
Rainy? Nope, boring? Yes. Spent the whole day bashing my head on my desk with problems writing my game engine.

off-topic: I guess my sunday would be less boring if the weather was nice... Take care of your head ;)

</edit>

raf
10-26-2003, 04:08 PM
to mak it a quoted list:


dim quoted
quoted = "'" & replace(original,",","','") & "'"


Where originel isa variable containing the list. (so you just add an quote at the start and end of the string, and then replace all commas by ','

Checkboxes are of course safer...


About "In" --> its a fairly universal clause. You have it in mySQL and most other db-formats as well. It enables you to run a where clause on a collection of values. It's opponent is 'Not In'.

jeskel
10-26-2003, 06:17 PM
thanx a lot raf... it's always a pleasure to learn nice tricks from you:thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum