7 replies to this topic

[PaulD]

This function is used to query any table on the etomite database. For tables on other databases you need getExtTableRows.

From the documentation.

getIntTableRows($fields="*", $from="", $where="", $sort="", $dir="ASC", $limit="", $push=true)

// function to get rows from ANY internal database table
// This function works much the same as the getDocuments() function. The main differences are that it will accept a table name and can use a LIMIT clause.
// $fields = a comma delimited string: $fields="name,email,age"
// $from = name of the internal Etomite table which data will be selected from without database name or table prefix ($from="user_messages")
// $where = any optional WHERE clause: $where="parent=10 AND published=1 AND type='document'"
// $sort = field you wish to sort by: $sort="id"
// $dir = ASCending or DESCending sort order
// $limit = maximum results returned: $limit="3" or $limit="10,3"
// $push = ( true = [default] array_push results into a multi-demensional array | false = return MySQL resultset )
// Returns FALSE on failure.

Example usage
Here I have used it to query the internal table user_attributes, to return the name and phone number of the user.

If you use a prefix on your database, it is automatically added, so dont worry about prefixes. If however you have created your own table on the etomite database, it still counts as an internal table. Just make sure that your new table has the same prefix as the etomite tables. For instance if all the etomite tables start with etomite_ then make sure your new table does too, for getIntTableRows to work.

// returns the internalKey for the current session. Assumes logged in.
$session = $_SESSION['internalKey'];

// uses getIntTableRows to access the user attributes table from within the etomite install
$userdetails = $etomite->getIntTableRows($fields="*", $from="user_attributes", $where="internalKey=$session", $sort="", $dir="ASC", $limit="1", $push=true);

// output some fields from the table
$output .= 'Your real name: '.$userdetails[0]['fullname'];
$output .= 'Your phone: '.$userdetails[0]['phone'];

return $output;

I have used the $fields="*" which returns all the fields. I have also set the $limit to return only one record. The row should be uniquely identified by my $where clause.

Paul.

[PaulD]

Looking at a snippet by Cris.D I noticed this:

$internalKey= $etomite-> getIntTableRows($fields="id", 
										 $from="manager_users", 
										 $where="`username`= '".$retrieveTempUserDetails[0]['userName']."'", 
										 $sort="id", 
										 $dir="ASC", 
										 $limit="1", 
										 $push=true, 
										 $addPrefix=true);

It appears that you can drop the etomite table prefix if you choose to with a $addPrefix flag set to false.

$addPrefix=true adds the prefix,
$addPrefix=false does not add prefix.

Paul.

PS Wish I knew that before I renamed all the bloody tables :-)

[PaulD]

Wow - Look what I found! On Ralphs development site. Great example and notes on the getIntTableRows function, plus using it with getChunk().

// assign a document id as a starting point (should be a folder document)

$id = isset($id) ? $id : $etomite->parents[$etomite->documentIdentifier];

// query database using getIntTableRows()
$rs = $etomite->
  getIntTableRows(
	// list of fields to return in the result set
	$fields=
	"
	  id, 
	  type, 
	  pagetitle, 
	  longtitle, 
	  description, 
	  alias, 
	  content, 
	  published, 
	  parent, 
	  menuindex
	",

	// the database table we are accessing
	$from="etomite_site_content",

	// data abstraction criteria (MySQL WHERE clause)
	$where=
	"
	  `parent`=$id 
	  AND `type`='document' 
	  AND `published`=1 
	  AND `deleted`=0
	",

	// data column to sort by
	// can be used for compound sorts if $dir is set to NULL
	$sort="editedon",

	// sorting direction ["ASC"|"DESC"|NULL]
	$dir="DESC",

	// maximum number of rows to return in the result set
	$limit="5",

	// convert the MySQL result set into a multi-dimensions array
	$push=true
  );

// assign the name of the chunk holding our display template code
$chunkName="MyTpl";

// load the $tpl variable with chunk-based template code
$tpl = $etomite->getChunk($chunkName);

// example of using the mergeCodeVariables API function
$output .= $etomite->
  mergeCodeVariables(
	$content=$tpl,
	$rs,
	$prefix="{",
	$suffix="}",
	$oddStyle="class=\"odd\"",
	$evenStyle="class=\"even\"",
	$tag="div"
  );

return $output;

I copied it all because the use of chunks is brilliant. I never knew it was so easy to do this! I have never used getChunk but will give it a go now!

Paul

[Ralph]

There are lots of "Easter Eggs" hidden within Etomite, PaulD...

While I don't have time to post it here now, there is now an alternative to using mergeCodeVariables() with getChunk()... By adding looping capabilities to parseChunk() you can do with task much easier as long as all data has been properly manipulated before calling parseChunk()... It's also described on my site... Probably best left to be covered in the parseChunk() function thread if it already exists...

[PaulD]

Suppose I did a query like this below, which returns all the rows of a products table that belong to the user.

// returns the internalKey for the current session. Assumes logged in.
$session = $_SESSION['internalKey'];

$products = $etomite->getIntTableRows($fields="*", 
			$from="DN_inventory", 
			$where="user_key=$session", 
			$sort="", 
			$dir="ASC", 
			$limit="50", 
			$push=true);

This time there could be 50 result rows. To access them I use a foreach.

foreach ($prods as $prod) {
		$output.='Product Name: '.$prod['prod_name'];
		$output.='Product Description: '.$prod['prod_desc'];
	}

return $output;

The prod_name and prod_desc are just the names of a columns on my table. I can access all the columns for each product in the same way. (Read more about 'for each' on php.net)

I hope this helps someone. I had been using an ugly if loop with a counter. 'For each' is so much cleaner and easier to read.

Edited by PaulD, 25 February 2009 - 10:01 PM.

[Ralph]

That's correct, PaulD... However, in a production environment you need to do a check to assure that the resultset isn't empty as foreach() will throw an error if no data is present for the mandatory first iteration... There are several methods of doing this...

// returns the internalKey for the current session. Assumes logged in.
 $session = $_SESSION['internalKey'];
	 
  if($products = $etomite->getIntTableRows(
	$fields="*", 
	$from="DN_inventory", 
	$where="user_key=$session", 
	$sort="", 
	$dir="ASC", 
	$limit="50", 
	$push=true)
  )
  {
	foreach ($products as $prod)
	{
	  $output.='Product Name: '.$prod['prod_name'];
	  $output.='Product Description: '.$prod['prod_desc'];
	}
  }
	 
  return $output;

OR

// returns the internalKey for the current session. Assumes logged in.
  $session = $_SESSION['internalKey'];
	 
  $products = $etomite->getIntTableRows(
	$fields="*", 
	$from="DN_inventory", 
	$where="user_key=$session", 
	$sort="", 
	$dir="ASC", 
	$limit="50", 
	$push=true
  );
	  
  if($products)
  {
	 foreach ($products as $prod)
	{
		$output.='Product Name: '.$prod['prod_name'];
		$output.='Product Description: '.$prod['prod_desc'];
	 }
  }
	  
  return $output;

These both work because getIntTableRows() returns false on empty resultset...

An alternative, using while() and with $push=false would look something like:

// returns the internalKey for the current session. Assumes logged in.
 $session = $_SESSION['internalKey'];
   
 $products = $etomite->getIntTableRows
  (
	$fields="*", 
	$from="DN_inventory", 
	$where="user_key=$session", 
	$sort="", 
	$dir="ASC", 
	$limit="50", 
	$push=false
  );
 
 while($prod = $etomite->fetchRow($products))
  {
	$output.='Product Name: '.$prod['prod_name'];
	$output.='Product Description: '.$prod['prod_desc'];
 }
	
 return $output;

In this example, if the query returns no results, the while() loop fails out the bottom gracefully..

Can't have enough options...

[PaulD]

Thanks for that Ralph,

The while loop looked the best (IMHO) but I was worried about the push=false (as am much more comfortable with PHP arrays than mysql result sets).

I tried the while loop, but in my snipet, it was a loop that would be recalled. So I tried resetting it, but that caused a problem because of the push=false of course. Anyway, after lots of playing, I went back to a nested foreach with push=true.

The mandatory first loop in foreach is a problem actually and I have started adding in that check. Thank you for that. It would have been a real headache when it started failing.

I love the fact that a while loop doesnt need that first pass, and how neat it is to read. But how would I use the while loop with push=false to access the data? Perhaps I need to read up a bit on MySql result sets.

Paul.

[PaulD]

On layout.

For clarity of reading, and for speed and ease of cutting and pasting wherever I need it, I have started writing getIntTableRows function calls like this:

// get data
	$fields   =   "*";
	$from	=   "my-table";
	$where  =   "field_name=".$my-variable;	
	$sort	 =   "";
	$dir	   =   "ASC";
	$limit	 =   "1";
	$push	=   true;
$results = $etomite->getIntTableRows($fields, $from, $where, $sort, $dir, $limit, $push);

(This looks much nicer when the tabs line the columns up properly)

The function call line itself never changes. Plus this is easy to cut and paste and modify when I need to get data from a table.

Paul.

Edited by PaulD, 28 February 2009 - 03:08 AM.