DBI functions in Perl

[MartinTygsen]

Hello Perl friends

I need a little help for 2 questions.

1. $dbh->do("INSERT INTO bookmark values(id,'$title','$link','$descrip')"); -->How do I make it so the line only execute one time. As you can see in my script ( bottom of my topic ) The line will generate a empty field every time it is loaded. ( hope you understand me )

2. When I use me delete(slet on danish) function, I have to reload the page to see the entry is deleted. Can I someway get the script to update it self?

The HTML code

<form name="form1" method="post" action="../cgi-bin/index.pl">
 <p> Titel : <input name="title" type="text">
 </p>
 <p> Link : <input name="link" type="text">
 </p>
Beskrivelse:<br>
 <textarea name="descrip"></textarea><br>
 <br>
 <input name="Reset" value="Reset" type="reset"> <input name="Submit" value="tilf&oslash;j" type="submit">
</form>

Perl Code

#!c:/perl/bin/perl.exe
use strict;
use CGI ':standard';
use DBI;
print "Content-type: text/html\n\n";
my $DataBaseName = "bookmark";
my $DataBaseHost = "localhost";
my $DataBaseUser = "root";
my $DataBasePass = "";
my $dbh = DBI->connect("DBI:mysql:database=$DataBaseName;host=$DataBaseHost",
 "$DataBaseUser",
 "$DataBasePass",
 { RaiseError => 1,
 AutoCommit => 0 }) ¦¦ die "Unable to connect to $DataBaseHost because $DBI::errstr";
my $title = param('title');
my $link = param('link');
my $descrip = param('descrip');
$dbh->do("INSERT INTO bookmark values(id,'$title','$link','$descrip')");
#henter data ind fra databasen
my $sql = "SELECT * FROM bookmark ORDER BY id DESC";
my $sth = $dbh->prepare($sql);
$sth->execute();
#slet funktionen
my $id = param('slet');
my $rows_deleted = $dbh->do(q[DELETE FROM bookmark WHERE id =?], undef, $id) ¦¦ print "Problem deleting \$id=$id $dbh->errstr";
#outputtet
while (my ($id, $title, $link, $descrip) = $sth->fetchrow_array ) {
print qq(
<style type="text/css">
<!--
table {
 border:1px solid black;
}
.style1 {
 font-family: Verdana, Arial, Helvetica, sans-serif;
 font-size: 10px;
 font-weight: bold;
}
.style2 {
 font-family: Verdana, Arial, Helvetica, sans-serif;
 font-size: 10px;
}
-->
</style>
<table width="631">
 <tr>
 <td colspan="4"><span class="style1"><a href="$link">$title</a></span></td>
 </tr>
 <tr valign="top">
 <td colspan="4"><span class="style2">$descrip</span></td>
 </tr>
 <tr>
 <td width="54"><a href="rediger.pl"><img src="../rediger.jpg" width="54" height="13" border="0"></a></td>
 <td width="529"><a href="?slet=$id"><img src="../slet.jpg" width="54" height="13" border="0"></a></td>
 <td width="15"><img src="../op.jpg" width="15" height="15" border="0"></td>
 <td width="15"><img src="../ned.jpg" width="15" height="15" border="0"></td>
 </tr>
 <br>
</table>);
}
$dbh->disconnect();

[KevinADC]

my $title = param('title');
my $link = param('link');
my $descrip = param('descrip');

if ($title and $link and $descrip) {
$dbh->do("INSERT INTO bookmark values(id,'$title','$link','$descrip')");
}

[MartinTygsen]

I thought I tried that :( but it works THANK YOU.

Could you also solve the refresh problem?

[KevinADC]

I don't know how to solve the refresh problem.

[bennymack]

Change:

AutoCommit => 0

to

AutoCommit => 1

and

$dbh->do("INSERT INTO bookmark values(id,'$title','$link','$descrip')");

to

$dbh->do('INSERT INTO bookmark values(id,?,?,?)',$title,$link,$descrip);

[KevinADC]

good suggestion bennymack. I'm weak in the DBI and SQL stuff and was not aware of that possibility.

[MartinTygsen]

It does not work, now I cant make entries in the database anymore?

[MartinTygsen]

can you try to explain what you do ind the $Dbh line?

[bennymack]

Whoops, that last line there is wrong. It should be:

$dbh->do('INSERT INTO bookmark values(id,?,?,?)',undef,$title,$link,$descrip);

It's simply using bind variables instead of direct substitution of the variables. This is a much safer way to do queries because the DBD layer will escape quotes in whatever manner the underlying DB needs them to be quoted.

[MartinTygsen]

Thanks now it works again, but it still dont solve the problem by updating.

maybe i didn't explain it good enough.

when I have my list of entries

blabla1
blabla2
blabla3

and i push the delete button on one of the lines. I get this line in the browser

index.pl?slet=101

And the entry is deleted from the database.

BUT i think internet explorer is caching the site so the entry does still appear on the link untill i hit F5

[lexipixel]

Do you have (IE) Setting for:

Check for newer versions of page:
[x] every visit to the page

If script is calling itself it is generating a new page on the fly and should never pulled a cached page unless you use the [BACK] button.

OR MAYBE ...looking at your code, it almost appears the script may write the entry to the database when deleting (slet).

Is there something in the code that jumps it to the slet function AND skips over henter?

It looks like it runs both.

[MartinTygsen]

I am new at this :)

Firefox also need a refresh to make it disapear.

henter=showing data strings on danish :)

But no the code is exactly like it is in this post. So maybe this is the problem? but how could I solve it?

[KevinADC]

yea, I think the problem is the script just runs from start to end without checking input parameters to execute sub routines.

get you input variables at the beginning of the script and depending on the input direct the script to execute a sub routine, a crude example:

my $title = param('title');
my $link = param('link');
my $descrip = param('descrip');
my $id = param('slet');
if ($id) {
slet($id);
}
else {
something else
}

[MartinTygsen]

I know I am stupid, but i really cant figure out how to define this in my script?

could you help me a little more on the way?

[lexipixel]

Forget the specific code for a minute and try to understand the logic.

The logic operators are:

eq ............. EQUAL
ne ............. NOT EQUAL
gt ............. GREATER THAN
lt ............. LESS THAN

here's a code snip using them in 'if' statements-

$a = '99';
$b = '100';
#
if ($a eq $b) { print 'A equals B'; }
if ($a ne $b) { print 'A is not equal to B'; }
if ($a gt $b) { print 'A is greater than B'; }
if ($a lt $b) { print 'A is less than B'; }
#
#

You can take it a step further and nest multiple if, elsif, else statements in a single logic block-

#
if ($a eq $b) {
print 'A equals B';
} else {
print 'A is not equal to B, and ';
if ($a gt $b) { print 'A is greater than B'; }
if ($a lt $b) { print 'A is less than B'; }
}
#

(in the above block, since we first test for equal, we know if it does not pass the test, it is NOT EQUAL and we can go on to test if A is greater or less than B).

We can then go further and only execute certain code based on a logic statement. Let's add a hidden field to a form to make it easy.

In the form add-

<input type=hidden name=foo value=DEL>

Now in our script we can test for the value of 'foo' and only take certain action based on what foo is set to;

#
if ($foo eq 'DEL') {
#
# code to delete a record
#
} elsif {$foo eq 'ADD') {
#
# code to add a record
#
} elsif ($foo eq 'MOD') {
#
# code to modify a record
#
} else {
#
# code for error (no mode specified)...
#
}

...clear as mud?

[PhraSEOlogy]

You can always add this code to tell the browser that the page has expired when it is displayed.

$expires = $^T;
$formatime = gmtime($expires);
print "Expires: $formatime GMT\n";
print "Content-type: text/html\n\n";

So the browser should always display a fresh page.

This may not be what you want but I thought I would put it out here anyhow - just in case.