Where Clause at Data Projects Page Level

  • Resolved jcmplastics

    (@jcmplastics)


    5 years, 10 months ago

    Hi Peter,

    Adding a where clause, assuming this is the format to use (Client_Aproved = ‘Approved’) produces no output of data – no where clause produces a complete record set. On the Front-End Page, Data Projects page level formatting is the only thing that seems to have any result without creating a child theme and doing extensive mods.

    I did create a view at the MyPhpAdmin level, but it won’t allow edits to views in the plugin.

    Does the where clause work, or am I using the wrong statement?

    I’m tyring to utilize this plugin as much as possible to avoid excessive back-end programming time for this project..

    Any Help is appreciated.

    – Jack

    • This topic was modified 5 years, 10 months ago by jcmplastics.
Viewing 12 replies - 1 through 12 (of 12 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Jack,

    The where clause works and your format is correct. Where did you add the where clause?

    Just to make sure, this is about a Data Project, not a publication, right? In Data Projects you can define where clauses on two levels: the parent table and the child table. If you want to add a where clause to a parent table, you need to enter it in the project page. For the child table you can use an option set. The where clause in an option set does NOT affect the parent table!

    If you don’t have a relationship (just a plain table page) you have to add your where clause to the project page.

    Does this help?

    Best regards,
    Peter

    Thread Starter jcmplastics

    (@jcmplastics)

    5 years, 10 months ago

    Hi Peter,

    I added it to the Project Page with the typical where clause shown in the original post, and it resulted in an empty recordset. It is a single table with no relationships. I’m trying to use one Data Project for Approved viewing and editing for the client, and another project with all data for Admin use to not allow records for viewing unless the Approved status is set.

    I have the same issue with the order by clause..

    – Jack

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Jack,

    >>> I have the same issue with the order by clause..

    Now I wonder… That page has no order by clause… Only a where clause. Just to be sure to add your where clause at the right place:
    – Start Data Projects
    – Edit your project (this will show all pages for your project)
    – Edit your project page
    – Enter your where clause

    Don NOT use the Manage Table Options tab for this setting. That where clause is meant for child tables only.

    PLease let me know if this helps…

    Best regards,
    Peter

    Thread Starter jcmplastics

    (@jcmplastics)

    5 years, 10 months ago

    Hi Peter,

    I went to:
    Wp Data Access > Data Projects
    Under Manage Projects > Edit Proj 1
    Pages > Edit Page 1
    Enter Where Clause at the Bottom
    Result: Empty Dataset

    I even tried at the front-end page level for editing with:
    [wpdadiehard project_id=”1″ page_id=”1″ filter_field_name=”Client_Approved” filter_field_value=”approved”]
    Result: Full dataset result and “Pending” record appears in the recordset.

    However, using the following at the front-end page level, I get a proper result with only approved records – but no editing capabilities:
    [wpdataaccess pub_id=”1″ table=”Wp_Client_CRM” filter_field_name=”Client_Lead_Approved” filter_field_value=”approved” sql_orderby=”Entry_Date,desc”]
    Note: I get no order by result – not sure if statement is properly coded for the result.

    – Jack

    • This reply was modified 5 years, 10 months ago by jcmplastics.
    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Jack,

    You are using different conditions for your project and publication:
    Project = Client_Approved='approved'
    Publication = Client_Lead_Approved='approved'

    Could that be the problem?

    Best regards,
    Peter

    Thread Starter jcmplastics

    (@jcmplastics)

    5 years, 10 months ago

    I must have “fat fingered” the first one when I pasted it somehow, because that’s not what I had on the page shortcode…

    Bad result – shows “pending”
    [wpdadiehard project_id=”1″ page_id=”1″ filter_field_name=”Client_Lead_Approved” filter_field_value=’approved’]

    Good Result – only “approved”
    [wpdataaccess pub_id=”1″ table=”Wp_Client_CRM” filter_field_name=”Client_Lead_Approved” filter_field_value=’approved’ sql_orderby=”Entry_Date,desc”]

    – Jack

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Jack,

    Do you get the data you expect when you use this shortcode:
    [wpdadiehard project_id="1" page_id="1"]

    Can you add some screenshots?

    Thanks,
    Peter

    Thread Starter jcmplastics

    (@jcmplastics)

    5 years, 10 months ago

    I can’t show you the result set screenshot because it has some client data (PID data), but I can get a full result recordset using the above code you are using, and I also get a full result using the shortcode I posted, just doesn’t implement the where clause.

    If I add the Where clause at the page level, I get an “empty” recordset on both the front-end and back-end.

    Maybe I can post some screen shots with the PID data cropped off…

    – Jack

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Hi Jack,

    Is it possible to send me (part of) the data? You can you the contactform on the plugin website to send it in private.

    Thanks,
    Peter

    Thread Starter jcmplastics

    (@jcmplastics)

    5 years, 10 months ago

    Sure…

    – Jack

    Thread Starter jcmplastics

    (@jcmplastics)

    5 years, 10 months ago

    I wanted to do a follow-up.

    Peter has been so helpful and responsive on any technical issues I’ve had – he’s a rare breed and truly appreciate all his help.

    Result and Resolution:
    1. The Where clause does not work on “parent” pages – Only on Child pages.
    2. Parent/Child pages were created and on page 2 (the child page) where clause was entered and worked.
    3. At front-end web page level using the “[wpdataaccess “”] shortcode (Viewing only) – the where statement codes work fine.
    4. At front-end web page level using the “[wpdadiehard “”] shortcode (Edit Mode) – the where statement codes are not working. Peter said he will fix and next release.

    Hope this helps.

    A huge thanks to Peter for his commitment and dedication to this project! He’s an Awesome person.

    I’m going to leave this open for now in case Peter wants to make any final comments before closing…

    – Jack

    • This reply was modified 5 years, 10 months ago by jcmplastics.
    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 10 months ago

    Great it works Jack! 🙂

    Thank you for your compliments and your trust in me. I’ll fix the issue with the wpdadiehard filter asap…

    Best regards,
    Peter

Viewing 12 replies - 1 through 12 (of 12 total)

The topic ‘Where Clause at Data Projects Page Level’ is closed to new replies.