[Snippet] loopDbChunk 1.01 - with pagination (update)

12 Posts

amirm Reply #1, 19 years, 4 months ago

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 2008):

released a new version, 1.1

update (may 28 2008):
released a new version, 1.2. Main change - fixes a bug when a placeholder exists more than once in a chunk

Amir

MODX Staff
12,272 Posts

Ryan Thrash Reply #2, 19 years, 4 months ago

Very cool idea Amir.

Ryan Thrash, MODX Co-Founder
Follow me on Twitter at @rthrash or catch my occasional unofficial thoughts at thrash.me

7,075 Posts

davidm Reply #3, 19 years, 4 months ago

Indeed this will come in handy !

I’ll give this a look and report

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

MODx est l'outil id

157 Posts

DangerMouse1981 Reply #4, 19 years, 4 months ago

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 Posts

amirm Reply #5, 19 years, 4 months ago

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 Posts

bkvernst Reply #6, 19 years, 1 month ago

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 huh

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 Posts

heliotrope Reply #7, 19 years, 1 month ago

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

:-)

Made with MODx : [url=http://www.copadel.com]copadel, fruits et l

4,205 Posts

dev_cw Reply #8, 19 years, 1 month ago

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

[font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

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

66 Posts

bkvernst Reply #9, 19 years, 1 month ago

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 Posts

bkvernst Reply #10, 19 years, 1 month ago

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! tongue

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);
	}