{{indexmenu_n>300}}

====== DB query using the funct. "_dbQuery" ======

FIXME Better TRANSLATE

Here's a small example of how the built-in function _dbQuery ** ** may be used in a script. 

<code php>function _dbQuery($query='', $_queryMode='ASSOC') { ....... } </code>
\\
**$_queryMode:** 

  * INSERT, DELETE, UPDATE 
  * ON_DUPLICATE //(INSERT ... ON DUPLICATE KEY)//
  * ROW, ARRAY   //(SELECT Queries)  // 
  * COUNT        //(Count)//
  * COUNT_SHOW   //(send SHOW query and count results)//
  * ASSOC        //(default)//

\\

===== Example: =====

**Adoption:** From a given table //(exemplarily "phpwcms_country")// data should be selected and displayed in a CP. \\
Of course  the table can also be a self-created.

The script is stored in the folder /frontend_render/ . The data are here collected from the database, selected and placed into a usable format, here <table>. \\
With a replacement tag is the result shown in a CP. \\


\\
===== Adjusted Table Excerpt: =====

^country_id  ^ country_iso ^ country_iso3 ^ country_isonum ^ country_continent_code ^ country_name                     ^  country_continent ^  
| 1          | AF          |  AFG         |   4            |    AS                  | Afghanistan, Islamic Republic of |  Asia              |
| 2          | AL          |  ALB         |   8            |    EU                  | Albania, Republic of             |  Asia              |
| 3          | DZ          |  DZA         |  12            |    AF                  | Algeria, People\'s Democratic Republic of  |  Africa  |
| 4          | AS          |  ASM         |  16            |    OC                  | American Samoa                   |  Oceania           |
| 5          | AD          |  AND         |  20            |    EU                  | Andorra, Principality of         |  Europe            |
| 6          | ...         |  ...         |  ...           |    ...                 | ...                              |  ...               |


\\

  * **Replacer in CMS:** {DB-QUERY}
  * **Table name:** phpwcms_country
  * **Used fields of the table:** country_id, country_iso, country_iso3, country_isonum, country_continent_code, country_name, country_continent
  * **Selection by:** 
    * A value of "continent_code"  //(EU)//
    * **AND NOT** Several values of "country_iso3" //('ala','alb')//
    * **AND** First letter from "country_iso" // (a,b,c) //

\\

===== Script: =====

**File:** template/frontend_remder/rt_example_db_query01.php

<code php |h rt_example_db_query01.php |h>

<?php
 
/**
*********************************************************************************************
* 13.10.10 KH: http://planmatrix.de V1.0
* frontend_render-Script: Example for a simple data base query
* TAGs: {DB-QUERY} 
* Assumption: The database tables are available to be queried. 
* (This can also be created tables).
* Example: The existing table phpwcms_country
*********************************************************************************************
*/
// -------------------------------------------------------------------------------------------
// obligate check for phpwcms constants
   if (!defined('PHPWCMS_ROOT')) {
      die("You Cannot Access This Script Directly, Have a Nice Day."); }
// -------------------------------------------------------------------------------------------


if (strpos($content["all"], '{DB-QUERY}') ) {

// --- Custom var
	$select_continent_code = 'EU';
	$select_continent_iso3 = array('ala','alb');

	// Example SQL statement for country table
	$sql  = "SELECT country_id, country_iso, country_iso3, country_isonum, country_continent_code, country_name, country_continent ";

	$sql .= "FROM ".DB_PREPEND."phpwcms_country ";
	// Select by ....
	$sql .= "WHERE country_continent_code='".$select_continent_code."' ";
	// AND ISO has to start with the first letter e.g. a,b,c
	$sql .= "AND (country_iso LIKE 'a%' OR country_iso LIKE 'b%' OR country_iso LIKE 'c%') ";
	// AND strings (e.g. in array) not part of ISO3
//	$sql .= "AND country_iso3 NOT IN ('ala','alb') ";		// Variant 1 without array
	$sql .= "AND country_iso3 NOT IN ('".implode("','", $select_continent_iso3)."') ";	// Variant 2
	// Sort order
	$sql .= "ORDER BY country_iso ASC";

//	--- Test
//	dumpVar($sql);
	
	$result = _dbQuery($sql);
	
	
//	--- Test
//	dumpVar($result);


// --- OUTPUT
	$table = '';
	if(isset($result[0]))  {
		$table  ='<table width="500" border="1" align="left" cellpadding="2" cellspacing="0"  summary="Tabelle Country">'.LF;
		$table .= '<tbody>'.LF.'<tr>'.LF;
		$table .= 	 LF.'<td> db-ID </td>'
					.LF.'<td> ISO </td>'
					.LF.'<td> ISO3 </td>'
					.LF.'<td> ISONUM </td>'
					.LF.'<td> C-CODE </td>'
					.LF.'<td> C-NAME </td>'
					.LF.'<td> CONTINENT </td>'
					.LF;
		$table .= '</tr>'.LF;
		
		foreach ($result as $value) {
			$table .= '<tr>'.LF;
			$table .= 	 LF.'<td>'.$value['country_id'].			'</td>'
						.LF.'<td>'.$value['country_iso'].			'</td>'
						.LF.'<td>'.$value['country_iso3'].			'</td>'
						.LF.'<td>'.$value['country_isonum'].		'</td>'
						.LF.'<td>'.$value['country_continent_code'].'</td>'
						.LF.'<td>'.$value['country_name'].	'</td>'
						.LF.'<td>'.$value['country_continent'].'</td>'
						.LF;
			$table .= '</tr>'.LF;
		}
	
		$table .= '<tbody>'.LF.'</table>'.LF;
	}


// --- Replace and insert into page
	$content["all"] = str_replace('{DB-QUERY}',$table, $content["all"]);

}

?>

</code>

\\

\\

===== Result: =====

**TAG:** {DB-QUERY} e.g. in the CP  "Plain Text".

{{:deutsch:technik:aufruf-interner-funktionen:example_db_query01_1.gif|}}
 

\\

~~UP~~

===== Script V1.1: =====

**With parameter transfer** e.g. **{DB-QUERY:EU}**, Results, see above.

**File:** template/frontend_remder/rt_example_db_query01.php

**TAG:** {DB-QUERY:[C-CODE]} 



<code php |h rt_example_db_query01.php |h>
<?php
 
/**
*********************************************************************************************
* 13.10.10 KH: http://planmatrix.de V1.1
* Update V1.1: Parameter input C-CODE
* frontend_render-Script: Example for a simple data base query
* TAGs: {DB-QUERY:[C-CODE]} 
* Assumption: The database tables are available to be queried. 
* (This can also be created tables).
* Example: The existing table phpwcms_country
*********************************************************************************************
*/
// -------------------------------------------------------------------------------------------
// obligate check for phpwcms constants
   if (!defined('PHPWCMS_ROOT')) {
      die("You Cannot Access This Script Directly, Have a Nice Day."); }
// -------------------------------------------------------------------------------------------


if (strpos($content["all"], '{DB-QUERY:') ) {

	preg_match_all ('/\{DB-QUERY:(\w+)\}/e', $content["all"], $matches);

//	--- Test
//	dumpVar($matches);

// --- Custom var
	$allowed_input_selector 	= array('AF','AN','AS','EU','NA','OC','SA');
	$select_continent_code 		= 'xx';
	$select_continent_iso3 		= array('ala','alb');
	$error_text					= 'ERROR: The parameter is incorrect!';
	$error_flag					= true;
// ---


// --- catch the selector
	if (!empty($matches['1'])) {
		$input = $matches['1']['0'];

		// --- One way		
		//	$select_continent_code = aporeplace(strip_tags($matches['1']['0']));

		// --- The better way
		if ( in_array( strtoupper($input), $allowed_input_selector) ) {
			$select_continent_code 	= aporeplace(trim($matches['1']['0']));
			$error_flag 			= false;
		}
	}
	
	IF (!$error_flag) {
		// Example SQL statement for country table
		$sql  = "SELECT country_id, country_iso, country_iso3, country_isonum, country_continent_code, country_name, country_continent ";

		$sql .= "FROM ".DB_PREPEND."phpwcms_country ";
		// Select by continent code
		$sql .= "WHERE country_continent_code='".$select_continent_code."' ";
		// AND ISO has to start with the first letter e.g. a,b,c
		$sql .= "AND (country_iso LIKE 'a%' OR country_iso LIKE 'b%' OR country_iso LIKE 'c%') ";
		// AND strings (e.g. in array) not part of ISO3
	//	$sql .= "AND country_iso3 NOT IN ('ala','alb') ";		// Variant 1 without array
		$sql .= "AND country_iso3 NOT IN ('".implode("','", $select_continent_iso3)."') ";	// Variant 2
		// Sort order
		$sql .= "ORDER BY country_iso ASC";

//	--- Test
//	dumpVar($sql);
		
		$result = _dbQuery($sql);
		
		
//	--- Test
//	dumpVar($result);


		// --- OUTPUT
		$table = '';
		if(isset($result[0]))  {
			$table  ='<table width="500" border="1" align="left" cellpadding="2" cellspacing="0"  summary="Tabelle Country">'.LF;
			$table .= '<tbody>'.LF.'<tr>'.LF;
			$table .= 	 LF.'<td> db-ID </td>'
						.LF.'<td> ISO </td>'
						.LF.'<td> ISO3 </td>'
						.LF.'<td> ISONUM </td>'
						.LF.'<td> C-CODE </td>'
						.LF.'<td> C-NAME </td>'
						.LF.'<td> CONTINENT </td>'
						.LF;
			$table .= '</tr>'.LF;
			
			foreach ($result as $value) {
				$table .= '<tr>'.LF;
				$table .= 	 LF.'<td>'.$value['country_id'].			'</td>'
							.LF.'<td>'.$value['country_iso'].			'</td>'
							.LF.'<td>'.$value['country_iso3'].			'</td>'
							.LF.'<td>'.$value['country_isonum'].		'</td>'
							.LF.'<td>'.$value['country_continent_code'].'</td>'
							.LF.'<td>'.$value['country_name'].	'</td>'
							.LF.'<td>'.$value['country_continent'].'</td>'
							.LF;
				$table .= '</tr>'.LF;
			}
		
			$table .= '<tbody>'.LF.'</table>'.LF;
		}
	}
	
	if ($error_flag) $table = $error_text;

// --- Replace and insert into page
//	$content["all"] = str_replace('{DB-QUERY:}',$table, $content["all"]);	// Old version
	
	$content["all"] = preg_replace('/\{DB-QUERY\:'.$input.'\}/', $table, $content["all"]);

}

?>
</code>
\\

<note>
Please pay attention to the inquiry of security  after  ## %%// --- catch the selector %%## \\
If the data comes from a user input, this data must be considered very low!! 
</note>


**Links:**
  * [[http://php.net/manual/de/function.preg-match.php|PHP preg_match]] 
  * [[http://www.php.net/manual/de/function.preg-replace.php|PHP preg_replace]]
  * [[links/php/regular-expression]]
  * [[http://php.net/manual/de/function.in-array.php|PHP in_array]]

\\