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

english/technics/internal-function-call/db_query_using_function_dbquery.1286983078.txt.gz · Last modified: 2018/06/03 18:07 (external edit)

Show pagesource Old revisions