You are here: Willkommen im phpwcms-HowTo:wiki » Benutzerhandbuch (DE) » Technik » Aufruf interner Funktionen » DB-Abfrage mit der Funkt. "_dbQuery"

Show pagesource Old revisions

Recent changes Index

NAVIGATION

Imprint / Impressum

Please Donate!

With your donation you accelerate more enhancements of the wiki.

DB-Abfrage mit der Funkt. "_dbQuery"
- Beispiel 01:
- Beispiel 02:
  - Script V1.2:
  - Ergebnis:

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)

Show pagesource Old revisions