error in string 1

[iluvperl]

This is in Perl and I am getting an error near values( it says.

I'd like to use placeholders but it looks difficult to use with $dbh->do() as I am not particularly familiar with this.

  $dbh->do("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)") unless $dbh->do(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") > 0;

What I need to do is sort of complicated for me, since I know basic MySQL stuff.

I need to check to see if "url" exists in the table "storage" and if it does, update it with new information. If it doesn't, I need to insert it.

I tried to do a SELECT first to check if it existed, but it errored out since it can't prepare an undefined value if it didn't already exist.

So I need help.

 

[r937]

check out the ON DUPLICATE KEY UPDATE syntax option for the INSERT statement

r937.com | rudy.ca

 

[iluvperl]

I read that already but it doesn't seem like it's what I need. I don't want to create a duplicate key if it already exists, I only want to create a row with "url" if it doesn't exist.

 

[r937]

If you specify ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in PRIMARY or UNIQUE key, an UPDATE of the old row is performed.

in other words, if a row with the key value does not exist, the INSERT proceeds, but if the row does exist, the row is updated


that sounds to me like exactly what you want



r937.com | rudy.ca

 

[iluvperl]

I read that all wrong. Geez. I read that it would cause a duplice value and thought it'd do everything twice.

Wow. I better go try this!

Thanks

 

[iluvperl]

I'm back. It still looks like the perfect solution but I can't get it to work. The below code adds zeros to all columns every time my script loads.

  my $data = qq(INSERT INTO storage (url, altavista, yahoo, msn, teoma, google, alltheweb, Total, lastsearch, totalsearch) values(url = "$url", altavista = "$altavista_results", yahoo = "$yahoo_results", msn = "$msn_results", teoma = "$teoma_results", google = "$google_results", alltheweb = "$alltheweb_results", Total = "$total", lastsearch = "$time", totalsearch = "1") ON DUPLICATE KEY UPDATE totalsearch = totalsearch+1 );
  my $sth = $dbh->prepare($data);
  $sth->execute() or die $dbh->errstr;

Of course, none of my variables I'm using are zeros.

Also, where do I specify which unique key I want to check on? I need to check on "url" and I fear that when this is working, it'll check the "id" field.

Thanks for your help.

 

[r937]

the error lies in the following incorrect syntax

... values(url = "$url", ...

unfortunately mysql is very lax, and actually interprets that equality expression, which, naturally, evaluates to false, which is equivalent to 0

the correct syntax is just the value, not an equality expression

INSERT INTO storage (url, altavista, ...
VALUES ('$url', '$altavista_results', ...

as for the ON DUPLICATE KEY UPDATE syntax, i believe that is where you want the equalities, which is roughly the same syntax as in an UPDATE statement after the SET operator

ON DUPLICATE KEY
UPDATE url = "$url"
, altavista = "$altavista_results"
, ...

r937.com | rudy.ca

 

[iluvperl]

Why does this have to be so complicated? lol.

I got rid of the zeros and it's pushing in real data now (which is good) but it's not doing anything for duplicates. Everything is being pushed in, new entry or not.

I think I need to specify which key for it to check for, but I didn't read that on the docs.

  my $data = 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", "1") ON DUPLICATE KEY UPDATE totalsearch = totalsearch+1 );
  my $sth = $dbh->prepare($data);
  $sth->execute() or die $dbh->errstr;

 

[r937]

did you define a primary key for the table?


r937.com | rudy.ca

 

[iluvperl]

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS storage
    (
       id int auto_increment NOT NULL,
       URL VARCHAR(100) NOT NULL,
       altavista VARCHAR(100) NOT NULL,
       yahoo VARCHAR(100) NOT NULL, 
       msn VARCHAR(100) NOT NULL,
       teoma VARCHAR(100) NOT NULL,
       google VARCHAR(100) NOT NULL, 
       alltheweb VARCHAR(100) NOT NULL,
       Total VARCHAR(100) NOT NULL,
       lastsearch VARCHAR(25) NOT NULL,
       totalsearch VARCHAR(10) NOT NULL,    
       primary key  (id)
    )");
$sth->execute();

Is how it is setup. I never messed with the primary key. Should I reset the database and change the last line to URL?

 

[iluvperl]

Nevermind, I got it working now.

I had to remove id int auto_increment, which is a bummer, but I reassigned the primary key and it works now.

Thanks for all your help, you get a star!

 

[r937]

you made url the primary key? probably just as well

i think you could've made it a unique key instead, but in this case, the auto_increment primary appears not to have had an important role

r937.com | rudy.ca

 

[iluvperl]

I'm back again with another snippet similar to the one before. It says the error is in the SET area.

  my $data = qq(INSERT INTO temp (url, altavista, yahoo, msn, teoma, google, alltheweb, Total) values("$url", "$altavista_results", "$yahoo_results", "$msn_results", "$teoma_results", "$google_results", "$alltheweb_results", "$total") ON DUPLICATE KEY UPDATE SET altavista = $altavista_results, yahoo = $yahoo_results, msn = $msn_results, teoma = $teoma_results, google = $google_results, alltheweb = $alltheweb_results, $total WHERE url = "$url");
  my $sth = $dbh->prepare($data);
  $sth->execute() or die $dbh->errstr;

I really do appreciate the help.

 

[r937]

please check the manual for the correct syntax

it's pretty plain about which word you shouldn't have in there

r937.com | rudy.ca

 

[iluvperl]

I read the UPDATE syntax page. There was an example with and without SET in there.

I tried putting parens around the SET like I think I did on a past script, but that failed. I removed SET entirely, and that failed.

Originally everything was quoted again, and that failed too (which is why I removed them).

 

[r937]

UPDATE tbl_name
SET col_name1=expr1 ...

--

http://dev.mysql.com/doc/refman/5.1/en/update.html

INSERT [INTO] tbl_name
VALUES ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

--

http://dev.mysql.com/doc/refman/5.1/en/insert.html

there is no SET in the ON DUPLICATE KEY UPDATE syntax



r937.com | rudy.ca

 

[iluvperl]

UGH *rips out hair*

I know what I did and I can fix it but now I am getting more confused.

The originally error was me trying to place a WHERE clause inside, forgetting it does this on the primary key. So I went back and removed the ID column and had to update the rest of my script.

But by removing the ID column with an auto_increment, I now don't know how to pull back the LAST 10 records.

my $data = qq[SELECT url, altavista, yahoo, msn, teoma, google, alltheweb, Total FROM temp WHERE id > (SELECT MAX(id) FROM temp) - 10
ORDER BY Total DESC];

This is what I had before and it found the last 10 records based on the ID column.

So is there a way to add the ID column back, set it as the primary key and get the ON DUPLICATE to work on any particular field I choose? Or how is this supposed to be done?

Note: the above code retrieves the last 10 items fine, it doesn't sort based on Total though. That question was asked on a different forum entirely.

 

[r937]

So is there a way to add the ID column back, set it as the primary key and get the ON DUPLICATE to work on any particular field I choose?

yes, declare the particular field as a unique key

r937.com | rudy.ca