NAVIGATION
Differences
This shows you the differences between two versions of the page.
|
english:technics:internal-function-call:db_query_using_function_dbquery [2010/10/13 17:17] Knut Heermann (flip-flop) created |
english:technics:internal-function-call:db_query_using_function_dbquery [2018/06/03 18:09] (current) |
||
|---|---|---|---|
| Line 160: | Line 160: | ||
| + | \\ | ||
| + | |||
| + | ~~UP~~ | ||
| + | |||
| + | ===== 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]} | ||
| + | |||
| + | |||
| + | |||
| + | <code php |h rt_example_db_query01.php |h> | ||
| + | <?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"]); | ||
| + | |||
| + | } | ||
| + | |||
| + | ?> | ||
| + | </code> | ||
| + | \\ | ||
| + | |||
| + | <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:** | ||
| + | * [[http://php.net/manual/de/function.preg-match.php|PHP preg_match]] | ||
| + | * [[http://www.php.net/manual/de/function.preg-replace.php|PHP preg_replace]] | ||
| + | * [[links/php/regular-expression]] | ||
| + | * [[http://php.net/manual/de/function.in-array.php|PHP in_array]] | ||
| + | |||
| + | \\ | ||
