Originally there were quotes around all variables in the values() but I took them out thinking they were the problem.

There's a reason single quotes would have been there, unless the variables in question are already $dbh->quote 'd, then the correct syntax for the INSERT statement calls for the data to be quoted.

From the limited snippet you supply, that's about all one can ascertain.

Scott

unless($dbh->do("UPDATE storage SET (url = ?, altavista = ?, yahoo = ?
+, msn = ?, teoma = ?, google = ?, alltheweb = ?, total 
+= total +1, time = ?",undef,$url,$altavista_results,$yahoo_results,$m
+sn_results,$teoma_results,$google_results,$alltheweb_results,$time) >
+ 0){
  $dbh->do("INSERT INTO storage (url, altavista,yahoo, msn, teoma, goo
+gle, alltheweb,Total, lastsearch, totalsearch) values(?,?,?,?,?,?,?,?
+,?)",undef,$url, $altavista_results, $yahoo_results, $msn_results, $t
+eoma_results, $google_results, $alltheweb_results, $total,$time, $tot
+al)");
}
[download]

Is there a syntax error in yoru code? I C/P'd and the whole thing went nuts on me.

I took a quick look on insert on duplicate key but I want the opposite of what it does. From what I read, it'd make a duplicate of it if it's found. I'd only want to insert IF the column "url" was not found in the first place.

Thanks.

I C/P'd and the whole thing went nuts on me.

When you did that, were you careful to remove all the "+" characters that had been inserted by PM's own code-line-wrap logic?

It looks like reneeb was not so careful about this, because that post includes  total += total +1 (where the first "+" had been added by PM line-wrapping).

BTW, "went nuts" doesn't sound like the kind of error code that I could look up and help you with. If you don't actually look at the content of the error report, and don't show us what it is, how do you expect to fix the error?

Any error messages??

DBI's do() method does not use placeholders - it's there specifically for drivers that do not support placeholders.

You should quote any strings in the SQL that you're executing.
You might want to format things a little better so that any errors are easier to find, rather than having a huge block of code (which in this case has two SQL statements).

Edit: what am I talking about? do() does support placeholders.

do does support placeholders. Have a look at http://search.cpan.org/~timb/DBI-1.50/DBI.pm#Database_Handle_Methods

It's hard to find the error because your code is all on one line, but it looks like the problem is that your URL isn't quoted. That must be the problem.

Using placeholders might make it easier for you to code if you are using a loop to execute the statements, but it might not give you a performance benefit in MySQL. MySQL doesn't support server-side prepared statements by default and I think that's where the performance benefit comes in with placeholders and bind values. I've had some problems with turning on this setting on, but try it if you want to experiment with it. ref: DBD::mysql (search for "prepared").

Columns with character data types will need quotes. You can quote everything, though because it won't break anything with the other data types.

MySQL will tell you what's wrong if you set up your queries properly. Break it back out to more lines during troubleshooting, then condense it again when everything works.

Here's my suggested method:

use strict;
use warnings;

my $connect_string = "DBI:mysql:$CONFIG::database";
my $dbh = DBI->connect( $connect_string, $CONFIG::dbusername,
    $CONFIG::dbpassword, \%CONFIG::dbattr );
my $statement;
my $result;

{
    package CONFIG;

    %dbattr = (
        PrintError => 0,
        ChopBlanks => 0,
    );

    $database   = "stuff";
    $dbusername = "username";
    $dbpassword = "password";
}

my $insert = qq{
    INSERT INTO storage ( url, altavista, yahoo, msn, teoma,
        google, alltheweb, Total, lastsearch, totalsearch )
    values(
        '$url',
        '$altavista_results',
        '$yahoo_results',
        '$msn_results',
        '$teoma_results',
        '$google_results',
        '$alltheweb_results',
        '$total',
        '$time',
        '$total'
    )
};

my $update = qq{
    UPDATE storage
    SET
        url       = "$url",
        altavista = "$altavista_results",
        yahoo     = "$yahoo_results",
        msn       = "$msn_results",
        teoma     = "$teoma_results",
        google    = "$google_results",
        alltheweb = "$alltheweb_results",
        total     = total +1,
        time      = "$time"
};

$statement = $update;
$result = $dbh->do( $statement );
if ( ! defined $result ) {
    die "Fatal SQL error :\n$statement\n" . $dbh->errstr;
}

# If there were no rows affected by the update, execute the
# insert
if ( $result < 1 ) {
    $statement = $insert;
    $result = $dbh->do( $statement );
    if ( ! defined $result ) {
        die "Fatal SQL error :\n$statement\n" . $dbh->errstr;
    }
}
[download]

--
-- GhodMode

Please use the placeholders!
What if the google result contains a ' ?? Then your statement fails. Or use at least the $dbh->quote() method. It is much more security to your scripts.