WBCE CMS Forum

WBCE CMS – Way Better Content Editing.

You are not logged in.

#1 29.08.2021 21:30:14

losttrip
Member

oneforall topitems ordering by descending

OFA 1.1.6
I have "Item order" set to Descending in my General Settings.
Order items by position ascending or descending

This is great as it shows the most currently added items at the top of the pages.  These are Shop style pages.

I am now trying to use OneForAll TopItems to show the top items from all OFA sections on a different Main Overview Shop Page.
I am using the following in a Code2 section:

[== PHP ==]
oneforall_topitems(88, $num_of_items = 5)

But this shows the OLDEST item from each of my five OFA sections.  I want to show the NEWEST.  I know the the old AnyNews used to allow for an "order_by" in this call, and even the OneForAll AnyItems has this option:

[== PHP ==]
oneforall_anyitems($section_id, $num_of_items = 3, $order = true)

Where switching it to $order=false changes the order to Descending.

Aside question - I notice that it doesn't seem to matter what section ID I put in, but there seems to have to be a number put in.  Does it matter?

I've tried changing:

[== PHP ==]
// Query items
			$query_items = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_items WHERE active = '1' AND title != '' ORDER BY position, section_id".$limit);

to:

[== PHP ==]
// Query items
			$query_items = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_items WHERE active = '1' AND title != '' ORDER BY position DESC, section_id".$limit);

But this caused the output to show five items from a single OFA section, rather than one from each of the five different OFA sections.

I am not sure how to achieve this.


Here's the include.php code

[== PHP ==]
<?php

/*
  Snippet developed for the Open Source Content Management System Website Baker (http://websitebaker.org)
  Copyright (C) 2016, Christoph Marti

  LICENCE TERMS:
  This snippet is free software. You can redistribute it and/or modify it 
  under the terms of the GNU General Public License  - version 2 or later, 
  as published by the Free Software Foundation: http://www.gnu.org/licenses/gpl.html.

  DISCLAIMER:
  This snippet is distributed in the hope that it will be useful, 
  but WITHOUT ANY WARRANTY; without even the implied warranty of 
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
  GNU General Public License for more details.


  -----------------------------------------------------------------------------------------
   Code snippet OneForAll TopItems for Website Baker v2.6.x
  -----------------------------------------------------------------------------------------

*/



// Function to display featured events of the events module (invoke function from template or code page)
if (!function_exists('oneforall_topitems')) {
	function oneforall_topitems($section_id, $num_of_items = 3) {



		// ************************************************************
		// MAKE YOUR MODIFICATIONS TO THE LAYOUT OF THE ITEMS DISPLAYED
		// ************************************************************

		// Use this html for the layout
		$setting_header = '<div id="mod_oneforall_topitems_wrapper">
		<h2>OneForAll TopItems</h2>';

		$setting_item_loop = '<a href="[LINK]">
		<div class="mod_oneforall_topitems_item">
		<h3>[TITLE]</h3>
		<ul>
			<li class="mod_oneforall_topitems_field_1">[FIELD_1]</li>
			<li class="mod_oneforall_topitems_field_2">[FIELD_2]</li>
			<li class="mod_oneforall_topitems_field_3">[FIELD_3]</li>
		</ul>
		</div>
		</a>
		';
	
		$setting_footer = '</div>';
		// End layout html




		// **************************************************************************
		// DO NOT CHANGE ANYTHING BEYOND THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING
		// **************************************************************************

		global $wb, $database;
		$output = '';

		// Get module name corresponding with section id
		$mod_name = $database->get_one("SELECT module FROM ".TABLE_PREFIX."sections WHERE section_id = '$section_id'");

		if (empty($mod_name)) {
			$output = 'ERROR: No module found for section id '.$section_id.'.';
		}
		else {

			// Look for language file
			$module_name = $mod_name;
			if (LANGUAGE_LOADED && !isset($MOD_ONEFORALL[$mod_name])) {
				include(WB_PATH.'/languages/EN.php');
				if (file_exists(WB_PATH.'/languages/'.LANGUAGE.'.php')) {
					include(WB_PATH.'/languages/'.LANGUAGE.'.php');
				}
				include(WB_PATH.'/modules/'.$mod_name.'/languages/EN.php');
				if (file_exists(WB_PATH.'/modules/'.$mod_name.'/languages/'.LANGUAGE.'.php')) {
					include(WB_PATH.'/modules/'.$mod_name.'/languages/'.LANGUAGE.'.php');
				}
			}



			// GENERATE THE PLACEHOLDERS
			
			// Make array of general placeholders
			$general_placeholders = array('[PAGE_TITLE]', '[THUMB]', '[THUMBS]', '[IMAGE]', '[IMAGES]', '[TITLE]', '[ITEM_ID]', '[LINK]', '[DATE]', '[TIME]', '[USER_ID]', '[USERNAME]', '[DISPLAY_NAME]', '[USER_EMAIL]', '[TEXT_READ_MORE]', '[TXT_ITEM]');

			// Get the field placeholders
			$query_fields = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_fields");
			
			if ($query_fields->numRows() > 0) {
				while ($field = $query_fields->fetchRow()) {

					// Array with field placeholders
					$field_id                  = $field['field_id'];
					$field_placeholders_name[] = '['.strtoupper(stripslashes($field['name'])).']';
					$field_placeholders_num[]  = '[FIELD_'.$field_id.']';

					// Array with field types, label and templates
					$types[$field_id]     = $field['type'];
					$extra[$field_id]     = $field['extra'];
					$labels[$field_id]    = $field['label'];
					$templates[$field_id] = $field['template'];
				}
			}
			else {
				$field_placeholders_name = array();
				$field_placeholders_num  = array();
				$templates               = array();
			}


			// LOOP THROUGH AND SHOW ITEMS

			// Limit number of items
			$limit = is_numeric($num_of_items) ? " LIMIT ".$num_of_items : '';
			// Query items
			$query_items = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_items WHERE active = '1' AND title != '' ORDER BY position, section_id".$limit);

			// Get items
			if ($query_items->numRows() > 0) {
				while ($item = $query_items->fetchRow()) {
					$item_id = stripslashes($item['item_id']);
					$page_id = stripslashes($item['page_id']);
					$title   = htmlspecialchars(stripslashes($item['title']));
					$uid     = $item['modified_by']; // User who last modified the item
					// Workout date and time of last modified item
					$item_date = gmdate(DATE_FORMAT, $item['modified_when']+TIMEZONE);
					$item_time = gmdate(TIME_FORMAT, $item['modified_when']+TIMEZONE);
					// Work-out the item link
					$item_link = WB_URL.PAGES_DIRECTORY.get_page_link($page_id).$item['link'].PAGE_EXTENSION;

					// Get item fields data
					$query_item_fields = $database->query("SELECT field_id, value FROM ".TABLE_PREFIX."mod_".$mod_name."_item_fields WHERE item_id = ".$item_id);

					if ($query_item_fields->numRows() > 0) {
						while ($item_fields = $query_item_fields->fetchRow()) {

							$field_id          = $item_fields['field_id'];
							$values[$field_id] = trim(stripslashes($item_fields['value']));
							$unserialized      = @unserialize($values[$field_id]);

							// For textareas convert newline to <br>
							if ($types[$field_id] == 'textarea') {
								$values[$field_id] = nl2br($values[$field_id]);
							}

							// For wysiwyg replace [wblinkXX] by real link (XX = PAGE_ID)
							if ($types[$field_id] == 'wysiwyg') {
								$pattern = '/\[wblink(.+?)\]/s';
								preg_match_all($pattern, $values[$field_id], $ids);
								foreach ($ids[1] as $page_id) {
									$pattern = '/\[wblink'.$page_id.'\]/s';
									// Get page link
									$link              = $database->get_one("SELECT link FROM ".TABLE_PREFIX."pages WHERE page_id = '$page_id' LIMIT 1");
									$page_link         = page_link($link);
									$values[$field_id] = preg_replace($pattern, $page_link, $values[$field_id]);
								}
							}

							// For wb_link convert page_id to page link
							if ($types[$field_id] == 'wb_link' && is_numeric($values[$field_id])) {
								$link = $database->get_one("SELECT link FROM ".TABLE_PREFIX."pages WHERE page_id = '".$values[$field_id]."' LIMIT 1");
								$values[$field_id] = page_link($link);
							}

							// For media add WB_URL to the link
							if ($types[$field_id] == 'media' && !empty($values[$field_id])) {
								$values[$field_id] = WB_URL.MEDIA_DIRECTORY.$values[$field_id];
							}

							// If value is serialized, unserialize it and convert it to string
							if ($unserialized !== false || $values[$field_id] == 'b:0;') {
								// Filter empty values
								$array_size = count(array_filter($unserialized));
								if ($array_size > 0) {
									// For datepickers with start and end use "until" to separate the two dates
									if ($types[$field_id] == 'datepicker_start_end' || $types[$field_id] == 'datetimepicker_start_end') {
										$glue = ' '.$MOD_ONEFORALL[$mod_name]['TXT_DATEDATE_SEPARATOR'].' ';
									} else {
										$glue = ' ';
									}
									$values[$field_id] = implode($glue, $unserialized);
								} else {
									$values[$field_id] = '';
								}
							}

							// For droplet
							if ($types[$field_id] == 'droplet' && !empty($values[$field_id])) {
								// Get the droplet
								$droplet = $database->get_one("SELECT name FROM ".TABLE_PREFIX."mod_droplets WHERE active = 1 AND id = '".$values[$field_id]."' LIMIT 1");
								$values[$field_id] = '[['.$droplet.']]';
							}

							// For select
							if ($types[$field_id] == 'select' && !empty($values[$field_id])) {
								$index     = $values[$field_id] - 1;
								$a_options = explode(',', $extra[$field_id]);
								$values[$field_id] = $a_options[$index];
							}
						}
					}



					// ITEM THUMB(S) AND IMAGE(S)

					// Initialize or reset thumb(s) and image(s) befor laoding next item
					$thumb_arr = array();
					$image_arr = array();
					$thumb     = "";
					$image     = "";

					// Get image data from db
					$query_image = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_images WHERE `item_id` = '$item_id' AND `active` = '1' ORDER BY position ASC");
					if ($query_image->numRows() > 0) {
						while ($image = $query_image->fetchRow()) {
							$image       = array_map('stripslashes', $image);
							$image       = array_map('htmlspecialchars', $image);
							$img_id      = $image['img_id'];
							$image_file  = $image['filename'];
							$img_alt     = $image['alt'];
							$img_title   = $image['title'];
							$img_caption = $image['caption'];

							// Prepare thumb and image directory pathes and urls
							$thumb_dir = WB_PATH.MEDIA_DIRECTORY.'/'.$mod_name.'/thumbs/item'.$item_id.'/';
							$img_dir   = WB_PATH.MEDIA_DIRECTORY.'/'.$mod_name.'/images/item'.$item_id.'/';
							$thumb_url = WB_URL.MEDIA_DIRECTORY.'/'.$mod_name.'/thumbs/item'.$item_id.'/';
							$img_url   = WB_URL.MEDIA_DIRECTORY.'/'.$mod_name.'/images/item'.$item_id.'/';

							// Check if png image has a jpg thumb (version < 0.8 used jpg thumbs only)
							$thumb_file = $image_file;
							if (!file_exists($thumb_dir.$thumb_file)) {
								$thumb_file = str_replace('.png', '.jpg', $thumb_file);
							}

							// Make array of all item thumbs and images
							if (file_exists($thumb_dir.$thumb_file) && file_exists($img_dir.$image_file)) {
								$thumb_prepend = '<a href="'.$item_link.'"><img src="';
								$img_prepend   = '<img src="';
								$thumb_append  = '" alt="'.$img_alt.'" title="'.$img_title.'" class="mod_'.$mod_name.'_main_thumb_f" /></a>';
								$img_append    = '" alt="'.$img_alt.'" title="'.$img_title.'" class="mod_'.$mod_name.'_main_img_f" />';
							}
							// Make array
							$thumb_arr[] = $thumb_prepend.$thumb_url.$thumb_file.$thumb_append;
							$image_arr[] = $img_prepend.$img_url.$image_file.$img_append;
						}
					}
					// Main thumb/image (image position 1)
					$thumb = empty($thumb_arr[0]) ? '' : $thumb_arr[0];
					$image = empty($image_arr[0]) ? '' : $image_arr[0];
					unset($thumb_arr[0]);
					unset($image_arr[0]);

					// Make strings for use in the item templates
					$thumbs = implode("\n", $thumb_arr);
					$images = implode("\n", $image_arr);



					// REPLACE PLACEHOLDERS BY VALUES

					// Get user data
					if (empty($users[$uid]['username'])) {
						$uid                         = '';
						$users[$uid]['username']     = '';
						$users[$uid]['display_name'] = '';
						$users[$uid]['email']        = '';
					}

					// Make array of general values of current item
					$general_values = array(PAGE_TITLE, $thumb, $thumbs, $image, $images, $title, $item_id, $item_link, $item_date, $item_time, $uid, $users[$uid]['username'], $users[$uid]['display_name'], $users[$uid]['email'], $TEXT['READ_MORE'], $MOD_ONEFORALL[$mod_name]['TXT_ITEM']);

					// Replace placeholders in field templates by label and value
					$ready_templates = array();
					foreach ($templates as $field_id => $template) {

						// If value is empty return a blank template
						if (!isset($values[$field_id]) || empty($values[$field_id])) {
							$template = '';
						} else {
							$search   = array('[CUSTOM_LABEL]', '[CUSTOM_CONTENT]');
							$replace  = array($labels[$field_id], $values[$field_id]);
							$template = str_replace($search, $replace, $template);
						}

						// Array of templates with replaced placeholders
						$ready_templates[] = $template;
					}

					// Print item loop
					$search  = array_merge($general_placeholders, $field_placeholders_name, $field_placeholders_num);
					$replace = array_merge($general_values, $ready_templates, $ready_templates);
					$output .= trim(str_replace($search, $replace, $setting_item_loop));

					// Clear arrays for next item
					unset($values);
					unset($ready_templates);
				}
			}
		}

		// If we have one or more featured event(s), print them
		if (!empty($output)) {
			// Print header
			echo $setting_header;
			// Print event(s)
			echo $output;
			// Print footer
			echo $setting_footer;		
		}
	}
}
?>

topitems
Snippet to display the top positioned items of all OneForAll sections.

Offline

#2 24.04.2022 17:19:21

losttrip
Member

Re: oneforall topitems ordering by descending

I never found a solution for this...

I'll try to simplify my question:

WBCE Version: 1.5.0
PHP Version: 7.4.28
OFA 1.1.6
OneForAll TopItems 0.3

OFA General Settings "Item order" set to Descending.

I am trying to use the Helper "OneForAll TopItems" to find the NEWEST (most recently added) top position items from all the OFA sections of a website - and display them on a separate "Featured Items" Page.

I am using the following in a Code2 section on the "Featured Items" Page:

[== PHP ==]
oneforall_topitems(88, 6);

But this displays the OLDEST bottom position item from each of my six OFA sections:
Oldest item from section 88
Oldest item from section 89
Oldest item from section 90
Oldest item from section 91
Oldest item from section 92
Oldest item from section 93

I need to show the NEWEST (most recently added) top position item from each of these sections.

I've tried all the changes I can think of in the include.php file and nothing has worked.

Any assistance would be greatly appreciated.

Last edited by losttrip (01.05.2022 01:23:15)

Offline

#3 25.04.2022 09:25:52

florian
Administrator

Re: oneforall topitems ordering by descending

I think the issue is that the query sorts the items in ascending order, first by position, then by section. I don't know so much about mySQL, but maybe it helps if you alter the query

$query_items = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_items WHERE active = '1' AND title != '' ORDER BY position, section_id".$limit);

to

$query_items = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_items WHERE active = '1' AND title != '' ORDER BY `position` DESC ".$limit);

Last edited by florian (25.04.2022 09:26:04)

Offline

#4 25.04.2022 23:00:23

losttrip
Member

Re: oneforall topitems ordering by descending

Thank you for your time.  I apologize in advance for the length of this message.  I am trying to provide as much info as I can in hopes of improving the usefulness of this module.

The result I am after, is for the module to query each section and sort them from newest to oldest top position to bottom position, then display the newest 1 top position item from section 1, then the newest 1 top position item from section 2, and so on.

I had already tried:

$query_items = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_items WHERE active = '1' AND title != '' ORDER BY position DESC, section_id".$limit);

I tried your suggestion:

$query_items = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_".$mod_name."_items WHERE active = '1' AND title != '' ORDER BY `position` DESC ".$limit);

Both give the same result, which is unfortunately not the result I am looking to achieve, because of variations in the number of items in each section, which causes the sort by position to show more new top position items from sections with more items than sections with less items.

---

Before I get into more details, I am genuinely curious of a few things:

  1. the importance of adding the single back-tic around "position"

  2. the reason for using back-tic as opposed to single quote

  3. the rational for leaving out the "section_id" part of the query

---

Now, here are my finding and issues on further testing of oneforall_topitems ...

For reference, here are some example sections, their items, and the outcome using the default code, your suggested code, followed by what I am trying to achieve.

Page 1 Section 1:
item a3
item a2
item a1

Page 2 Section 2:
item b3
item b2  ( Inactive )
item b1

Page 3 Section 3:
item c4  ( newest added item from ALL sections )
item c3
item c2
item c1  (oldest added item compared to ALL sections - item_id = 1 and position=1  )

Page 4 Section 4:
item d6
item d5
item d4
item d3
item d2
item d1

----

Outcome on Page 5 using the default oneforall_topitems sorting code  (using code oneforall_topitems(4,10); ):
item a1
item b1
item c1
item d1
item a2
item c2
item d2
item a3
item b3
item c3

---

Outcome on Page 5 using the code change you suggested ( `position` DESC ) sorting code  (using code oneforall_topitems(4,10); ):
item d6
item d5
item c4  ( newest added item from ALL sections )
item d4
item a3
item b3
item c3
item d3
item a2
item c2

Although this code change does sort in descending order, since there are two more items in section_4 than section_3 and three more than in section_1 and section_2, these extra items from section_4 and Section_3 are shown before we ever see any section_1 or section_2 items.

----

My ultimate wish list desired outcome on Page 5, (using code oneforall_topitems(4,10); ):
item d6
item a3
item b3
item c4  ( newest added item from ALL sections )
item d5
item a2
item b1
item c3
item d4
item a1

This displays the first item from the section which the user chooses in the oneforall_topitems code for $section_id, and then proceeds from there.

---

But I would be more than satisfied with this outcome on Page 5, (using code oneforall_topitems(4,10); ):
item a3
item b3
item c4  ( newest added item from ALL sections )
item d6
item a2
item c3
item d5
item a1
item b1
item c2

---

NOTES:

It doesn't matter what section ID is enter in the code section (e.g., oneforall_topitems(88,6); or oneforall_topitems(92,6); ), as long as that section contains a valid oneforall module.  Using the default code, regardless of the section ID entered, the first item displayed will be the 'active' item from the database that has the lowest position AND lowest section_id .  I'm not saying this is WRONG, but it is confusing, especially if you consider the possibility that someone might use something like the following code:

oneforall_topitems(3,2); 

expecting to see the top position item from section 3, and the top position item from section 4, but instead what is displayed is a single item from section 1, and a single item from section 2, because sections 1 and 2 are also a oneforall sections.

---

ISSUES:

This module will display 'active' items from sections from pages with visibility is set to "private".
This module will display 'active' items from sections from pages which have been 'partially' deleted (single trash can).
This module will display 'active' items from sections with expired end dates set to them.

Though this module will not display items which are 'inactive', it will not in turn show the next 'active' item from that section, it will skip to the next section for the next item to display.  This is not ideal for my use case, but I understand that this is likely not really an issue, more of just a reality of the automation of the query.

Again, sorry for the long winded post.

Last edited by losttrip (01.05.2022 01:27:56)

Offline

#5 29.04.2022 07:40:36

florian
Administrator

Re: oneforall topitems ordering by descending

About the backticks: there are reserved words in mysql. If a table column has the same name, this will lead to errors depending on the mysql version/configuration. To avoid this, field names have to be set in backticks.
Using backticks for quotation is required by mysql (see https://dev.mysql.com/doc/refman/8.0/en/glossary.html => backticks). Other characters like ' or " probably won't work.

About your issues: change the main query to

$query_items = $database->query("SELECT * FROM 
`".TABLE_PREFIX."mod_".$mod_name."_items` 
WHERE `active` = '1' AND `title` != '' AND `section_id` = ".$section_id." 
ORDER BY `position` DESC ".$limit);

(line breaks only for readability, do not adapt them)

then a call like oneforall_topitems(89,2) will output the items which are on the top positions of the OFA section 89. These items are not mandatory the newest ones. To achieve this, change the query to

$query_items = $database->query("SELECT * FROM 
`".TABLE_PREFIX."mod_".$mod_name."_items` WHERE `active` = '1' AND `title` != '' AND 
`section_id` = ".$section_id." ORDER BY `item_id` DESC ".$limit);

By this, the items with the highest id from the given section will be displayed.

About the issues with active items on private/deleted pages and outdated sections: the query does not check the page or section state. The issue should not appear with one of the modified queries from this post, since these will display only items from the given section (unlike showing all items from all sections before).
Also, afaics inactive items will be skipped as expected and the next active item from a given section will appear.

Last edited by florian (29.04.2022 07:41:10)

Offline

Liked by:

losttrip

#6 29.04.2022 16:31:54

losttrip
Member

Re: oneforall topitems ordering by descending

Thank you for the info on backticks.

I need to choose my words more carefully.  YES, I want to display the items in the top positions, from certain page sections - NOT necessarily the newest items.

I appreciate your offered solution.  It is so close to what I am after, I can work with it, but...

Is there a way to allow for an array for the section id(s) in the call?

For instance, and I know this is not how it would be done:

oneforall_topitems(array(88,89,91,92])4); 

Where the module would display the single "top positioned" item from section, 88, 89, 91, and 92.

or:

oneforall_topitems([88,89,91,92],6); 

Where the module would display:
top position item section 88
top position item section 89
top position item section 90
top position item section 91
second from top position item section 88
second from top position item section 89

I truly appreciate your time, and if I am asking too much, please don't hesitate to say so, or drop me a PM.  Thank you.

Last edited by losttrip (01.05.2022 01:29:46)

Offline

Board footer

up