Games.pl again

[Nikos]

#=======================LOADING THE .TXT TO THE
DATABASE========================

my (@row, $gamename, $gamedesc, $gamecount);
my $select = $dbh->prepare( "SELECT * FROM games WHERE gamename=?" );
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc,
gamecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=?+1
where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
while (<FILE>) {
chomp;

($gamename, $gamedesc) = split /\t/;
$select->execute( $gamename );

if ($select->rows) {
$update->execute( $gamedesc, $gamecount, $gamename );
}
else {
$insert->execute( $gamename, $gamedesc, 0 );
}
}
close (FILE);

#=============================SHOW THE GAMES
TABLE==============================

print span( {class=>'lime'}, "Ãðü åäþ ìðïñåßò íá êáôåâÜóåéò ùñáßá
áðëÜ ðáé÷íßäéá ðïõ Ã÷ù åðéëÃîåé!" ), br;
print span( {class=>'yellow'}, "Ìðïñåßò íá åðéêïéíùíÞóåéò ìáæß ìïõ óôï
hackeras\@gmail.com" ), br() x 2;

$sth = $dbh->prepare( "SELECT * FROM games" );
$sth->execute;

print start_form(-action=>'games.pl');
print table( {class=>'games'} );
while( $row = $sth->fetchrow_hashref )
{
print Tr(
td( {-width=>'20%'}, submit( $row->{gamename} )),
td( {-width=>'75%'}, $row->{gamedesc} ),
td( {-width=>'5%'}, $row->{gamecounter} )
);
}
print end_table;
print end_form;

print br;

if ( !param() ) {
print p( {-align=>'center'}, a( {href=>'index.pl'}, img
{src=>'../data/images/back.gif'} ));
}

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

Its no that i want to deliberately ignore the advice you proposed to me,
its just that i almost never use primary key as text only as integers
with auto_increment.

Apart from that i wanted to see how would it work they way i have it.
Is there really anned for a primary key?
We can always select from our mysql table to see if the record exists or
not to update or insert accordingly!

 

[Brian Wakem]

my $select = $dbh->prepare( "SELECT * FROM games WHERE gamename=?" );

We can always select from our mysql table to see if the record exists or
not to update or insert accordingly!

I think it would be better/faster to do a SELECT COUNT(*) FROM games
WHERE ....... and then check whether the value is true or not, rather than
selecting an unknown number of rows in their entirety.

 

[Nikos]

Nikos wrote:






I think it would be better/faster to do a SELECT COUNT(*) FROM games
WHERE ....... and then check whether the value is true or not, rather than
selecting an unknown number of rows in their entirety.

Yes i agree.

But how am i gona check if the value is true or not?

 

[Nikos]

Perhaps you mean like this:

my ($gamename, $gamedesc, $gamecount);
my $select = $dbh->prepare( "SELECT count(*) FROM games WHERE gamename=?" );
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc,
gamecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=?+1
where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
while (<FILE>) {
chomp;

($gamename, $gamedesc) = split /\t/;

if ( $select->execute($gamename) ) {
$update->execute( $gamedesc, $gamecount, $gamename );
}
else {
$insert->execute( $gamename, $gamedesc, 0 );
}
}
close (FILE);

 

[Tad McClellan]

my (@row, $gamename, $gamedesc, $gamecount);

You should declare variables in the smallest possible scope.

That usually means declare them where you first use them.

Your code never uses @row, so you don't need to declare it at all.

Your code never puts a value into $gamecount (do you have warnings enabled?).

my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc,
gamecounter) VALUES (?, ?, ?)" ); ^^^^^^^^^^^
^^^^^^^^^^^
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=?+1

^^^^^^^^^

One of these things is not like the other,
one of these things just doesn't belong...

($gamename, $gamedesc) = split /\t/;

my($gamename, $gamedesc) = split /\t/; # declare at first use

if ( !param() ) {

unless ( param() ) {

 

[Brian Wakem]

Perhaps you mean like this:

my ($gamename, $gamedesc, $gamecount);
my $select = $dbh->prepare( "SELECT count(*) FROM games WHERE gamename=?"
); my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc,
gamecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=?+1
where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
while (<FILE>) {
chomp;

($gamename, $gamedesc) = split /\t/;

if ( $select->execute($gamename) ) {
$update->execute( $gamedesc, $gamecount, $gamename );
}
else {
$insert->execute( $gamename, $gamedesc, 0 );
}
}
close (FILE);

Not quite. I meant like this:-

....
$select->execute($gamename);
my $record_exists = $select->fetchrow_array();
if ($record_exists) {
# update
}
else {
# insert
}

 

[Nikos]

Not its like this but its till problematic:

my ($gamename, $gamedesc, $gamecounter);
my $select = $dbh->prepare( "SELECT count(*) FROM games WHERE gamename=?" );
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc,
gamecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?,
gamecounter=?+1 where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
while (<FILE>) {
chomp;

($gamename, $gamedesc) = split /\t/;

$select->execute($gamename);
my $record_exists = $select->fetchrow_array();

if ($record_exists) {
$update->execute( $gamedesc, $gamecounter, $gamename );
}
else {
$insert->execute( $gamename, $gamedesc, 0 );
}
}
close (FILE);

 

[Brian Wakem]

Not its like this but its till problematic:

Sorry I do not understand that.

 

[A. Sinan Unur]

Not its like this but its till problematic:

You are making absolutely no sense whatsoever.

If you want answers, you will have to read and follow:

comp.lang.perl.misc guidelines on the WWW:
http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html


Sinan

 

[Tad McClellan]

If you want answers, you will have to read and follow:

comp.lang.perl.misc guidelines on the WWW:
http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html

It also helps to get an answer if you actually _ask_ some question...


(questions are easily identified, as they end with a question mark.)

 

[Nikos]

It also helps to get an answer if you actually _ask_ some question...


(questions are easily identified, as they end with a question mark.)

my ($gamename, $gamedesc, $gamecounter);
my $select = $dbh->prepare( "SELECT count(*) FROM games WHERE gamename=?" );
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc,
gamecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?,
gamecounter=?+1 where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
while (<FILE>) {
chomp;

($gamename, $gamedesc) = split /\t/;

$select->execute($gamename);
my $record_exists = $select->fetchrow_array();

if ($record_exists) {
$update->execute( $gamedesc, $gamecounter, $gamename );
}
else {
$insert->execute( $gamename, $gamedesc, 0 );
}
}
close (FILE);

Well yes.
Something wrong with update. the last gamename from description.txt
always updates and not the others.

Code seems fine though and i made it as straightforward as i could.
I think it explains very well what i am up to.
But please if you have questions on what iam trying to do just ask me....

Or maybe we can write it even better.
you can always check how it is run by visiting http://www.nikolas.tk
going bottom down & clicking the fun ballon button. Then games.pl loads.
IUnfortunately i cant explain better why its nor runnign as expected.
you can see for yourself!

 

[Tad McClellan]

Tad McClellan wrote:

[snip text, none of it ending with a question mark]


It helps to get an answer if you actually _ask_ some question...

Is there anybody in there? Just nod if you can hear me.

 

[Nikos]

Tad McClellan wrote:

Tad i changes the code i posted to you with this one, which is even more
straight forward that the other: Here it is:


#============LOADING THE .TXT TO THE DATABASE================

my ($gamename, $gamedesc, $gamecounter);
my $replace = $dbh->prepare( "REPLACE INTO games (gamename, gamedesc,
gamecounter) VALUES (?, ?, ?)" );

open (FILE, "<../data/games/descriptions.txt") or die $!;

while (<FILE>) {
chomp;
$replace->execute( split(/\t/, $_, 2) , 0 ) or print $dbh->errstr;
}

close (FILE);


and i am getting the following error:

called with 1 bind variables when 3 are neededcalled with 1 bind
variables when 3 are neededcalled with 1 bind variables when 3 are
neededcalled with 1 bind variables when 3 are neededcalled with 1 bind
variables when 3 are neededcalled with 1 bind variables when 3 are
neededcalled with 1 bind variables when 3 are neededcalled with 1 bind
variables when 3 are neededcalled with 1 bind variables when 3 are
neededcalled with 1 bind variables when 3 are neededcalled with 1 bind
variables when 3 are neededcalled with 1 bind variables when 3 are
neededcalled with 1 bind variables when 3 are neededcalled with 1 bind
variables when 3 are neededcalled with 1 bind variables when 3 are
neededcalled with 1 bind variables when 3 are neededcalled with 1 bind
variables when 3 are neededcalled with 1 bin

I cant understand it though.

 

[Nikos]

i changed the connect method to:

my $dbh = DBI->connect('DBI:mysql:nikos_db', 'root', '*******',
{RaiseError=>1});

instead of

my $dbh = DBI->connect('DBI:mysql:nikos_db', 'root', '*******') or
{RaiseError=>1});


and now i am getting this error:

DBD::mysql::st execute failed: called with 1 bind variables when 3 are
needed at C:\DOCUME~1\beatnik\LOCALS~1\Temp\dzprltmp.pl line 30, <FILE>
line 2.

Pleas help out a bit i dont know whts this error mean or how to proceed
any more.

 

[Tad McClellan]

Tad McClellan wrote:

Please do not say I wrote stuff that I did not write.

Your repeated carelessness shows a disrespect for your audience.

my $replace = $dbh->prepare( "REPLACE INTO games (gamename, gamedesc,
gamecounter) VALUES (?, ?, ?)" );

$replace->execute( split(/\t/, $_, 2) , 0 ) or print $dbh->errstr;

called with 1 bind variables when 3 are needed

[ snip a bunch more of the same message]

Seeing the identical string dozen times does not help us help you,
it hurts us being able to help you.

So please don't do that.

I cant understand it though.

In your prepare() you have 3 placeholders.

In your execute() you have only 1 value when there should be 3.

The split() must be returning a 1-element list rather than
the 3-element list you seem to be expecting.

ie. You have processed a data line with zero tabs on it.