{{indexmenu_n>300}}

====== DB-Abfrage mit der Funkt. "_dbQuery" ======

hier ein kleines Beispiel, wie die eingebaute Funktion **_dbQuery** in einem Script verwendet werden kann. 

<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)//

\\

===== Beispiel 01: =====

**Annahme:** Aus einer gegebene Tabelle //(hier beispielhaft "phpwcms_country")// sollen Daten selektiert und in einem CP abgebildet werden. \\
Die Tabelle kann natürlich auch eine selbst erstellte sein.

Das Script wird im Ordner /frontend_render/ abgelegt. Die Daten werden aus der DB abgeholt, selektiert und in ein brauchbares Format gebracht, hier %%<table>%%. \\
Über einen Replacement-TAG wird das Ergebnis in einem CP abgebildet. \\


\\

==== Angepasster Tabellenauszug: ====

^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 im CMS:** {DB-QUERY}
  * **Tabellenname:** phpwcms_country
  * **Verwendete Felder der Tabelle:** country_id, country_iso, country_iso3, country_isonum, country_continent_code, country_name, country_continent
  * **Selektion nach:** 
    * Ein Wert aus "continent_code"  //(EU)//
    * **UND NICHT** Mehrere Werte aus "country_iso3" //('ala','alb')//
    * **UND** Anfangsbuchstaben aus "country_iso" // (a,b,c) //

\\

==== Script V1.0: ====

**Datei:** template/frontend_remder/rt_example_db_query00.php

**TAG:** {DB-QUERY}

<code php |h rt_example_db_query00.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>

\\

\\

==== Ergebnis: ====

**TAG:** {DB-QUERY} z.B. im CP  "Einfacher Text".

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

\\ 
~~UP~~

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

**Mit Parameterübergabe** z.B. **{DB-QUERY:EU}**, Ergebnis siehe oben.

**Datei:** 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>
**Unbedingt auf die Sicherheitsabfrage achten nach ## %%// --- catch the selector %%## \\
Falls die Daten von einer Benutzereingabe kommen, müssen diese Daten sehr tief geprüft werden :!: :!:**
</note>

~~UP~~
\\

===== Beispiel 02: =====

**Kundendatenbank:**

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

**SQL: Beispieltabelle test_db_query02**
<code sql>
# --------------------------------------------------------
# Host:                         127.0.0.1
# Database:                     XXXX
# Server version:               5.1.30-community
# Server OS:                    Win32
# Date/time:                    2010-10-20 08:05:10
# --------------------------------------------------------

# Dumping structure for table XXXX.test_db_query02
CREATE TABLE IF NOT EXISTS `test_db_query02` (
  `ID` int(10) DEFAULT NULL,
  `type` char(20) DEFAULT NULL,
  `color` char(20) DEFAULT NULL,
  `something` char(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Dumping data for table XXXX.test_db_query02: 12 rows
/*!40000 ALTER TABLE `test_db_query02` DISABLE KEYS */;
INSERT INTO `test_db_query02` (`ID`, `type`, `color`, `something`) VALUES (1, 'Lipstick Pen', 'red', 'big'), (2, 'Lipstick Pen', 'red', 'small'), (3, 'Lipstick Pen', 'light red', 'big'), (4, 'Lipstick Pen', 'light red', 'small'), (5, 'Eyeliner Pen', 'black', 'big'), (6, 'Eyeliner Pen', 'black', 'small'), (7, 'Eyeliner Pen', 'dark grey', 'big'), (8, 'Eyeliner Pen', 'dark grey', 'small'), (9, 'Eyebrow Pen', 'black', 'big'), (10, 'Eyebrow Pen', 'black', 'small'), (11, 'Eyebrow Pen', 'dark grey', 'big'), (12, 'Eyebrow Pen', 'dark grey', 'small');
/*!40000 ALTER TABLE `test_db_query02` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
</code>

\\
==== Script V1.2: ====

**Datei:** template/inc_script/frontend_render/rt_example_db_query02.php

<code php |h Example |h>
<?php
 
/**
*********************************************************************************************
* 13.10.10 KH: http://planmatrix.de V1.2
* Update V1.2: Parameter input via TAG
* frontend_render-Script: Example for a simple data base query
* TAGs: {DB-QUERY7:[Parameter]} 
* Assumption: The database tables are available to be queried. 
* (This can also be created tables).
* Example: A new tabel test_db_query02
*********************************************************************************************
*/
// -------------------------------------------------------------------------------------------
// 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-QUERY2:') ) {

	preg_match_all ('/\{DB-QUERY2:(.*?)\}/', $content["all"], $matches);

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

// --- Custom var
//	$allowed_input_selector 	= array('Lipstick Pen', 'Eyeliner Pen','Eyebrow Pen');
	$select_kind		 		= '';
	$error_text					= 'ERROR: The parameter is incorrect!';
	$error_text2				= 'No DB output available!';
	$error_flag					= true;
// ---


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

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

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

		// --- The better way for testing input
//		if ( in_array( ($input), $allowed_input_selector) ) {
			$select_kind 		= aporeplace(trim($matches['1']['0']));
		if (!empty($select_kind)) {
			$error_flag 		= false;
		}
	}
	
//	--- Test
// dumpVar($select_kind);
	
	IF (!$error_flag) {
		// Example SQL statement for country table
		$sql  = "SELECT id, type, color, something ";

		$sql .= "FROM ".DB_PREPEND."test_db_query02 ";
		// Select by Kind
		$sql .= "WHERE type='".$select_kind."' ";
		// Sort order
		$sql .= "ORDER BY color, something 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="XXX YYY">'.LF;
			$table .= '<tbody>'.LF.'<tr style="font-weight: bold">'.LF;
			$table .= 	 LF.'<td> db-ID </td>'
						.LF.'<td> TYPE </td>'
						.LF.'<td> COLOR </td>'
						.LF.'<td> SOMETHING </td>'
						.LF;
			$table .= '</tr>'.LF;
			
			foreach ($result as $value) {
				$table .= '<tr>'.LF;
				$table .= 	 LF.'<td>'.$value['id'].			'</td>'
							.LF.'<td>'.$value['type'].			'</td>'
							.LF.'<td>'.$value['color'].			'</td>'
							.LF.'<td>'.$value['something'].		'</td>'
							.LF;
				$table .= '</tr>'.LF;
			}
		
			$table .= '<tbody>'.LF.'</table>'.LF;
		}
		else 
			$table = $error_text2;  // No DB output available!
			
	} // END IF (!$error_flag)
	
	
	if ($error_flag) $table = $error_text; // Wrong parameter


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

}

?>
</code>

\\

==== Ergebnis: ====

Z.B. mit dem Aufruf in einem HTML-CP 

**{DB-QUERY2:Eyeliner Pen}**

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



\\
**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]]


\\