Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Loop help

Quote Reply
Loop help
I am currently writing an internal email script into DBManSQL V1 and have run into a problem. I haven't gotten to the point of figuring out loops and how to set them up to work with queries.
Here is what I have so far:

print qq| <b><input type="checkbox" name="ID" value="$row->{ID}">&nbsp;<font color="black"><$sizefont>$row->{date}</font><$sizefont><font color="black"> - <a href="$db_script_link_url&ID=$row->{ID}&show_message=1&ww=on">$row->{received}</a></font><$sizefont><font color="black"> - $row->{subject}</font></b><hr> |;

That is a message link with checkbox. The checkbox is for selecting single or multiple (if I can get it working) messages for marking unread, deleting etc. Here is the sub for marking messages unread:

sub mark_unread {
# --------------------------------------------------------
my $ID_q = $DBH->quote($in{'ID'});
$query = qq!
UPDATE $db_comments SET messageread = '0'
WHERE ID = $ID_q
!;
$rc = $DBH->do($query);
$rc ?
($message_q = "User: $in{'username'} updated.") :
($message_q = "Error updating user: $in{'username'}. Reason: $DBI::errstr");
if ($rc) {
&html_view_messages($message);
}
else {
&html_view_messages($message);
}
}

So far, that will work if I check 1 message and click the submit button. But I need this to be able to work if more than 1 message is checked. How can I get this to work with more than 1 message being checked?
Thanks for any help!
Shannon
Quote Reply
Re: [shann123] Loop help In reply to
You should do a foreach loop around the inner code, and loop the @{$IN->{'ID'}} array.
Note: multiple checkboxes with same name are collected into array reference in the input variable $IN.

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [webmaster33] Loop help In reply to
So this line:
my $ID_q = $DBH->quote($in{'ID'});

should be change to this?:

my $ID_q = $DBH->quote(@{$IN->{'ID'}});

then do a foreach around the query? I still haven't grasped the foreach part of perl yet. I'm still an amature, but I've come a long way since I first installed dbman! Wink
Shannon
Quote Reply
Re: [shann123] Loop help In reply to
I don't know DBMan SQL very well, but basically, yes, this would be the logical way.
Give it a try, the worst case could be that you learn something Wink

Check first, that input is stored in $in or $IN.
I suppose $IN is the used one.

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [webmaster33] Loop help In reply to
OK, this is getting frustrating. The line:

my $ID_q = $DBH->quote(@{$in->{'ID'}});

causes this error:

Error Message : fatal error: DBI quote: invalid number of arguments: got handle + 0, expected handle + between 1 and 2
Usage: $h->quote($string [, $data_type ]) at ./mailbox_inbox.pl line 202.

So it seems that something is not right with that. I've been googling for several hours and nothing I am finding is helping me. While it'd be nice to allow members to select more than one message for moving, deleting, flagging etc, I might just have to put a note on there that only 1 message at a time can be selected. *sighs* Unsure

If you have any ideas, I'd be really happy to try them!
Shannon

BTW, except for that problem, this internal email system is working great and has a lot of features! It's almost like squirrelmail, ok, not quite that many features, but close.. lol

Last edited by:

shann123: Feb 27, 2005, 11:15 AM
Quote Reply
Re: [shann123] Loop help In reply to
Yes, because you pass the input parameters together instead one by one.
I did not notice that in your previous post.

Try something like this:
Code:
sub mark_unread {
# --------------------------------------------------------
foreach my $id (@{$IN->{'ID'}}) {
my $ID_q = $DBH->quote($id);
$query = qq!
UPDATE $db_comments SET messageread = '0'
WHERE ID = $ID_q
!;
$rc = $DBH->do($query);
$rc ?
($message_q = "User: $in{'username'} updated.") :
($message_q = "Error updating user: $in{'username'}. Reason: $DBI::errstr");
if ($rc) {
&html_view_messages($message);
} else {
&html_view_messages($message);
}
}
}

Also note, I don't know if you should use $in or $IN.
Try other if one doesn't work.
I did not check the rest of code, so still not guaranteed 100%.

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [webmaster33] Loop help In reply to
That doesn't seem to be working either. I was getting 500 server errors with that. I did a little playing around with it and I'm beginning to wonder if the values of the checkboxes are even being passed on to the sub. I turned DBMan's debugging on and it's only showing 1 ID being passed to the sub even after checking more than 1 box.

Unsure

Last edited by:

shann123: Feb 28, 2005, 6:36 AM
Quote Reply
Re: [shann123] Loop help In reply to
I changed things around a little bit to this:

foreach ($ID_q = (@in{'ID'})) {

$query = qq!
UPDATE $db_comments SET messageflag = '1'
WHERE ID = $ID_q
!;
$rc = $DBH->do($query);

$rc ?
($message_q = "Message Flagged.") :
($message_q = "Error Flagging Message. Reason: $DBI::errstr");

}

Doing it that way works as far as flagging 1 of the messages, but I still don't think it's getting all of the values. :(
Quote Reply
Re: [shann123] Loop help In reply to
Ok, let's debug a bit.
Place the following code before the code:
Code:
use Data::Dumper;
print "\$in: " . Dumper(\$in) . "\n<br>";
print "\$IN: " . Dumper(\$IN) . "\n<br>";
Let me know the result. If it's too long, PM it me.

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [shann123] Loop help In reply to
There are some errors in your code.

Give this a go....

Code:
sub mark_unread {
my $hash = $IN->get_hash;
my @list = exists $hash->{ID} && ref $hash->{ID} eq 'ARRAY' ? @{$hash->{ID}} : $hash->{ID};

for (@list) {
my $query = qq!UPDATE $db_comments SET messageread = '0' WHERE ID = ! . $DBH->quote($_);
return $DBH->do($query) ?
&html_view_messages("User updated.") :
&html_view_messages("Error updating user. Reason: $DBI::errstr");
}
}
Quote Reply
Re: [Paul_Wilson] Loop help In reply to
Hi Paul,
Thanks a bunch for your reply. Unfortunately, I got an error:

Error Message : fatal error: Can't call method "get_hash" on an undefined value at ./mailbox_inbox.pl line 233.

Line 233 is:
my $hash = $IN->get_hash;

Shannon
Quote Reply
Re: [shann123] Loop help In reply to
Change $IN to the name of the GT::CGI object used in DBMAN (is it $in?) - for other products it is $IN so I assumed it was the same.
Quote Reply
Re: [Paul_Wilson] Loop help In reply to
Still the same error after changing it to $in (yeah, DBMan SQL Version 1 uses $in).


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] Loop help In reply to
Ok I see the problem. %in is actually just a hash of parameters and not an object. Try this:

Code:
sub mark_unread {
my @list = exists $in{ID} && ref $in{ID} eq 'ARRAY' ? @{$in{ID}} : $in{ID};

for (@list) {
my $query = qq!UPDATE $db_comments SET messageread = '0' WHERE ID = ! . $DBH->quote($_);
return $DBH->do($query) ? &html_view_messages("User updated.") : &html_view_messages("Error updating user. Reason: $DBI::errstr");
}
}
Quote Reply
Re: [Paul_Wilson] Loop help In reply to
I think you're getting closer! There wasn't any errors this time, it marked 1 message out of more than 1 checked. This is what the debugging returned for the form variables when I checked more than 1 message:

-------------------------------------------
ID : 18
db : db
mark_unread : Unread
move_inbox_to : Select

When checking just 1, it reports the same ID number (changes if I check a different message though). 18 is the largest ID number of the messages in the inbox right now, so checking more than 1 always seems to just mark the highest number only.
Shannon
Quote Reply
Re: [Paul_Wilson] Loop help In reply to
Paul,
Could you keep away yourself from those threads, where I do actively help a user?

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [shann123] Loop help In reply to
Can you find the code that parses the form to see how %in is populated for parameters with more than one value - that might shed some light on the situation. I don't have access to the files myself.

By the way, to improve the performance of the code it may be better to use the following query, which only needs to be run once, rather than executing multiple updates....

UPDATE $db_comments SET messageread = '0' WHERE ID IN(1,2,3,4,5)

To do that you'd use:
Code:
my $query = qq|UPDATE $db_comments SET messageread = '0' WHERE ID IN(| . join(', ', map $DBH->quote($_), @list) . q|)|;
Quote Reply
Re: [Paul_Wilson] Loop help In reply to
I think this is what you were looking for:

sub parse_form {
# --------------------------------------------------------
my (%in);
my ($buffer, $pair, $name, $value);

PAIR: foreach $name ($query->param()) {
$value = $query->param("$name");
$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ s/<!--(.|\n)*-->//g;
if ($value eq "---") { next PAIR; }
(exists $in{$name}) ?
($in{$name} .= "~~$value") :
($in{$name} = $value);
}
return %in;
}

Last edited by:

shann123: Feb 28, 2005, 5:01 PM
Quote Reply
Re: [shann123] Loop help In reply to
Bingo.

Try this:

Code:
sub mark_unread {
my @list = split '~~', $in{ID};
my $query = qq!UPDATE $db_comments SET messageread = '0' WHERE ID IN(! . join(', ', map $DBH->quote($_), @list) . qq!)!;
return $DBH->do($query) ?
&html_view_messages("User updated.") :
&html_view_messages("Error updating user. Reason: $DBI::errstr");
}
Quote Reply
Re: [Paul_Wilson] Loop help In reply to
That didn't seem to make a difference either. Unsure
I don't know if it'd help or not, but I can PM ya that view_messages sub if you need me to.
Quote Reply
Re: [shann123] Loop help In reply to
Can you select multiple messages and then print out the contents of $in{ID} to see what it holds.

Also print out the SQL query to make sure it looks right.

I think it is something to do with the fact that @list isn't getting all the ID's
Quote Reply
Re: [Paul_Wilson] Loop help In reply to
Here's what the form variables are returning using the built in debugging:

Form Variables
-------------------------------------------
ID : 20
db : db
mark_unread : Unread

and the query:

my $query = qq!
UPDATE $db_comments SET messageread = '0'
WHERE ID IN(! . join(', ', map $DBH->quote($_), @list) . qq!)!;
return $DBH->do($query) ?
Quote Reply
Re: [shann123] Loop help In reply to
If more than one ID is selected it should be showing something like:

ID: 20~~15~~18

..according to the form parsing code you pasted above. Send me a pm if you want.

It's after 1am here so I don't have long but I'll try my best to solve it before I go to bed :)