NAVIGATION
This is an old revision of the document!
hier ein kleines Beispiel, wie die eingebaute Funktion _dbQuery in einem Script verwendet werden kann.
function _dbQuery($query='', $_queryMode='ASSOC') { ....... }
$_queryMode:
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 hier aus der DB abgeholt, selektiert und in ein brauchbares Format gebracht, hier <tabelle>.
Über einen Replacement-TAG wird das Ergebnis in einem CP abgebildet.
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 | … | … | … | … | … | … |
Datei: template/frontend_remder/rt_example_db_query00.php
TAG: {DB-QUERY}
<?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"]); } ?>
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]}
<?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 = $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"]); } ?>
<note>
Unbedingt auf die Sicherheitabfrage achten nach // --- catch the selector
Falls die Daten von einer Benutzereingabe kommen, müssen diese daten sehr tief geprüft werden!!
</note>