Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SELECT and then INSERT in a for loop

    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...

    Code:
    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....
    Last edited by jeskel; 10-26-2003 at 12:44 PM.

  • #2
    Senior Coder Mhtml's Avatar
    Join Date
    Jun 2002
    Location
    Sydney, Australia
    Posts
    3,531
    Thanks
    0
    Thanked 1 Time in 1 Post
    Well my ASP is a little rusty since I converted to PHP so I'll just give you algorithmic sort of advice.

    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.
    Last edited by Mhtml; 10-26-2003 at 01:18 PM.
    Omnis mico antequam dominus Spookster!

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.)

  • #4
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #5
    Senior Coder Mhtml's Avatar
    Join Date
    Jun 2002
    Location
    Sydney, Australia
    Posts
    3,531
    Thanks
    0
    Thanked 1 Time in 1 Post
    I am unfamiliar with In, a stored procedure in access?

    [edit:] Rainy? Nope, boring? Yes. Spent the whole day bashing my head on my desk with problems writing my game engine.
    Last edited by Mhtml; 10-26-2003 at 01:23 PM.
    Omnis mico antequam dominus Spookster!

  • #6
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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>
    Last edited by jeskel; 10-26-2003 at 02:52 PM.

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    to mak it a quoted list:
    Code:
    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'.

  • #8
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanx a lot raf... it's always a pleasure to learn nice tricks from you


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •