List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:November 4 2013 5:32pm
Subject:Another query question...
View as plain text  
MySQL 5.0.92-log

I'm trying to form a clickable link using CONCAT, but the link as displayed points to the
NEXT row's URL, not the one from the same row as the other data displayed!

Is there something I don't understand about this?

Below is the query. "{{{1}}}" is replaced by a year, like "2013".

The second column is the problem one. When the table is displayed, the link in the
`Product` field points to the NEXT SEQUENTIAL product row! In other words, if you click
on the link for "garlic," you'll get the page for "gherkins."

Live example is at: http://www.EcoReality.org/wiki/2013_harvest

If you hover over the link in the `Product` column, you can clearly see that the page at
the link is not the same as that in the `ID` column, but is in fact the same `ID` as the
next sequential row. I am so confused.

SELECT
   harvest.product AS ID,
   CONCAT('<a href="http://www.EcoReality.org/wiki/Product/', s_product.ID, '">',
COALESCE(s_product.name, 'TOTAL:'), '</a>') AS `Product`,
   FORMAT(sum(harvest.quantity), 3) AS `<a
href="http://www.EcoReality.org/wiki/Harvest">Harvest</a>`,
   harvest.units AS Units,
  CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
   prices.market_type AS `R-W`,
   COUNT(*) AS Harvests,
   DATE(MIN(harvest.date)) AS Begin,
   DATE(MAX(harvest.date)) AS End
FROM
   s_product_harvest harvest
 INNER JOIN
   s_product on s_product.ID = harvest.product AND
   s_product.units = harvest.units
 LEFT OUTER JOIN
   s_product_market_prices prices ON prices.product_ID = harvest.product AND
   prices.units = harvest.units AND
   year(prices.price_date) = year(harvest.date)
WHERE
   year(harvest.date) = {{{1}}}
GROUP BY
   s_product.name WITH ROLLUP

:::: Some days I wonder if it might not be better to culturally engineer humans to enjoy
small scale garden farming than to genetically engineer weeds to save large scale
agribusiness. -- Gene Logsdon
:::: Jan Steinman, EcoReality Co-op ::::

Thread
Another query question...Jan Steinman4 Nov
Re: Another query question...Jan Steinman4 Nov
  • Re: Another query question...hsv4 Nov
    • Re: Another query question...Reindl Harald5 Nov
Re: Another query question...Jan Steinman9 Nov
  • Re: Another query question...hsv12 Nov