Please don't tell me I spent over $300 on these products and I cannot use a selection sql statement like:

SELECT
lms_indv_goal.ID as goalID,
lms_indv_goal.DHS_WRKR_PARTY_ID,
lms_indv_goal.CD_LMS_CURR_PRFCNY_ID,
curr_prfcny.SHRT_DESC as Current,
des_prfcny.SHRT_DESC as Desired,
lms_indv_goal.GOAL_NM,
lms_indv_goal.GOAL_NMBR,
cd_lms_cmptncy.SHRT_DESC as Competency,
lms_indv_goal.GOAL_EFF_DT,
lms_indv_goal.GOAL_TRGT_DT,
lms_indv_goal.GOAL_ACHV_DT,
cd_lms_goal_stts.SHRT_DESC,
lms_indv_goal.CD_LMS_GOAL_STTS_ID
FROM lms_indv_goal INNER JOIN cd_lms_cmptncy ON lms_indv_goal.CD_LMS_CMPTNCY_ID = cd_lms_cmptncy.ID
INNER JOIN cd_lms_prfcny curr_prfcny ON lms_indv_goal.CD_LMS_CURR_PRFCNY_ID = curr_prfcny.ID
INNER JOIN cd_lms_prfcny des_prfcny ON lms_indv_goal.CD_LMS_DESRD_PRFCNY_ID = des_prfcny.ID
INNER JOIN cd_lms_goal_stts ON lms_indv_goal.CD_LMS_GOAL_STTS_ID = cd_lms_goal_stts.ID"

Instead I have to BUILD it using your rather cumbersome build query

Hello,

Unfortunately there is no way to enter any sql statements in the query builder. The query builder is a powerful tool that lets you build any query fully visual.

How do I use the Database Connector's Database Source Query Builder to create this SQL statement:

SELECT ID, StartDate, Headline, SUBSTRING_INDEX(NewsArticle, ' ', 10) AS Summary
FROM breakingnews
WHERE CurDate() >= StartDate AND CurDate() <= EndDate
ORDER BY StartDate DESC

I am clueless how to build this query using the features available in your Database Source Query Builder.

Hello,
Please describe what do you need to achieve as a result?

The MySQL database stores a Start Date, End Date, Headline, and an Article in each record. The results of the query I described should display only an Article where the Start Date is => to today's date AND <= the End Date. The display should show only the first 10 words of each filtered article as "Summary". A "Repeat" region should display only the relevant query-filtered summaries... A "Detail" button located in the "Repeat" region will direct the user to the webpage that displays the related entire article.

Hope this helps to describe what I need. The query statement I sent earlier does this very nicely using the deprecated DWCS6 PHP recordset server behavior.

Randy

Hello Randy,
Here you can do this really easy just clicking a few buttons:
1. Add the columns you want to use on the page inside the Select tab:

Full Image
2. In the Filters tab add:
- StartDate <= Entered Value: now
- EndDate >= Entered Value: now
or whatever your conditions need to be. "now" is used for now/today/current day.

Full Image
3. Create the repeat region on your page and add the Data Element you want to show inside it {{Article}} in your example. Click the DMXzone Database Formattter and select the Truncate Filter. Set how many characters you want to show and click OK.

Full Image
4. Add your button inside the repeat region and link to the full article page, for example: article.php?id={{id}} where {{id}} is the article id from your data source.

Thanks for the tutorial... worked like a charm! i just purchased the Formatter extension to do the truncate - very slick! This old dog is slowly unlearning old DW tricks and learning new DMXzone tricks!