From MIGXDB How to Filter Data With PdoResources To Show Certain Current Date Data?

115 Posts

jimmyjazz Reply #1, 10 years, 6 months ago

So far I've set up MIGXDB and its CMP, I've added some data covering 4 dates and this data is confirmed present in the mySql table.
This is the schema I used to populate the table:

<?xml version="1.0" encoding="UTF-8"?>
<model package="datequote" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    <object class="DateQuote" table="datequote" extends="xPDOSimpleObject">
	  	<field key="quote" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
		<field key="author" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
    	<field key="topic" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>        
		<field key="birthday" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
		<field key="month" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>		
		<field key="date" dbtype="int" precision="10" phptype="string" null="false" default=""/>
		<field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
    <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
    <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
    <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="deletedon" dbtype="datetime" phptype="datetime" null="false" />
		<field key="deletedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
    <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />  
		<field key="publishedon" dbtype="datetime" phptype="datetime" null="false" />
		<field key="publishedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
    <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
    <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>
    </object>
</model>

2 Problems:
1. filtering the data
2. getting it to display properly in the front end

I created a basic call with migxLoopCollection to test the output and it is filtering the data ok (for this call) but it is not displaying it correctly, it only shows code:

[[!migxLoopCollection?
&packageName=`datequote`
&classname=`DateQuote`
&selectfields=`id,quote,author,topic,birthday`
&where=`{"date":"11"}`
&toJsonPlaceholder=`json` 
]]
[[+json]]

The fields I created (&selectfields) are the only fields I want to display front end.

I added the fields 'month' & 'date' for filtering only.

What I want to do is show different quotations every day that are dependent on the current date.....For example:

So far I've populated it with data for 9th April, 10th April, 11th April & 12th April only. (for testing, i want to populate it for 365 days eventually if it can work).

What code do I need to use to display the following?:

Quote
Author
Topic
Birthday

The list must be in that order.

fyi......
The value for 'month' is April
The value for 'date' is 9, 10, 11 or 12
The value for 'birthday' is 9th April 1972 or 9th April 1951 or 10th April 1932.....etc

I kind of figured that an easy way to display the required data for every current date would be a query that must include April for month and 9 for date if current date is 9th April.....a query that must include April for month and 10 for date if current date is 10th April.......and so on for every month and current daily date.

The example migxLoopCollection is not working, however I would prefer to use PdoResources but I cannot get any data to display frontend with any of the PdoResources snippets I've tried.

What is the correct PdoResources call to display the required data on the required day?

This question has been answered by Bruno17. See the first response.

discuss.answer

5,888 Posts

Bruno17 Reply #2, 10 years, 6 months ago

you will need something like that:

[[!migxLoopCollection?
&packageName=`datequote`
&classname=`DateQuote`
&selectfields=`id,quote,author,topic,birthday`
&where=`{"date":"[[!now:date=`%d`]]","month":"[[!now:date=`%B`]]"}`
&tpl=`myTpl`
]]

the chunk myTpl:

[[+quote]]<br>
[[+author]]<br>
[[+topic]]<br>
[[+birthday]]<br>
<hr>

snippet now:

return time();

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

115 Posts

jimmyjazz Reply #3, 10 years, 6 months ago

Thank you so much Bruno, that worked perfectly

Just wondering, is there any limitation how many quotes I can load into MIGXDB?
I plan to load 1000+ covering every day of the year and I'm thinking this should be no issue.

btw........MIGXDB with CMP really rocks, one of the best things about Modx

189 Posts

gissirob Reply #4, 10 years, 6 months ago

I won't remind you that there are 365 days in a year (ok, 366 this year).

To answer your question, thousands should not be a problem. But, if it does start to slow down, you could try adding indexes to the date and month columns.

115 Posts

jimmyjazz Reply #5, 10 years, 6 months ago

Quote from: gissirob at Apr 11, 2016, 11:53 AM

I won't remind you that there are 365 days in a year (ok, 366 this year).

To answer your question, thousands should not be a problem. But, if it does start to slow down, you could try adding indexes to the date and month columns.

Hi gissirob,

Leap year, yes

Thanks for the indexes suggestion.

Answered From MIGXDB How to Filter Data With PdoResources To Show Certain Current Date Data?