DB-Abfrage mit der Funkt. "_dbQuery"

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

function _dbQuery($query='', $_queryMode='ASSOC') { ....... } 


$_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}

rt_example_db_query00.php

<?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"]);
 
}
 
?>



Ergebnis:

TAG: {DB-QUERY} z.B. im CP “Einfacher Text”.


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]}

rt_example_db_query01.php

<?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"]);
 
}
 
?>


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


Beispiel 02:

Kundendatenbank:

SQL: Beispieltabelle test_db_query02

# --------------------------------------------------------
# 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 */;


Script V1.2:

Datei: template/inc_script/frontend_render/rt_example_db_query02.php

Example

<?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"]);
 
}
 
?>


Ergebnis:

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

{DB-QUERY2:Eyeliner Pen}


Links:


deutsch/technik/aufruf-interner-funktionen/db-abfrage_mit_dbquery.txt · Last modified: 2018/06/03 18:08 (external edit)
www.planmatrix.de www.chimeric.de Creative Commons License Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0