Lunarpages Web Hosting Forum

Scripting Languages Hosting Help => C++ / PERL / CGI Support => Topic started by: MagicMike304 on March 07, 2004, 02:41:14 PM

Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 02:41:14 PM
I am new to Perl/CGI and trying to write my first script.  I am using ssi to run this cgi script.  I want the script to display a random item from the DB. This is just my first step.  After I get this to work, then I will wrok on how and where I want it displayed.  Basicly in this script I have two problems, 1. connecting to my DB and 2. have it random select the item and display it.  Like I said, this is my first time writing.

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);

use DBI;

$dbhost = "localhost";
$dbname = "erotic6_items";
$dbtable = "Item_Rotate";
$dbuser = "name";   #This will be sub for my user name
$dbpass = "password";   #This will be sub for my password

$dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",$dbuser,$dbpass) or dienice("Can't connect: ",$dbh::errstr);

print "Content-type:text/html\n\n";
srand(time() ^ ($$ + ($$ << 15)) );

$sth = $dbh->prepare("select * from items") or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;
while (($SKU,$Name,$Price,$Image,$Link) = $sth->fetchrow_array) {
print "$SKU - $Name - $Price - $Image - $Link\n";
}

$dbh->disconnect;

sub dienice {
my($msg) = @_;
print "<h2>Error</h2>\n";
print $msg;
exit;
}


I know that I could find many free scripts, but I would like to write this to better learn it.  If anyone has any suggestions, it would be appreciated.


Thanks,
Mike
Title: Re: Random Item script
Post by: Ripta on March 07, 2004, 06:27:27 PM
Quote from: MagicMike304
I am new to Perl/CGI and trying to write my first script.  I am using ssi to run this cgi script.  I want the script to display a random item from the DB. This is just my first step.  After I get this to work, then I will wrok on how and where I want it displayed.  Basicly in this script I have two problems, 1. connecting to my DB and 2. have it random select the item and display it.  Like I said, this is my first time writing.
Code: [Select]

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);

use DBI;

$dbhost = "localhost";
$dbname = "erotic6_items";
$dbtable = "Item_Rotate";
$dbuser = "name";   #This will be sub for my user name
$dbpass = "password";   #This will be sub for my password

$dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",$dbuser,$dbpass) or dienice("Can't connect: ",$dbh::errstr);

print "Content-type:text/html\n\n";
srand(time() ^ ($$ + ($$ << 15)) );

$sth = $dbh->prepare("select * from items") or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;
while (($SKU,$Name,$Price,$Image,$Link) = $sth->fetchrow_array) {
print "$SKU - $Name - $Price - $Image - $Link\n";
}

$dbh->disconnect;

sub dienice {
my($msg) = @_;
print "<h2>Error</h2>\n";
print $msg;
exit;
}


I know that I could find many free scripts, but I would like to write this to better learn it.  If anyone has any suggestions, it would be appreciated.


Thanks,
Mike


Hi Mike :)

Are you getting errors when connecting to the database? I think the script is already well written. And maybe add:

Code: [Select]

$sth->finish();


before

Code: [Select]

$dbh->disconnect();


:thumb:
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 06:32:38 PM
Hello and thanks for responding. I was getting an error that said "Can't Connect" , but I searched the forums here and made a few changes to what the post said.  Now, I just get a blank page.  The page I am testing this on is blank to begin with, but it should have printed something that it fetched from the DB.  Sense I don't get a "Can't Connect", I am assuming that it is connecting.  So, if it is connecting, then now my problem is, it isn't fetching any data or printing it.

Thanks,
Mike
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 06:39:17 PM
I added

$sth->finish();

But still the same, blank page.

I am not seeing any errors in my error log either.

Thanks,
Mike
Title: Random Item script
Post by: Ripta on March 07, 2004, 06:44:09 PM
Quote from: MagicMike304
Hello and thanks for responding. I was getting an error that said "Can't Connect" , but I searched the forums here and made a few changes to what the post said.  Now, I just get a blank page.  The page I am testing this on is blank to begin with, but it should have printed something that it fetched from the DB.  Sense I don't get a "Can't Connect", I am assuming that it is connecting.  So, if it is connecting, then now my problem is, it isn't fetching any data or printing it.

Thanks,
Mike


Just wondering, what is the SSI tag you're using? Is it something like:

Code: [Select]

<!--#include virtual="/cgi-bin/your-perl-script.pl"-->


But I guess since there was an output before this, it should work. Maybe if I ponder more...
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 06:44:16 PM
I'm sorry, I did get a error in my error log

malformed header from script. Bad header=<h2>Error</h2>:
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 06:46:23 PM
I am using

<!--#exec cgi="/cgi-bin/mrt.cgi"-->

in a .shtml page.

Thanks,
Mike
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 07:08:20 PM
I tried changing my SSI to what you posted above and I get this

[an error occurred while processing this directive]

I was thinking the "include" element would just copy what I had in the file.  That is why I was using the "exec" element.  I thought it would "exec" the script.

Thanks for your help,
Mike
Title: Random Item script
Post by: Ripta on March 07, 2004, 07:12:45 PM
Quote from: MagicMike304
I tried changing my SSI to what you posted above and I get this

[an error occurred while processing this directive]

I was thinking the "include" element would just copy what I had in the file.  That is why I was using the "exec" element.  I thought it would "exec" the script.

Thanks for your help,
Mike


:thumb: So did it finally work, though?
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 07:15:29 PM
I changed the ssi to what you said and I get this printed on the page:

[an error occurred while processing this directive]

and this in my error log:

unable to include "/cgi-bin/mrt.cgi" in parsed file

I can say, at least it did something.  That's better then a blank page, but still no data.

Thanks,
Mike
Title: Random Item script
Post by: Ripta on March 07, 2004, 07:16:52 PM
LOL.
Hmm.. that is weird.
Title: Random Item script
Post by: Ed on March 07, 2004, 08:43:28 PM
Try using the exec command instead of include virtual.

- Ed
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 08:47:01 PM
Using the exec command just gives me a blank page.  I'm thinking I have the "print" wrong and that's why nothing is showing.

Thanks,
Mike
Title: Random Item script
Post by: Ed on March 07, 2004, 08:56:59 PM
You dont get any errors though?

Does the script output fine if you run it on its own?

- Ed
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 09:06:20 PM
Quote

Does the script output fine if you run it on its own?


How would I do this?  I am using a ssi to insert the output on a shtml page.

The page is blank and there is a error in my error log that says:

Quote

malformed header from script. Bad header=<h2>Error</h2>:


That's it.  Not sure why it isn't printing on the page.  If I view the page and then view the source, there is nothing in the source either.

Thanks,
Mike
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 09:10:02 PM
Quote

Does the script output fine if you run it on its own?


LoL, silly me.  I know what you mean.  If I called the script in a url in my browser.  Yes, I just did it and it says:

Quote

The server encountered an internal error or misconfiguration and was unable to complete your request.


But that is becuase I don't have this script creating a html page.

Thanks,
Mike
Title: Random Item script
Post by: Ed on March 07, 2004, 09:15:59 PM
Where is the script located on your server? Try running it from your browser. The output that it shows there will be the same as inserted on the page..
Title: Random Item script
Post by: Ed on March 07, 2004, 09:33:13 PM
the 500 error means that permissions aren't set write or there is an error in the code.

Check your error log and try chmod'ing to 755

- Ed
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 09:36:03 PM
How can I do a check to see if it is connecting? The reason I ask is, just to test something, I put in a fake username and pasword and I still ended up with a blank page.  Sense I used a wrong user and pass, it should have errored about not connecting.

Thanks,
Mike
Title: Random Item script
Post by: Ed on March 07, 2004, 09:43:49 PM
You could put in print statements throughout the code:

print "About to connect\n";

connect code

print "Connected as user $user\n";

etc..

- Ed
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 09:49:33 PM
Thanks for the info.  I put a before connect, after connect, and disconect.  The page shows all three but doesn't show any thing else.  So I'm guessing that the problem lies with reading the database and printing the results of the database.

Thanks again.
Mike
Title: Random Item script
Post by: Ed on March 07, 2004, 10:01:47 PM
Make sure that your cases are all right (case sensitive).. and that there is actual data to read! :-)

Good luck with it!
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 10:04:25 PM
Ok I got something to work.  Now it prints the whole contents of the database.  What I did to make it work was, change "select" and "from" to "SELECT" and "FROM". So now I am working on trying to get it to select one row at random.  Here is my script so far:

Quote

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);

use DBI;

print "Content-type:text/html\n\n";
print "Before connection.\n";

$dbhost = "localhost";
$dbname = "erotic6_items";
$dbtable = "Item_Rotate";
$dbuser = "name";
$dbpass = "pass";

$dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",$dbuser,$dbpass) or dienice("Can't connect: ",$dbh::errstr);
print "Now connected.\n";

srand(time() ^ ($$ + ($$ << 15)) );

$sth = $dbh->prepare("SELECT * FROM $dbtable") or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;
while (($SKU,$Name,$Price,$Image,$Link) = $sth->fetchrow_array) {
     print "$SKU - $Name - $Price - $Image - $Link\n";
}

$dbh->disconnect;
print "Disconnected from database.\n";

sub dienice {
my($msg) = @_;
print "<h2>Error</h2>\n";
print $msg;
exit;
}


Thanks for all the help.  If you know anything about randomness. Please let me know.

Thanks again for all your help and time,
Mike
Title: Random Item script
Post by: Ed on March 07, 2004, 10:08:12 PM
search on google for some tutorials for mySQL in perl. There are lots of awesome tutorials out there! :-)

- Ed
Title: Random Item script
Post by: Ripta on March 07, 2004, 10:30:06 PM
Quote from: MagicMike304
Ok I got something to work.  Now it prints the whole contents of the database.  What I did to make it work was, change "select" and "from" to "SELECT" and "FROM". So now I am working on trying to get it to select one row at random.  Here is my script so far:

Quote

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);

use DBI;

print "Content-type:text/html\n\n";
print "Before connection.\n";

$dbhost = "localhost";
$dbname = "erotic6_items";
$dbtable = "Item_Rotate";
$dbuser = "name";
$dbpass = "pass";

$dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",$dbuser,$dbpass) or dienice("Can't connect: ",$dbh::errstr);
print "Now connected.\n";

srand(time() ^ ($$ + ($$ << 15)) );

$sth = $dbh->prepare("SELECT * FROM $dbtable") or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;
while (($SKU,$Name,$Price,$Image,$Link) = $sth->fetchrow_array) {
     print "$SKU - $Name - $Price - $Image - $Link\n";
}

$dbh->disconnect;
print "Disconnected from database.\n";

sub dienice {
my($msg) = @_;
print "<h2>Error</h2>\n";
print $msg;
exit;
}


Thanks for all the help.  If you know anything about randomness. Please let me know.

Thanks again for all your help and time,
Mike


The uppercase "SELECT" and lowercase "select" is treated equally in SQL. I believe the no output was getting "FROM items" and the one working says "FROM $dbtable" where:

Code: [Select]

$dbtable = "Item_Rotate";


The code should still work with upper- or lowercase letters :thumb:
Title: Random Item script
Post by: MagicMike304 on March 07, 2004, 10:36:30 PM
Thanks Ripta for pointing that out.  That is the item I also changed.  This is my first time writing a script, and as I see, I have my many steps to take.  It would have been easier to use a flat file DB, but I was wanting to us mySQL.  I have learned alot just today with this script.  I am now going to work on randomness and then I will try ti figure out how to print the out in the format I want.  Thanks for all the help.

Thanks again,
Mike
Title: Random Item script
Post by: Ripta on March 07, 2004, 10:42:56 PM
For randomness:

Code: [Select]

my $index = int(rand($sth->rows));
my $row;
if ($index) {
  for (my $counter = 0; $counter < $index; $counter++) {
    $row = $sth->fetchrow_hashref;
  }
}


After that code, if all goes well,

Code: [Select]

$row->{SKU}    # will contain the SKU
$row->{Image} # will contain the image

# etc


Of course, the things between { and } are case sensitive. (i.e. if your MySQL database has the column SKU, then you should put SKU. If it has the column sku, put down sku.)

From where I stand, the code *should* work... :D
Title: Random Item script
Post by: Ripta on March 07, 2004, 10:44:20 PM
Quote from: MagicMike304
Thanks Ripta for pointing that out.  That is the item I also changed.  This is my first time writing a script, and as I see, I have my many steps to take.  It would have been easier to use a flat file DB, but I was wanting to us mySQL.  I have learned alot just today with this script.  I am now going to work on randomness and then I will try ti figure out how to print the out in the format I want.  Thanks for all the help.

Thanks again,
Mike


Well, MySQL is a step over flat files, but it's really useful in the long run. :thumb: Good luck!
Title: Random Item script
Post by: Ed on March 07, 2004, 10:47:44 PM
Thats pretty admirable goign straight to SQL - but you wont turn back! :-)

I'm off for the night but Ripta (brand new staff) is doing a great job already :-) So I'll just leave it up to him!

- Ed
Title: Random Item script
Post by: MagicMike304 on March 08, 2004, 07:42:15 AM
Ripta,  Thanks for the information on randomness.  I have been searching and unable to find information on it.  In my book, this is what they tell me:

srand() ^ ($$ + ($$ << 15)) );

$rand1 = rand(100);

and in the sample script for a rand. image they use:

$img = $imgs[int(rand(@imgs))];

I will try the peices of code to posted.  Thanks very much.  I was wondering about using the hashref. Thanks.  Today is my birthday so I may not get to edit my script until later on.  Thanks so much.  You have been a big help.

Thanks again,
Mike
Title: Random Item script
Post by: Ed on March 08, 2004, 07:45:41 AM
Happy Birthday Mike!  :yey:
Title: Random Item script
Post by: MagicMike304 on March 08, 2004, 03:02:05 PM
Thanks Kata, I'm having a great day, so far.

Ripta,  here is what I have with adding what you posted:

Quote

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);

use DBI;

print "Content-type:text/html\n\n";
print "Before connection.\n";

$dbhost = "localhost";
$dbname = "erotic6_items";
$dbtable = "Item_Rotate";
$dbuser = "name";
$dbpass = "pass";

$dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",$dbuser,$dbpass) or dienice("Can't connect: ",$dbh::errstr);
print "Now connected.\n";

$sth = $dbh->prepare("SELECT * FROM $dbtable") or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;
my $index = int(rand($sth->rows));
my $row;
if ($index) {
   for (my $counter = 0; $counter < $index; $counter++) {
       $row = $sth->fetchrow_hashref;
   }
}

$row->{SKU}
$row->{Name}
$row->{Price}
$row->{Image}
$row->{Link}

$dbh->disconnect;
print "Disconnected from database.\n";

sub dienice {
my($msg) = @_;
print "<h2>Error</h2>\n";
print $msg;
exit;
}



this doesn't seem to print anything. I get a error "syntax error at mrt.cgi line 30, near "$row" "

I have also tried differnt ways of printing such as:

print "$row{SKU}\n";

and

print "$SKU - $Name - $Price - $Image - $Link\n";

but only get the "before connect,connect, disconect" but nothing else.  Did I add what you posted wrong?  I don't mean to keep bothering you, but if you have some time, please let me know.  Thanks for all your help.

Thanks again,
Mike
Title: Random Item script
Post by: Ed on March 08, 2004, 03:13:01 PM
$row->{SKU}
$row->{Name}
$row->{Price}
$row->{Image}
$row->{Link}

I've never used perl to connect to SQL so I"m guessing here. But what if you were to assign one of those to a var:

$test = $row->{SKU};
(Each one of those lines should have a ; at the end should it not?

then print $test;

Also, if that doesn't get you anything.. manually enter data into the DB and hard code the row # it should access (knowing already that there is content there).

Also, try printing out the variable for the row number after you establish it so you can manually see if there is even any data in there!

- Ed
Title: Random Item script
Post by: Ripta on March 08, 2004, 03:16:24 PM
Hey, Happy B'day! :D

About the script, you'll need to print the results out. So, all you need to do is change:

Code: [Select]

$row->{SKU}
$row->{Name}
$row->{Price}
$row->{Image}
$row->{Link}


into something like:

Code: [Select]

print $row->{SKU};
print $row->{Name};
print $row->{Price};
print $row->{Image};
print $row->{Link};


:thumb:
Title: Random Item script
Post by: Ripta on March 08, 2004, 03:22:21 PM
Also,

Code: [Select]

print "$row{SKU}\n";


did not work, because $row{SKU} takes an element a hash instead of a hashref (hash reference). The -> (which looks like an arrow), un-references (the proper term is "dereferencing")  the hash.

Alternatively, if you want to use:

Code: [Select]

print "$SKU - $Name - $Price - $Image - $Link\n";


instead of the nasty looking $row->{SKU} and stuff, you could do so by changing:

Code: [Select]

$row = $sth->fetchrow_hashref;

into something like:

Code: [Select]

($SKU, $Name, $Price, $Image, $Link) = $sth->fetchrow_array;


Hope it helps! :thumb:
Title: Random Item script
Post by: MagicMike304 on March 08, 2004, 03:23:32 PM
Quote

print $row->{SKU};
print $row->{Name};
print $row->{Price};
print $row->{Image};
print $row->{Link};


That's it.  :yey:

Thanks so much.  I last question.  The Image is a link to the image.  To show the image I would use:

print "<img src=\"{Image}\">";

Thanks,
Mike
Title: Random Item script
Post by: MagicMike304 on March 08, 2004, 03:25:57 PM
Ok so by chnaging it into a

$sth->fetchrow_array;

I could show the image like this:

print "<img src=\"$Image\">";

Thanks,
Mike
Title: Random Item script
Post by: MagicMike304 on March 08, 2004, 03:30:44 PM
One more thing.  I am asuming that I can change the way it looks, prints out, by using html like

print <<EndOfHTML;

and use the html to get it to look the way I want.

Correct?

Thanks,
Mike
Title: Random Item script
Post by: Ripta on March 08, 2004, 03:41:14 PM
Quote from: MagicMike304
One more thing.  I am asuming that I can change the way it looks, prints out, by using html like

print <<EndOfHTML;

and use the html to get it to look the way I want.

Correct?

Thanks,
Mike


Yep! :D Instead of doing five prints:

Code: [Select]

print <<EndOfHTML;
$row->{SKU}
$row->{Name}
$row->{Price}
$row->{Image}
$row->{Link}
EndOfHTML


will do the same. And you can use HTML in it. Just remember that both EndOfHTML need to be the same case and that the last EndOfHTML needs to start at the first column. That's where I usually make a mistake. :)
Title: Random Item script
Post by: MagicMike304 on March 08, 2004, 03:45:01 PM
Ripta, thanks so much for all the help.  I really learned a lot from this.  That is the main reason I wanted to write the script myself instead of getting one on hotscripts or somewhere.  Thanks again for all the help.

Thanks,
Mike :thumb:
Title: Random Item script
Post by: Ripta on March 08, 2004, 03:50:01 PM
No problem, Mike. That's what I'm here for :D. Enjoy your birthday :).
Title: Random Item script
Post by: Ed on March 08, 2004, 05:15:22 PM
Congrats on getting it working! :-) Thats the best way to learn everything! :yey:
Title: Random Item script
Post by: Ripta on March 08, 2004, 10:16:01 PM
I found an alternate solution that is faster on large number of records, using the MySQL function RAND(). To use this on the script above, simply replace:

Code: [Select]

$sth = $dbh->prepare("SELECT * FROM $dbtable") or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;
my $index = int(rand($sth->rows));
my $row;
if ($index) {
for (my $counter = 0; $counter < $index; $counter++) {
$row = $sth->fetchrow_hashref;
}
}


with:

Code: [Select]

$sth = $dbh->prepare("SELECT * FROM $dbtable ORDER BY RAND() LIMIT 1") or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;
my $row = $sth->fetchrow_hashref;


It's faster and shorter. However, there has been reports that this does not work on if the servers run Windows. But since we run Linux all the way, there shouldn't be any problem :D. I even tested it on LP's eros server.

Of course, you can still use $sth->fetchrow_array instead of $sth->fetchrow_hashref and everything should work out alright.
Title: Random Item script
Post by: MagicMike304 on March 09, 2004, 04:01:02 AM
Thanks Ripta.  I will try this out.  It seems that this will work correctly with ot returning a "0" becuase the table is being sorted to a random "1".  I have been using the fethcrow_array in my script.  Just becuase I can manipulate the print out easier than using hashes.  I question about the print out.  In the HTML part, what is the html code for making the print out from:

Quote

aaaaaa

aaaaaa

aaaaaa


to this

Quote

aaaaaa
aaaaaa
aaaaaa


I can't seem to get rid of the spaces.  I tried using the <p style="margin-top: 0; margin-bottom: 0"> but it didn't work.  What is the correct way to do this?  Here is what I have.

Quote

print <<EndOfHTML;
<p style="margin-top: 0; margin-bottom: 0"><center><a href="$Link"><img border="0" src="$Image"></a></p>
<p style="margin-top: 0; margin-bottom: 0"><center><font size="2"><a style="text-decoration: none" href="$Link">$Name</font></a></p>
<p style="margin-top: 0; margin-bottom: 0"><center><font size="2">$SKU  -  \$$Price</font></p>
EndOfHTML



Thanks again for all your help.
Mike
Title: Random Item script
Post by: Priest on March 09, 2004, 04:37:39 AM
You can take out the multiple <p> tags and just use one at the beginning, and then use <BR> to represent a new line.

Code: [Select]
print <<EndOfHTML;
<p style="margin-top: 0; margin-bottom: 0">
     <center><a href="$Link"><img border="0" src="$Image"></a><BR>
     <center><font size="2"><a style="text-decoration: none" href="$Link">$Name</font></a><BR>
     <font size="2">$SKU - \$$Price</font><BR>
</p>
EndOfHTML
Title: Random Item script
Post by: MagicMike304 on March 09, 2004, 05:43:06 AM
Thanks StevenP, that fixed the problem.  :yey:

Thanks,
Mike
Title: Random Item script
Post by: Ripta on March 09, 2004, 07:52:48 AM
Quote from: StevenP
You can take out the multiple <p> tags and just use one at the beginning, and then use <BR> to represent a new line.

Code: [Select]
print <<EndOfHTML;
<p style="margin-top: 0; margin-bottom: 0">
     <center><a href="$Link"><img border="0" src="$Image"></a><BR>
     <center><font size="2"><a style="text-decoration: none" href="$Link">$Name</font></a><BR>
     <font size="2">$SKU - \$$Price</font><BR>
</p>
EndOfHTML


Darn it. He beat me to it :thumb:.