Francis Tresham (c. 1567 – 1605) was one of the English provincial
Catholics who planned the failed Gunpowder Plot of 1605, a conspiracy to
assassinate King James I. Having previously been imprisoned for his
role in a failed rebellion and involved in missions to Spain that sought
support for persecuted English Catholics, Tresham joined the Gunpowder
Plot in October 1605. Its leader Robert Catesby asked him to provide a
large sum of money and the use of Rushton Hall, but Tresham apparently
provided neither, instead giving a small amount of money to fellow
plotter Thomas Wintour. Tresham also expressed his concern that two of
his brothers-in-law would be killed if the plot succeeded. An anonymous
letter delivered to one of them was handed to Robert Cecil, 1st Earl of
Salisbury, and was decisive in foiling the conspiracy. Historians
suspect that Tresham wrote it, although this is unproven.
(even though it peripherally involves MySQL)
This on MediaWiki 1.16.2 (Yea, I know. I will upgrade Any Day Now...)
I am trying to do conditional display, based on the result of a custom extension. This extension evaluates MySQL.
I want to include a header and table if records exist, otherwise not include the header nor the empty table.
I have an extension called "sql2wikiInline" that returns exactly one row, without column headers. I ask the table for a "count(*)" and then conditionally include the header and the result of another SQL query, this one with multiple lines and column headers.
The problem is that #ifeq: does not seem to be working as I understand it. My "sql2wikiInline" apparently appends whitespace to the end, but according to the documentation of #ifeq:, it should not matter for comparing numerics. But quoting the whitespace ("0 ") doesn't work, either.
Doing this:
COUNT = "{{#tag:sql2wikiInline|SELECT count(*) FROM s_product_harvest h INNER JOIN mw_user u ON h.who1 = u.user_id WHERE u.user_name = '{{{1}}}'|database=EcoReality}}"
prints
COUNT = "0 "
which I would assume #ifeq: would properly compare to an unquoted "0" numeral.
But doing this:
xxx {{#ifeq:"0 "|"{{#tag:sql2wikiInline|SELECT count(*) FROM s_product_harvest h INNER JOIN mw_user u ON h.who1 = u.user_id WHERE u.user_name = '{{{1}}}'|database=EcoReality}}"|no harvests|some harvests}} xxx
prints
xxx some harvests xxx
no matter what number the count(*) expression returns, with or without the quotes around the comparison parameters.
So, two questions for the parser function gurus out there:
1) Am I doing something obviously wrong? I've poked around at a few different elaborations, and can't make it work.
2) If it appears I'm using it correctly, what's my next step? How can I determine if my SQL extension is sneaking in some invisibles that are blowing #ifeq:s mind?
Thanks in advance for any assistance offered -- and I have the right list this time, I hope!
PS: this code is on a template page that includes a "some records" example:
http://www.ecoreality.org/wiki/Template:Personal_statistics_for
and here is a page that uses the template above, where the record count is zero:
http://www.ecoreality.org/wiki/User:Mary_Troke/Statistics
Self sign-up is disallowed and only administrators can edit pages with this extension. If you REALLY want to get in and play with it, let me know and I'll give you an admin account.
:::: The primary impediment to community is the stranglehold that hierarchical corporate-driven models of employemnt have on individuals. They so completely drain the life-energy of people in endeavors that are only marginally related to survival or fun that when people return home at the end of the day they're exhausted, suitable only for a few hours of TV and dropping into bed. -- Thom Hartmann
:::: Jan Steinman, EcoReality Co-op ::::
Sheesh. My apologies for posting SQL questions to the wrong list. I thought I was posting to the MySQL list. Please ignore.
But if any of you are MySQL gurus and want to help, feel free to reply privately. :-)
:::: When you find the mind judging, you don't have to stop it from doing that. All that is required is to be aware of it happening. No need to judge the judging and make matters even more complicated for yourself. -- Jon Kabat-Zinn
:::: Jan Steinman, EcoReality Co-op ::::
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 ::::
I'm using MySQL 5.0.92-log.
I'm trying to do a pivot-sort-of-thing. I've tried a few things from the O'Reilly "SQL Cookbook," but I seem to be having a mental block.
I have a table of farm harvests. Each harvest has a date, quantity, and foreign keys into product and harvester tables:
----------------
CREATE TABLE s_product_harvest (
id int(10) unsigned NOT NULL auto_increment,
`date` datetime NOT NULL COMMENT 'Date and time of harvest.',
product int(11) unsigned NOT NULL default '53',
quantity decimal(10,3) NOT NULL default '1.000',
units enum('kilograms','grams','pounds','ounces','liters','each','cords','bales') character set utf8 NOT NULL default 'kilograms',
who1 int(5) unsigned NOT NULL default '2' COMMENT 'Who harvested this resource?',
notes varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (id),
KEY product (product),
KEY `date` (`date`),
KEY who1 (who1),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='historical list of EcoReality farm products harvested';
----------------
What I want is a report with years as columns, and rows of:
first harvest (MIN(date)),
last harvest (MAX(date)),
days of harvest (DATEDIFF(MAX(date), MIN(date))) and
total (SUM(quantity)).
first/last 2007 2008 2009 ...
first Aug 5 Sep 27 Aug 7
last Oct 1 Nov 24 Oct 16
days 57 108 82
kg 10.17 16.7 46.53
This is my first attempt, and it appears to be giving me a row per year, with the first sequential harvest date for each year. I can get the data I want by making each one a separate column, but that's ugly and I want them in rows.
SELECT
'first_last' AS `First/Last`,
CASE WHEN YEAR(harvest.date)='2007' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2007',
CASE WHEN YEAR(harvest.date)='2008' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2008',
CASE WHEN YEAR(harvest.date)='2009' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2009',
CASE WHEN YEAR(harvest.date)='2010' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2010',
CASE WHEN YEAR(harvest.date)='2011' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2011',
CASE WHEN YEAR(harvest.date)='2012' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2012',
CASE WHEN YEAR(harvest.date)='2013' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2013',
CASE WHEN YEAR(harvest.date)='2014' THEN DATE_FORMAT(harvest.date, '%b %e') ELSE 0 END AS '2014'
FROM
s_product_harvest harvest
WHERE harvest.product = 4 /* product ID for tomatoes */
GROUP BY YEAR(harvest.date)
Using an example from "SQL Cookbook" on page 372, I tried to select from a subquery, grouped by a rank, but I kept getting one result row, and I can't figure out how to get the literal row headers.
Any ideas?
:::: Compared to those on pasteurized milk, children who received raw certified milk had better weight gain and greater protection against rachitis. -- Ron Schmid
:::: Jan Steinman, EcoReality Co-op ::::
>From what I understand, a category doesn't exist until the page for the
category is edited, so even if there are categories listed on a page,
unless those categories have been created, they do not actually exist until
their description pages have been edited.
I ask this because I'm in charge of wiki that has a lot of blank categories
(it was created from an XML dump made from content from another wiki engine
that did not support categories in the MediaWiki sense), and I would like
to mass add some text to all of these "wanted categories" so they can be
properly indexed.
Is there a way to do this, or will I have to edit all these categories
manually?