perl and mysql problem fetching

[marcel2]

Hello,

I have to questions about some coding.

* I have some nummeric code that I need to fetch thats working partly. if I use this if ($cur_group =~ /222/) { $section_started=1; } code is being fetched but not only code after 222 also code after 37222 or 32222 how can I be sure the script only decodes 222 and nothing else?

* I need to put data in to a mysql thats working data is being put in by this code;
$sth= $DBH -> prepare("SELECT id from hour WHERE id=$NAME"
$sth -> execute() || print "CANT EXEC";

if(!($sth->fetchrow)) {
$inserts++;

$DBH ->do("INSERT INTO hour VALUES ($NAME,$NINE_CODE_PHENO)&quot ||
print &quot;Can't add value. Reason: $DBI::errstr $new_idx<BR>

INSERT INTO hour VALUES ($NAME,$NINE_CODE_PHENO)<BR>&quot;;
}

When id is already in the dbase it has to add it, not update the old data. What is wrong or is it already working?

Hope you understand my needs.
Greatings
Marcel

 

[fishiface]

You could use
[tt]if ($cur_group == 222)[/tt]
for a numeric comparision - this would allow 222.0, for example - or
[tt]if ($cur_group eq '222')[/tt]
for a textual comparison. Only &quot;222&quot; would be allowed. The regular expression syntax you use is very powerful but over the top for this application.

There is no update statement in the code you show. A do() call returns the numbers of rows affected so you can try an insert and, if it fails, do an update or vice versa (decide which case is going to be the most common in order to minimise the number of transactions).

One problem is that the default behaviour of DBI is to shriek at errors, so you may wish to turn this off for the one statement.

Something like

    my $ret = $DBH->do(
            q{ INSERT INTO hour VALUES ( ?, ? ) },
            { PrintError => 0 },
            $NAME, $NINE_CODE_PHENO
    );
    die $DBI::errstr unless $DBI::err == -239; # duplicate row
    $DBH->do(
            q{ UPDATE hour SET hr_code = ? WHERE hr_name     = ? },
            {},
            $NINE_CODE_PHENO, $NAME
    ) unless $ret == 1; # ie unless the insert worked

The other option would merely swap the order of the [tt]do()[/tt]s and change the error code for which we test.

I'd advise use of the '?' placeholders even if you don't feel you need them. It's a good habit to get into and it prevents a stray quote in user input from breaking your query once it's in production. -T is also a good habit with database work.


HTH,


fish

&quot;As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.&quot;
--Maurice Wilkes