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>

Links:

deutsch/technik/aufruf-interner-funktionen/db-abfrage_mit_dbquery.1287554432.txt.gz · Last modified: 2018/06/03 18:06 (external edit)

Show pagesource Old revisions