MODX :: [Snippet] loopDbChunk 1.01 - with pagination (update)

12

amirm Reply #1, 4 years, 9 months ago

Link to this post#1

This snippet is used for displaying data from a database while allowing to format each row with a chunk.
In addition, each database field's value can be transformed with a defined function. For example, a long text can be shortened, a date field can be formatted, etc.

The snippet will loop over a database table and for
each row will inject some (or all) of the fields into placeholders
contained in a chunk.

See documentation in the attached file readme.txt
Any feedback is welcome, including suggestions for a better name...

update (sep 04, 2007):

released a new version, 1.0 , with pagination support.

update (jan 29 200

:

released a new version, 1.1

update (may 28 200

:
released a new version, 1.2. Main change - fixes a bug when a placeholder exists more than once in a chunk

Amir

12,035

Ryan Thrash

MODX Co-Founder & CEO

MODX Revolution

Your Content, Your Way.

Issues | Documentation | Git the Source

Need help? Help us help you.

rthrash Reply #2, 4 years, 9 months ago

Link to this post#2

Very cool idea Amir.

7,075

- Former MODx evangelist

.: COO - Commerce Guys - Community Driven Innovation :.

MODx est l'outil id

davidm Reply #3, 4 years, 9 months ago

Link to this post#3

Indeed this will come in handy !

I'll give this a look and report

134

DangerMouse1981 Reply #4, 4 years, 9 months ago

Link to this post#4

This looks great! - this is definately a missing link in Ditto from my point of view. The manager bloat that comes with creating huge numbers of documents in Modx means that a custom db that is used to populate a page on the fly is more efficient.

How difficult would it be to include the pagnation functions that Ditto has?

Will definately be playing with this, hope to help out with any testing.

Cheers,

DM

12

amirm Reply #5, 4 years, 9 months ago

Link to this post#5

I'm glad you find it useful

Right now the loopDbChunk is quite a simple script - not anywhere near the complexity of ditto.
Pagination is definitely on my list - I will work on it when I find some time...

66

bkvernst Reply #6, 4 years, 5 months ago

Link to this post#6

This snippet is just what I need, Amir! Thanks a lot

Now, I want to fetch the data from a different database, and was looking through the code to find the relevant MySQL query. As I didn't find it, could you please tell me how to do this magic? My table 'users' is in the database 'profiles', and not the MODx database

I guess it should be some place within this function:

	function renderChunks() 
	{
		global $modx;
		$query = '';
		$rs = '';
		$output = '';
		
		if($this->sql != '') {
			$query = $this->sql;
		}
		else {
			$fields = implode(',', $this->placeholders);
			$query = 'SELECT ' . $fields . ' FROM ' . $this->tableName;
			if ($this->orderby != '') {
				$query .= ' ORDER BY ' . $this->orderby;
			}
			if	 ($this->limit != '') {
				$query .= ' LIMIT ' . $this->limit;
			}	
		}				

		$rs = $modx->db->query($query);
		$chunk = $modx->getChunk($this->chunkName);

		while( $row = $modx->db->getRow($rs) ) {
			$output .= $this->getChunkReplaced($row, $chunk);			
		}
		
		return($output);
	}
}

2,542

Made with MODx : copadel, fruits et l

heliotrope Reply #7, 4 years, 5 months ago

Link to this post#7

Hi,

the parameters are viewable in the snippet code

 *		parameters:
 *			tableName	- 	the relevant table name
 *			chunkName 	- 	the name of the chunk to format the data 
 *			orderby 		- 	a database field name to order the results by.
 *			limit 		- 	number or 'rows' to display 
 *			sql			-	an optional sql query for the the result lines

so you just have to specify the value you need in your snippet code

&tableName=`my_table`

Hope that helps

:-)

4,205

Shane Sponagle | Snippet Call Anatomy | MODx Developer Blog | Working With a Content Management Framework: MODx

Something is happening here, but you don't know what it is.
Do you, Mr. Jones? -

dev_cw Reply #8, 4 years, 5 months ago

Link to this post#8

If I understood correctky I think you need a connect script to connect to the other databes. You may need to edit the snippet to add the ability to connect to another DB. Maybe look at this wiki post:
http://wiki.modxcms.com/index.php/Access_another_database_from_MODx

66

bkvernst Reply #9, 4 years, 5 months ago

Link to this post#9

Thanks guys, your link was right on, dev_cw

I'll try to alter the code, it should be fairly simple with DBAPI... I'll get back to you with the results!

66

bkvernst Reply #10, 4 years, 5 months ago

Link to this post#10

Okay, I managed to get this working with a different database, using DBAPI connect() instead. I wasn't able to use DBAPI select(), because it requires me to enter the table name (databasename.tablename), which also has to be done in the query ('SELECT ' . $fields . ' FROM ' . $this->tableName;). If I enter the tablename in both select() and query(), it fails...

Since I'm using connect() in this script, it will always connect to the database when I'm doing a query. Will this cause a heavy load on the server, and hows the safety? Are there any other methods that could do this job better?

Thanks again, guys! I'm really a designer more than a coder, but got stuck here doing this coding job... I'm learning though!

Here's the new code:

	function renderChunks() 
	{
		global $modx;
		$query = '';
		$rs = '';
		$output = '';
		
		if($this->sql != '') {
			$query = $this->sql;
		}
		else {
			$fields = implode(',', $this->placeholders);
			$query = 'SELECT ' . $fields . ' FROM ' . $this->tableName;
			if ($this->orderby != '') {
				$query .= ' ORDER BY ' . $this->orderby;
			}
			if	 ($this->limit != '') {
				$query .= ' LIMIT ' . $this->limit;
			}	
		}				
		
		// NEW CODE STARTS HERE
		$rs = $modx->db->connect('localhost', '*** database ***', '*** username ***', '*** password ***', true);
		// NEW CODE ENDS HERE
		$rs = $modx->db->query($query);
		$chunk = $modx->getChunk($this->chunkName);

		while( $row = $modx->db->getRow($rs) ) {
			$output .= $this->getChunkReplaced($row, $chunk);			
		}
		
		return($output);
	}