CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Perl MySQL help with code.

 

First page Previous page 1 2 3 4 Next page Last page  View All


FishMonger
Veteran / Moderator

Feb 27, 2013, 12:42 PM

Post #51 of 76 (4614 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Your database doesn't have a 'subscr_id' field, which is why it's not returning the data you expect. In fact, if you used the db code that I provided, it would have told you about that problem.

Also, having 2 fields with the same name but differing in case and datatype is a REALLY BAD idea. You should alter your table and rename the 'id' field to 'subscr_id';


(This post was edited by FishMonger on Feb 27, 2013, 12:46 PM)


brosskgm
User

Feb 27, 2013, 1:03 PM

Post #52 of 76 (4600 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I created a new db and changed that id field to subscr_id

Same undeclared error in the perl code.


brosskgm
User

Feb 27, 2013, 1:06 PM

Post #53 of 76 (4598 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

What one? I put most of what you posted

I was having several things thrown in.


brosskgm
User

Feb 27, 2013, 1:09 PM

Post #54 of 76 (4595 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

New dump, same undeclared error

Here is the dump

CREATE TABLE `members` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`subscr_id` varchar(255) NOT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `members`
--

INSERT INTO `members` VALUES(12, 'admin@domain.com', 'I-YS25UHRN3N9D', 'NULL');


FishMonger
Veteran / Moderator

Feb 27, 2013, 1:22 PM

Post #55 of 76 (4588 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
What one? I put most of what you posted

I was having several things thrown in.


This is the db code I was referring to, which was posted at 8:23am PST.


Code
# db related vars  
my $host = "remote_host";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "pw";

my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $pw,
{RaiseError => 1})
or die "Connection Error: $DBI::errstr\n";

my $sql = "SELECT subscr_id, username FROM $tablename WHERE subscr_id = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($id);
my $subscriber = $sth->fetchrow_hashref;

$sth->finish();


If you run your select statement in the mysql cli, it will generate a warning. Since your script is not redirecting warnings to the browser, that message was never displayed with the rest of the output.

The adjustments that I suggested, in particular the { RaiseError => 1} portion would have raised that warning to a fatal error which would then have been displayed in the browser and added to the web server error log.


brosskgm
User

Feb 27, 2013, 1:28 PM

Post #56 of 76 (4584 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

But with out the finish since it errors?


FishMonger
Veteran / Moderator

Feb 27, 2013, 1:29 PM

Post #57 of 76 (4583 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
New dump, same undeclared error

Here is the dump

CREATE TABLE `members` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`subscr_id` varchar(255) NOT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `members`
--

INSERT INTO `members` VALUES(12, 'admin@domain.com', 'I-YS25UHRN3N9D', 'NULL');


If you compare the 'subscr_id' field here with the id passed in from the php script, you'll see that they don;t match.

id = 1-YS25UHRN3N9D
subscr = I-YS25UHRN3N9D

One of them begins with the number 1 and the other begins with the uppercase I letter.


FishMonger
Veteran / Moderator

Feb 27, 2013, 1:31 PM

Post #58 of 76 (4579 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
But with out the finish since it errors?


Your finish statement errors out because it's wrong. Mine is correct and doesn't error.


brosskgm
User

Feb 27, 2013, 1:32 PM

Post #59 of 76 (4576 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Dam, that's odd, I even tagged and copied that sucker too. I'll make the change.


brosskgm
User

Feb 27, 2013, 1:39 PM

Post #60 of 76 (4574 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Wow!!!!

Ok, that part is working!!! Ya, but when it's the wrong $id it give the undeclared error for $subscr_id at
if($id eq $subscr_id)


FishMonger
Veteran / Moderator

Feb 27, 2013, 1:46 PM

Post #61 of 76 (4570 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

That's part of the reason I said that you shouldn't be comparing those 2 vars. Just check $username.


Code
if ( $username )


An undefined var checked in scalar context like this returns true or false.

If you want it more verbose, you could write it as:

Code
if ( defined $username )


EDIT:
The db code I reference a few posts back was not the one I actually wanted. I just scrolled to the wrong post when searching for the version I meant to refer to..


(This post was edited by FishMonger on Feb 27, 2013, 1:55 PM)


brosskgm
User

Feb 27, 2013, 1:54 PM

Post #62 of 76 (4563 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I tagged and copied yours here above it still tosses the finish error and when the $id is not matching it still tosses the following errors - first couple lines copied.


Code
[Wed Feb 27 13:44:21 2013] [error] [client 192.168.4.4] [Wed Feb 27 13:44:21 2013] log_it.cgi: Can't locate object method "finish" via package "DBI::db" at /var/www/cgi-bin/log_it.cgi line 51., referer: http://192.168.4.4/test.php 
[Wed Feb 27 13:46:37 2013] [error] [client 192.168.4.4] [Wed Feb 27 13:46:37 2013] log_it.cgi: Global symbol "$subscr_id" requires explicit package name at /var/www/cgi-bin/log_it.cgi line 64., referer: http://192.168.4.4/test.php



FishMonger
Veteran / Moderator

Feb 27, 2013, 1:57 PM

Post #63 of 76 (4560 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Are you using:

Code
$dbh->finish;


or

Code
$sth->finish;



Please post the lines that those error messages refer to.


(This post was edited by FishMonger on Feb 27, 2013, 1:59 PM)


brosskgm
User

Feb 27, 2013, 2:00 PM

Post #64 of 76 (4556 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

$sth->finish(); as you have posted above. I tagged and copied all of it and pasted it in.


brosskgm
User

Feb 27, 2013, 2:05 PM

Post #65 of 76 (4553 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Found it, I still had the one that didn't work about it.

The comment out was on the line above it.


brosskgm
User

Feb 27, 2013, 2:08 PM

Post #66 of 76 (4552 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

This if ( defined $username )
replaces if($id eq $subscr_id) ?

When I do it tosses all the way down on all of them. Even that the $id is wrong.

Code
[Wed Feb 27 14:09:12 2013] [error] [client 192.168.4.4] [Wed Feb 27 14:09:12 2013] log_it.cgi: Global symbol "$username" requires explicit package name at /var/www/cgi-bin/log_it.cgi line 65., referer: http://192.168.4.4/test.php 
[Wed Feb 27 14:09:12 2013] [error] [client 192.168.4.4] [Wed Feb 27 14:09:12 2013] log_it.cgi: Global symbol "$subscr_id" requires explicit package name at /var/www/cgi-bin/log_it.cgi line 70., referer: http://192.168.4.4/test.php



(This post was edited by brosskgm on Feb 27, 2013, 2:13 PM)


FishMonger
Veteran / Moderator

Feb 27, 2013, 2:10 PM

Post #67 of 76 (4550 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
This if ( defined $username )
replaces if($id eq $subscr_id) ?


Yes, but my first preference would be:

Code
if ( $username )



brosskgm
User

Feb 27, 2013, 2:14 PM

Post #68 of 76 (4545 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

same error about the global symbol all the way down.


FishMonger
Veteran / Moderator

Feb 27, 2013, 2:20 PM

Post #69 of 76 (4540 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

You need to post your code.


brosskgm
User

Feb 27, 2013, 2:36 PM

Post #70 of 76 (4531 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

 

Code
#!/usr/bin/perl 
use warnings FATAL => 'all';
use strict;
use diagnostics;
use CGI qw/param/;
use POSIX qw/strftime/;
use DBI;
use CGI::Carp qw(fatalsToBrowser); # remove this when the script is complete and put into production
use Data::Dumper;

print "ContentType: text/html\n\n";

my $host = "remote_host";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "password";

# Obtain items from web page.
my $id = param("id");
my $ip = param("ip");
my $pg = param("pg");
#
# End web page items
#
my $phone;
my $iptrace = "_iptrace.txt";
my $invalid_iptrace = "/tmp/invalid_id_iptrace.txt";
my $dir = "/tmp/";
my $ext = ".txt";

# message
# 1 = email
# 2 = phone
# 3 = both
my $message = param("pg");

my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $pw,
{RaiseError => 1})
or die "Connection Error: $DBI::errstr\n";
my $sql = "SELECT subscr_id, username FROM $tablename WHERE subscr_id = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($id);
my $subscriber = $sth->fetchrow_hashref;
$sth->finish();

print Dumper $id, $subscr_id;

# if ($id eq $subscr_id)
if ( $username )
{
#if we have a match open the file to write.
open(FILE, '>'."$dir$subscr_id$ext");

# lets put the time in the file.
print FILE strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );

# put the IP address and web page visited.
print FILE " -- IP Address: $ENV{REMOTE_ADDR}\n\t Accessed your page: $ENV{HTTP_REFERER}\n------\n";
print FILE " -- Here is the information we found:\n------\n";

#close the file. Finished with the first part
close(FILE);

# Run the IP2Location script.
system("./iptrace.sh $ENV{REMOTE_ADDR} json city>> $dir$subscr_id$ext");

# Lets send an email and or text with all the information.
# Send email

if($message == 1)
{
system("mail -s 'Web Page visited' $username \< $dir$subscr_id$ext");
# print "message 1"; <-- example
}
elsif($message == 2)
{
system("mail -s 'Web Page visited' $phone \< $dir$subscr_id$ext");
# do something else
}
elsif($message == 3)
{
system("mail -s 'Web Page visited' $username \< $dir$subscr_id$ext");
system("mail -s 'Web Page visited' $phone \< $dir$subscr_id$ext");
# do something
}
else
{
;
# No conditions met.
}
print "ContentType: text/html\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
exit();
}
else
{
# "To Do" send a page or popup if the id is not valid.
my $filed = "$invalid_iptrace";
open (FILED, '>'."$filed") or die $!;
print FILED strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
print FILED " -- IP Address: $ip\n\t Access Page: $ENV{HTTP_REFERER} \n From id: $id with :pg as $pg\n------\n";
close(FILED);
system("mail -s 'Page visited Unknown' admin\@domian.com \< $filed");
print "ContentType: text/html\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
exit();
}
exit();
#end of script



FishMonger
Veteran / Moderator

Feb 27, 2013, 2:56 PM

Post #71 of 76 (4524 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

You need to be careful when applying changes; you need to make sure that you adjust all related code that would be affected by the change you made.

If you fetch the data as a hash ref, then you need to update all of the related vars to the hash ref. Or, fetch the data as an array and keep the related vars a is.


change:

Code
my $subscriber = $sth->fetchrow_hashref;


to:

Code
my ($subscr_id, $username) = $sth->fetchrow_array;



brosskgm
User

Feb 27, 2013, 3:26 PM

Post #72 of 76 (4521 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Wow!!, thank you so much!!, I do try to tag/copy/paste as carefully as I can. Especially since I have no idea about much of this.

Is there something I can do for you and lightspd ? This was a great help. I'd been trying to get this working for better than a couple months.

Thanks so much.
Bob


FishMonger
Veteran / Moderator

Feb 28, 2013, 7:14 AM

Post #73 of 76 (4500 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

The best 2 things you can do for those of us that help you is to give your honest appreciation for our time and learn how to do this on your own so that you can repay this effort of ours by helping/teaching others.


brosskgm
User

Mar 4, 2013, 6:34 AM

Post #74 of 76 (4455 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I was trying to improve the code a bit the other day and ran into a slight snag. It is working as I was wanting, but it's creating two errors that I'm having a problem figuring out. I was trying to eliminate the need to tell it to send 1, 2 or 3. I added an extra table to the database to handle the cell sms address after their cell number and also to create a log file.

It seems to be acting like it's processing all the if and elsif but still working as I was hoping.

It still writes all the files and only sends the correct one. (Not a big deal, but it's an error.)

2nd) I'm now getting a mail error in the logs, but it's sending the mail just fine.

Error:

Code
[Mon Mar 04 04:51:52 2013] [error] [client 192.168.4.4] Send options without primary recipient specified., referer: http://192.168.4.4/ 
[Mon Mar 04 04:51:52 2013] [error] [client 192.168.4.4] Usage: mail -eiIUdEFntBDNHRV~ -T FILE -u USER -h hops -r address -s SUBJECT -a FILE -q FILE -f FILE -A ACCOUNT -b USERS -c USERS -S OPTION users, referer: http://192.168.4.4/




Code
    if ( $username ) 
{
#if we have a match open the file to write.
open(FILEB, '>>'."$dirlog$subscr_id$extb"); # log file
# lets put the time in the file.
print FILEB strftime("%A %B %d, %Y - %I:%M %p %Z ", localtime(time) );
# put the IP address and web page visited.
print FILEB " -- IP Address: $ENV{REMOTE_ADDR}\n";
#close the file. Finished with the first part
close(FILEB);
# Run the IP2Location script.
# Lets send an email and or text with all the information.

# Send email and text

if($subscr_provider ne " " && $subscr_email ne " ")
{
# do something
# open email & text files for write
open(FILE, '>'."$dir$subscr_id$ext"); # email file
open(FILEC, '>'."$dir$subscr_id$text"); # text message file
# lets put the time in the file
print FILE strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
print FILEC strftime("%A %B %d, %Y - %I:%M %p %Z ", localtime(time) );
# Put the IP address and web page visited
print FILE " -- IP Address: $ENV{REMOTE_ADDR}\n\t Accessed your page: $ENV{HTTP_REFERER}\n------\n";
print FILE " -- Here is the information we found:\n------\n";
print FILEC " -- IP Address: $ENV{REMOTE_ADDR} Accessed your page\n";
close(FILE);
close(FILEC);
# Run the IP2Location script.
system("./iptrace.sh $ENV{REMOTE_ADDR} json city>> $dir$subscr_id$ext");
# were done send the notices
system("mail -s 'Web Page visited' $subscr_email \< $dir$subscr_id$ext");
system("mail -s 'Web Page visited' $subscr_phone$subscr_provider \< $dir$subscr_id$text");
}
elsif($subscr_provider eq " " && $subscr_email ne " ") # send email only
{
# open email file for write
open(FILE, '>'."$dir$subscr_id$ext"); # email file
# put the time in the file
print FILE strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
#put the ip address and web page visited
print FILE " -- IP Address: $ENV{REMOTE_ADDR}\n\t Accessed your page: $ENV{HTTP_REFERER}\n------\n";
print FILE " -- Here is the information we found:\n------\n";
#close the file
close(FILE);
# Run the IP2Location script.
system("./iptrace.sh $ENV{REMOTE_ADDR} json city>> $dir$subscr_id$ext");
system("mail -s 'Web Page visited' $subscr_email \< $dir$subscr_id$ext");
}
elsif($subscr_provider ne " " && $subscr_email eq " ") # text message only
{
# open text file for write
open(FILEC, '>'."$dir$subscr_id$text"); # text message file
# lets put the time in the file
print FILEC strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
# put the ip address that accessed the web site
print FILEC " -- IP Address: $ENV{REMOTE_ADDR} Accessed your page\n";
# We don't run IP2Location for text. Makes them 5 messages long.
# were done lets send the notice.
system("mail -s 'Web Page visited' $subscr_phone$subscr_provider \< $dir$subscr_id$text");
}



FishMonger
Veteran / Moderator

Mar 4, 2013, 8:11 AM

Post #75 of 76 (4449 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

The error message is telling you that your syntax for the mail command is incomplete. You should read the man page for the mail command to learn how it should be used. I don't use it and don't want to guess on its usage.

It would be better to use one of Perl's email modules instead of doing the system call.
MIME::Lite - http://search.cpan.org/~rjbs/MIME-Lite-3.029/lib/MIME/Lite.pm

It would also be more efficient to not use those external files as a holding place for the email data that you're sending. Instead store that info in a perl var.

First page Previous page 1 2 3 4 Next page Last page  View All
 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives