We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Filtering Question - Is this possible?

    • 10226
    • 412 Posts
    fruitwerks Reply #1, 18 years ago
    I wanted to take jot results and filter them somehow. Here is what I am thinking;

    before I start I want to give a little background. In my application of jot only the date field is required to make a comment, and it is not even a true date field. The date is entered as MMDDYYY - it is a text field.

    I wanted to parse the comments until it finds any chunk of data in another field (lets call it "fed") and return the ’date’ from that entry. Since the fed field is not always entered, this would most likely not be the most recent entry. This is why I am asking if this is possible.

    Moving forward, I would like to take that date and calculate the days since the last entry where the fed field contains data.

    Any ideas would be appreciated!
      • 1122
      • 209 Posts
      Alik Reply #2, 17 years, 10 months ago
      I guess that "fed", "date" are custom fields.
      $query = "
SELECT id, title, and-anything-you-need,
  (SELECT content FROM modx_jot_fields WHERE label = 'fed' AND modx_jot_fields.id = modx_jot_content.id) AS fed,
  (SELECT content FROM modx_jot_fields WHERE label = 'date' AND modx_jot_fields.id = modx_jot_content.id) AS date
FROM modx_jot_content
WHERE published = 1 AND fed <> ''
ORDER BY createdon DESC
LIMIT 0, 1
";

if (($result = mysql_query($query)) && mysql_num_rows($result)) {
  # yeah! we got a most recent comment with non-empty fed-field
  $row = mysql_fetch_assoc($result);
  mysql_freeresult($result);
  # now do desired calculation with your custom date in $row['date']
  ...
}

      SQL query uses the subquery feature so it requires MySQL 4.1 or later server (if I am not wrong). Sorry if my code contains any syntactic errors (lacking comma or so; I have not tested it), but the idea should be correct.
        • 33992 ☆ A M B ☆
        • 455 Posts
        AHHP Reply #3, 17 years, 10 months ago
        You can check the value of a field by PHx too....

        creating comment tpl like below may help:
        [+comment.custom.fed:ifempty=``:then=``:else=`      your comment tpl        `:strip+]


        if this PHx work (i didn’t test it), it will prints only comments that their fed field has value.
          God loves me. 【ツ】

          MODX.ir (Persian Support)

          Boplo.ir/modx/ (Persian)
          • 10226
          • 412 Posts
          fruitwerks Reply #4, 17 years, 10 months ago
          been a while, but thanks for the input. before I start working on these, I need to figure out the date management... I think someone helped me out with that earlier but I forgot about it and I’m off to find that right now.
            • 10226
            • 412 Posts
            fruitwerks Reply #5, 17 years, 7 months ago 
            $query = "
SELECT id, title, and-anything-you-need,
  (SELECT content FROM modx_jot_fields WHERE label = 'fed' AND modx_jot_fields.id = modx_jot_content.id) AS fed,
  (SELECT content FROM modx_jot_fields WHERE label = 'date' AND modx_jot_fields.id = modx_jot_content.id) AS date
FROM modx_jot_content
WHERE published = 1 AND fed <> ''
ORDER BY createdon DESC
LIMIT 0, 1
";

if (($result = mysql_query($query)) && mysql_num_rows($result)) {
  # yeah! we got a most recent comment with non-empty fed-field
  $row = mysql_fetch_assoc($result);
  mysql_freeresult($result);
  # now do desired calculation with your custom date in $row['date']
  ...
}


            This just got a lot easier! Since records are being entered properly and in order, the default createdon date for jot is what we need. I simply need to find the first non-empty for the meal field and subtract it from - well this is the tricky part, I have a TV ff. ff holds the number of days between feedings (as a number of course). So take createdon+ff-now? not sure how that would go down...

            Thanks!