DB query using the funct. "_dbQuery"

DB query using the funct. "_dbQuery"

FIXME Better TRANSLATE

Here's a small example of how the built-in function _dbQuery may be used in a script.

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)

Example:

Adoption: From a given table (exemplarily “phpwcms_country”) data should be selected and displayed in a CP.
Of course the table can also be a self-created.

The script is stored in the folder /frontend_render/ . The data are here collected from the database, selected and placed into a usable format, here <table>.
With a replacement tag is the result shown in a CP.

Adjusted Table Excerpt:

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 in CMS: {DB-QUERY}
Table name: phpwcms_country
Used fields of the table: country_id, country_iso, country_iso3, country_isonum, country_continent_code, country_name, country_continent
Selection by:
- A value of “continent_code” (EU)
- AND NOT Several values of “country_iso3” ('ala','alb')
- AND First letter from “country_iso” (a,b,c)

Script:

File: template/frontend_remder/rt_example_db_query01.php

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

Result:

TAG: {DB-QUERY} e.g. in the CP “Plain Text”.

Script V1.1:

With parameter transfer e.g. {DB-QUERY:EU}, Results, see above.

File: 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> Please pay attention to the inquiry of security after // --- catch the selector
If the data comes from a user input, this data must be considered very low!! </note>

Links: