This is a two part problem

I need to UPDATE ON DUPLICATE for this MySQL query so I can perform the needed actions based on whether or not a particular field was found. If it wasn't found, insert it, otherwise update a few of the fields

  my $data = qq(INSERT INTO temp (url, altavista, yahoo, msn, teoma, g
+oogle, alltheweb, Total) values("$url", "$altavista_results", "$yahoo
+_results", "$msn_results", "$teoma_results", "$google_results", "$all
+theweb_results", "$total") ON DUPLICATE KEY UPDATE altavista = "$alta
+vista_results", yahoo = "$yahoo_results", msn = "$msn_results", teoma
+ = "$teoma_results", google = "$google_results", alltheweb = "$allthe
+web_results", total = "$total");
  my $sth = $dbh->prepare($data);
  $sth->execute() or die $dbh->errstr;

[download]
I have an id auto_increment field as a primary key. I can't seem to get this to hold when UPDATE ON DUPLICATE works on the primary key. And since id is new with each row, of course all data will then be inserted because it's new data.

The id column is important because, with it, I can track down the last 10 rows entered into the table.

My question is, what do I have to do in order to perform the insert/or update and the last 10 rows in order to work? It was mentioned in the CB that I could use a time column with $time. It sounds like a great idea but not sure how I'd find the last 10 newest entries based on time.

The second question is, how can I bring back the latest 10 rows AND THEN sort them by a particular field "Total"? 

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

[download]
This doesn't sort by Total like it should.

In reply to UPDATE ON DUPLICATE and sorting by coldfingertips

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.