loading a lot of values into an mysql database and looping

N

Nikos

ok before we go into the step i asked i must somehow load the games
database table with a row coantaining 3 fields. The games name, the
games description and the number of times the games downloaded by the user.

I had the table loaded with repetitive insert into statements but now
the games folder has more than 100+ games so i need a more automatic way
of filling it in.

I have made the following snippet of code but as usual it aint working
as it should so i could use a liitle Monk help/suggestions. After
completeing this part i will go to the one i first asked because now
that i deleted the insert into statements i must firstly load the
database table again Any way here is the code so far:

It would be even better that the description of the game would not be
the same text as the name of the game that why i have wriiten all the
description in one file in the same folder as games separating by one
line but i donw know hot to load them as a 2nd parameter to the 2nd ? in
prepare. Any way here is the code so far:

$db->do( "create table games( name varchar, desc text, counter int"
+);


#=====================================================================
+==========

my $st = $db->prepare( "INSERT INTO games VALUES (?, ?, ?)" );
my @games = glob( "/data/games/*" ) or die $!;

print @games;

while (@games) {
$st->execute( $_, $_, 0 );
}

#=====================================================================
+==========

# hre is the part that load the description game into an array from a
+file containign the descs

open(FILE, "</data/games/descriptions.txt") or die $!;
my @desc = <FILE>;
close(FILE);

@desc = grep { !/^\s*\z/s } @desc;
 
J

John Bokma

Nikos said:
I have made the following snippet of code but as usual it aint working
as it should

You really think people are going to complete your script, set up a
database, and fix it?

Which error do you get? Or what is the behavior you see?
 
N

Nikos

Jim said:
Put your game names and descriptions in a separate file, one game on
each line. Put the name first followed by a tab character followed by
the description, making sure that the description does not contain any
tabs. Read these descriptions into a hash with the following code
(untested):

my %descriptions;
open( my $desc, '<', 'descriptions.txt' ) or
die("Can't open descriptions file: $!");
while(<$desc>) {
chomp;
my( $name, $description ) = split(/\t/);
$descriptions{$name} = $description;
}

Thanks but i was wondering/thinking that there is no need actually to
put except from the descriptions also the name of the games inside the
description file since we can get all the games names from the game
folder they are inside by doing this:

my @games = </data/games/*.rar> or die $!;

What do you think?
 
N

Nikos

Actually now is changed to:

$db->do( "create table games( onoma text, description text, counter
int )" ) or die $!;


#===============================================================================

my %descriptions;
open( my $desc, "</data/games/perigrafes.txt" ) or die $!;

while(<$desc>) {
chomp;
my( $name, $description ) = split(/\t/);
$descriptions{$name} = $description;
}

my $st = $db->prepare( "INSERT INTO games VALUES (?, ?, ?)" );

my @games = </data/games/*.rar> or die $!;

while (@games) {
$st->execute( $_, $descriptions{$_}, 0 );
}

#===============================================================================


And here si the code that tries to displays those database table values
in a esy appelaing way:

my $row;
while ( $row = $st->fetchrow_hashref ) {

print table( {class=>'info'},
Tr(
td( submit( -name=>'game', -value=>$_->{name} )),
td( $_->{description} ),
td( $_->{counter} )
)
)
}

But it aint show anyhting :(
 
T

Tad McClellan

Nikos said:
(e-mail address removed) wrote:


You snipped everything that Xho wrote.

he said that this:


looked like an infinite loop.

while (@games) {
$st->execute( $_, $descriptions{$_}, 0 );
}


And it *still* looks like an infinite loop.

This is precisely why some folks have given up on helping you,
a problem was pointed out to you, but you didn't fix it.

We get the feeling that you are not listening to followups,
so there isn't much point it spending time composing
a followup.

Fix your infinite loop.

my $row;
while ( $row = $st->fetchrow_hashref ) {

print table( {class=>'info'},
Tr(
td( submit( -name=>'game', -value=>$_->{name} )),
td( $_->{description} ),
td( $_->{counter} )
)
)
}

But it aint show anyhting :(


Look at the name of the hashref variable.

Look at the name of the variable that you are using as if it
was a hashref.

Do they look like the same variable?
 
M

Mark Clements

Nikos said:
Actually now is changed to:

$db->do( "create table games( onoma text, description text, counter
int )" ) or die $!;
You still aren't partitioning your problem.
#===============================================================================


my %descriptions;
open( my $desc, "</data/games/perigrafes.txt" ) or die $!;

while(<$desc>) {
chomp;
my( $name, $description ) = split(/\t/);
$descriptions{$name} = $description;
}

my $st = $db->prepare( "INSERT INTO games VALUES (?, ?, ?)" );

my @games = </data/games/*.rar> or die $!;
Does @games contain what you think it contains at this stage? Have you
checked?
while (@games) {
$st->execute( $_, $descriptions{$_}, 0 );
}

This will only ever complete if @games is empty....
How many elements does @games contain? Have you tried Data::Dumper?
Do you check the return value of $st->execute()?


Following this, what is in the database table? Have you used the mysql
console to execute

select * from games;

Until you verify that the data is as you expect, then there is little
point in worrying about the next step....
#===============================================================================



And here si the code that tries to displays those database table values
in a esy appelaing way:

my $row;
while ( $row = $st->fetchrow_hashref ) {

print table( {class=>'info'},
Tr(
td( submit( -name=>'game', -value=>$_->{name} )),
td( $_->{description} ),
td( $_->{counter} )
)
)
}
This is going to give you a separate table for each row.

Mark
 
N

Nikos

Tad said:
while (@games) {
$st->execute( $_, $descriptions{$_}, 0 );
}

for (@games) {
$st->execute( $_, $descriptions{$_}, 0 );
}

but still for soem reason the database table games aint loading.
data::dumper says its empty.
But it should have been loading.
my $row;
while ( $row = $st->fetchrow_hashref ) {

print table( {class=>'info'},
Tr(
td( submit( -name=>'game', -value=>$_->{name} )),
td( $_->{description} ),
td( $_->{counter} )
)
)
}

my $st = $db->prepare( "SELECT * FROM games" );
$st->execute();

while ( $st->fetchrow_hashref ) {

print table( {class=>'info'},
Tr(
td( submit( -name=>'game', -value=>$_->{name} )),
td( $_->{description} ),
td( $_->{counter} )
)
)
}

Yes i removed @row because as i had it it didnt compare
$row and $st->fetchrow_hashref but instead it assigned values to #row,
so i completed remove $row.
 
M

Mark Clements

Nikos said:
for (@games) {
$st->execute( $_, $descriptions{$_}, 0 );
}

but still for soem reason the database table games aint loading.
data::dumper says its empty.
But it should have been loading.
Trying to break this down one painful step at a time.....

Data::Dumper doesn't show you what is in the database table, it shows
you what is in perl data structures. If @games is empty before you start
the for block, then nothing is going to get inserted. Your population of
@games is probably wrong. Check your glob statement (if that is what you
are still using).

ie (am not convinced you are doing this properly so)

warn Dumper @games;

anything there?

Also, try using explicit variable names where you can to avoid confusion:

foreach my $game(@games) {
$st->execute( $game, $descriptions{$game}, 0 );
}

*but* check the return value of execute

foreach my $game(@games) {
unless( $st->execute( $game, $descriptions{$game}, 0 ) ){
warn $db->errstr;
}
}

What does %descriptions contain? What does

warn Dumper \%descriptions;

give you?

Mark
 
N

Nikos

Mark said:
Trying to break this down one painful step at a time.....

Data::Dumper doesn't show you what is in the database table, it shows
you what is in perl data structures. If @games is empty before you start
the for block, then nothing is going to get inserted. Your population of
@games is probably wrong. Check your glob statement (if that is what you
are still using).

ie (am not convinced you are doing this properly so)

warn Dumper @games;

anything there?

Also, try using explicit variable names where you can to avoid confusion:

foreach my $game(@games) {
$st->execute( $game, $descriptions{$game}, 0 );
}

*but* check the return value of execute

foreach my $game(@games) {
unless( $st->execute( $game, $descriptions{$game}, 0 ) ){
warn $db->errstr;
}
}

What does %descriptions contain? What does

warn Dumper \%descriptions;

give you?

Mark

Actually to make very very sure that the file is openign ok and the
values of the file is loading into an array i made the following code:

perigarfes.txt coantians the game name and the games descriptions
seperated by a tab character:

#===============================================================================

open (FILE, "<../data/games/perigrafes.txt") or die "Can't open FILE: $!\n";
while (<FILE>) {
chomp;
my @row = split /\t/;
push @row, 0;
print "NAME: $row[0]\n";
print "DESC: $row[1]\n";
print "COUNT: $row[2]\n\n";
}
close FILE;

#===============================================================================

Will you help me move on now that i ensured this?
 
M

Mark Clements

Nikos said:
Actually to make very very sure that the file is openign ok and the
values of the file is loading into an array i made the following code:

perigarfes.txt coantians the game name and the games descriptions
seperated by a tab character:

#===============================================================================


open (FILE, "<../data/games/perigrafes.txt") or die "Can't open FILE:
$!\n";
while (<FILE>) {
chomp;
my @row = split /\t/;
push @row, 0;
print "NAME: $row[0]\n";
print "DESC: $row[1]\n";
print "COUNT: $row[2]\n\n";
}
close FILE;
great: so you have just populated @row with a load of 0s.

push @row, 0;

I'm pretty sure you don't want to do this. What is @row being used for
anyway? This is its first appearance.

But anyway, what happened to your glob populating @games? You seem to
have forgotten about it.

my @games = </data/games/*.rar> or die $!;

what is in @games?

I repeat (because you don't seem to be listening):

what is in @games?

Is the path "/data/games/*.rar" correct? What happens if you do

ls /data/games/*.rar

from the command-line?
Will you help me move on now that i ensured this?
We can't possibly help you with every single line of your program,
especially if you *don't listen to advice*.

Data::Dumper is your friend, but you seem determined not to use it.

You are casting about blindly: you have no idea how to track down
problems and you're hoping that if you throw enough switches then
magically it will all work.

I hope you are now running with strict and warnings.

Nikos: you are asking us to do far too much work.

Mark
 
N

Nikos

Mark Clements wrote:

$dbh->do( "create table games( onoma text, description text, counter int
)" ) or die $!;

#===============================================================================

my $sth = $dbh->prepare( "INSERT INTO games( name, description, count)
VALUES (?, ?, ?)" );

open (FILE, "<../data/games/perigrafes.txt") or die "Can't open FILE: $!\n";
while (<FILE>) {
chomp;
my @row = split /\t/;
push @row, 0;
next unless scalar @row == 3;
$sth->execute(@row);
}
close FILE;

#===============================================================================

Sorry before i forgot to paste the whole code for you to see why i use
@row which is to fill the games name nad description from the
perigarfex.txt file to the @row array and then in its turn to laod it in
the database but still although the whole make.pl file its correct still
the database table games its empty!!!!!

Iam gonna go nuts wit this thing. :(
 
M

Mark Clements

Nikos said:
Mark Clements wrote:

$dbh->do( "create table games( onoma text, description text, counter int
)" ) or die $!;

again: no, I didn't. If you are going to quote someone, please do it
properly.

#===============================================================================


my $sth = $dbh->prepare( "INSERT INTO games( name, description, count)
VALUES (?, ?, ?)" );

open (FILE, "<../data/games/perigrafes.txt") or die "Can't open FILE:
$!\n";
while (<FILE>) {
chomp;
my @row = split /\t/;
push @row, 0;
next unless scalar @row == 3;
$sth->execute(@row);
}
close FILE;

#===============================================================================


Sorry before i forgot to paste the whole code for you to see why i use
@row which is to fill the games name nad description from the
perigarfex.txt file to the @row array and then in its turn to laod it in
the database but still although the whole make.pl file its correct still
the database table games its empty!!!!!

OK: I misread
my @row = split /\t/;
push @row, 0;

I don't see why you have 3 bind parameters when only two of them vary.

However: you are *still* not checking the return value of execute. You
are determined to make this as difficult for yourself (and for us) as
possible.

I've said you need to do this several times, but, as usual, you aren't
listening. I'm going to try one last time:

*check the return value of $sth->execute*

From my last posting (did you actually read it), something like:

unless( $st->execute( $game, $descriptions{$game}, 0 ) ){
warn $db->errstr;
}

Alternatively, set RaiseError on the db connection.

You aren't going to take *any* of this on board (see n previous
exchanges like this), so I'm going to give up at this point.

Mark
 
N

Nikos

Mark said:
You aren't going to take *any* of this on board (see n previous
exchanges like this), so I'm going to give up at this point.

Please dont quit. i am trying:
here i write it with the use of warns:

$dbh->do( "create table games( onoma text, description text, counter int
)" ) or die $!;

#===============================================================================

my $sth = $dbh->prepare( "INSERT INTO games( name, description, count)
VALUES (?, ?, ?)" );

open (FILE, "<../data/games/perigrafes.txt") or die "Can't open FILE: $!\n";
while (<FILE>) {
chomp;
my @row = split /\t/;

my $num = scalar @row;
if ($num == 2) {
push @row, 0;
unless ( $sth->execute(@row) ) warn $dbh->errstr;
if ($dbh->errstr) {
warn "LINE $.: INSERT '$row[0]': $dbh->errstr\n";
}
} else {
warn "Error in line $.: number of fields $num\n";
}
close FILE;

#===============================================================================

Here it the output.
Now it wont even run!!!
Damn it what am i doing wrong?



<h1>Software error:</h1>
<pre>syntax error at
C:\DOCUME~1\beatnik\LOCALS~1\Temp\dirF40.tmp\make.pl line 3
8, near &quot;) warn&quot;
Missing right curly or square bracket at
C:\DOCUME~1\beatnik\LOCALS~1\Temp\dirF4
0.tmp\make.pl line 57, at end of line
syntax error at C:\DOCUME~1\beatnik\LOCALS~1\Temp\dirF40.tmp\make.pl
line 57, at
EOF
Execution of C:\DOCUME~1\beatnik\LOCALS~1\Temp\dirF40.tmp\make.pl
aborted due to
compilation errors.
</pre>
<p>
For help, please send mail to this site's webmaster, giving this error
message
and the time and date of the error.

</p>
[Tue Apr 26 18:56:39 2005] make.pl: syntax error at
C:\DOCUME~1\beatnik\LOCALS~1
\Temp\dirF40.tmp\make.pl line 38, near ") warn"
[Tue Apr 26 18:56:39 2005] make.pl: Missing right curly or square
bracket at C:\
DOCUME~1\beatnik\LOCALS~1\Temp\dirF40.tmp\make.pl line 57, at end of line
[Tue Apr 26 18:56:39 2005] make.pl: syntax error at
C:\DOCUME~1\beatnik\LOCALS~1
\Temp\dirF40.tmp\make.pl line 57, at EOF
[Tue Apr 26 18:56:39 2005] make.pl: Execution of
C:\DOCUME~1\beatnik\LOCALS~1\Te
mp\dirF40.tmp\make.pl aborted due to compilation errors.
 
N

Nikos

Jim said:
I think you need some way to associate the description of the game with
the name of the game. If all you have is a file full of descriptions,
how will you know which description goes with which game? If you have a
better idea than what I proposed, then use that instead.

No that was the former idea.

I now ahve inside perigrafes.txt both the name of the game and the
description seperated by a tab delimeter character.
 
N

Nikos

Guys do you see soemthing wrong in this?

#!/usr/bin/perl
use strict;
use warnings;

use CGI::Carp qw(fatalsToBrowser);
use CGI qw:)standard);
use DBI;
use DBD::mysql;

print header( -charset=>'iso-8859-7' );
print start_html( -style=>'../style.css', -title=>'Äçìéïõñãßá ÂÜóçò
ÄåäïìÃíùí!', -background=>'../data/images/night.gif' );

my $dbh = ($ENV{'SERVER_NAME'} ne 'nikolas.50free.net')
? DBI->connect('DBI:mysql:test', 'root', '')
: DBI->connect('DBI:mysql:test:50free.net', 'nikos_db', 'tiabhp2r')
or print font({-size=>5, -color=>'Lime'}, $DBI::errstr) and exit 0;

$dbh->do( "drop database if exists nikos_db" );
$dbh->do( "create database nikos_db" );
$dbh->do( "use nikos_db" );

$dbh->do( "create table logs( id int auto_increment primary key, host
text, xronos text, keimeno text, visits int, page int )" ) or die $!;
$dbh->do( "create table public( onoma text, euxoula text, sxolio text,
email text, xronos text, host text )" ) or die $!;
$dbh->do( "create table games( onoma text, description text, counter int
)" ) or die $!;

#===============================================================================

my $sth = $dbh->prepare( "INSERT INTO games( name, description, count)
VALUES (?, ?, ?)" );

open (FILE, "<../data/games/perigrafes.txt") or die "Can't open FILE: $!\n";
while (<FILE>) {
chomp;
my @row = split /\t/;

my $num = scalar @row;
if ($num == 2)
{
push @row, 0;
unless ( $sth->execute(@row) ) { warn $dbh->errstr; }
if ($dbh->errstr)
{
warn "LINE $.: INSERT '$row[0]': $dbh->errstr\n";
}
}
else
{
warn "Error in line $.: number of fields $num\n";
}
close FILE;

#===============================================================================

my @tables = @{ $dbh->selectcol_arrayref('show tables') };

for ( @tables ) {
$sth = $dbh->prepare( "select * from $_" );
$sth->execute();

print font( {-size=>5, -color=>'Cyan'}, $_ );
print font( {-size=>4, -color=>'Lime'}, ol( li( $sth->{NAME} ) ) );
}

This is all the source of make.pl
 
E

Eric Schwartz

Nikos said:
Guys do you see soemthing wrong in this?
$dbh->do( "create table games( onoma text, description text, counter
int )" ) or die $!;
.....

my $sth = $dbh->prepare( "INSERT INTO games( name, description, count)
VALUES (?, ?, ?)" );

Your columns are named differently when you create the table and when
you try to populate it (onoma, description, counter in the first case,
and name, description, count in the second). I suspect this might be
relevant.

-=Eric
 
J

J. Gleixner

Nikos said:
while (<FILE>) {
close FILE;
<h1>Software error:</h1>
<pre>syntax error at
C:\DOCUME~1\beatnik\LOCALS~1\Temp\dirF40.tmp\make.pl line 3
8, near &quot;) warn&quot;
Missing right curly or square bracket at


You should be able to resolve syntax errors. It's telling you exactly
what's wrong.

Your "while" needs to have a closing "right curly", which is '}'.
 
N

Nikos

J. Gleixner said:
You should be able to resolve syntax errors. It's telling you exactly
what's wrong.

Your "while" needs to have a closing "right curly", which is '}'.

Yes i was too tense and i didnt notice that. i noticed it just after i
post it before now seeing your post.
I was counting them and it looked fine.

But the problem is the table agmes aint loading.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top