WebsiteBaker Documentation

User Tools

Site Tools

You are here: start » en » dev » all » examples » sql
en:dev:all:examples:sql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Last revisionBoth sides next revision
en:dev:all:examples:sql [16.07.2015 21:56] – [INSERT / UPDATE] translated mrbasemanen:dev:all:examples:sql [20.02.2019 07:46] – [SELECT] Manuela v.d.Decken
Line 1: Line 1:
-FIXME **This page is not fully translated, yet. Please help completing the translation.**\\ //(remove this paragraph once the translation is finished)// 
- 
 ====== This is how SQL works... ====== ====== This is how SQL works... ======
  
Line 18: Line 16:
 Each of all the four examples give back a result-object with all records of the visible sections of a page at the current time. Each of all the four examples give back a result-object with all records of the visible sections of a page at the current time.
 <code php Example-1.php>  <code php Example-1.php> 
-$oResult = $oDb->doQuery("SELECT section_id, page_id, position, module, block, publ_start, publ_end FROM ".TABLE_PREFIX."sections where page_id= $iPageId and (publ_start = 0 OR publ_start <= $iTimestamp ) and (publ_end = 0 OR publ_end >= $iTimestamp ) order by block, position");+$oResult = $oDb->doQuery("SELECT section_id, page_id, position, module, block, publ_start, publ_end FROM ".TABLE_PREFIX."sections where page_id= $iPageId and $iTimestamp between `publ_start` and `publ_end` order by block, position");
 </code> </code>
 <code php Example-2.php>  <code php Example-2.php> 
 $sql  = 'SELECT `section_id`, `page_id`, `position`, `module`, `block`, `publ_start`, `publ_end` FROM `'.$oDb->TablePrefix.'sections` '; $sql  = 'SELECT `section_id`, `page_id`, `position`, `module`, `block`, `publ_start`, `publ_end` FROM `'.$oDb->TablePrefix.'sections` ';
-$sql .= 'WHERE `page_id`='.$iPageId.' AND (`publ_start`=0 OR `publ_start`<='.$iTimestamp.') AND (`publ_end`=0 OR `publ_end`>='.$iTimestamp.') ';+$sql .= 'WHERE `page_id`='.$iPageId.' AND '.$iTimestamp.' BETWEEN `publ_start` AND `publ_end` ';
 $sql .= 'ORDER BY `block`, `position`'; $sql .= 'ORDER BY `block`, `position`';
 $oResult = $oDb->doQuery($sql); $oResult = $oDb->doQuery($sql);
Line 31: Line 29:
      . 'FROM `'.$oDb->TablePrefix.'sections` '      . 'FROM `'.$oDb->TablePrefix.'sections` '
      . 'WHERE `page_id`='.$iPageId.' '      . 'WHERE `page_id`='.$iPageId.' '
-     .        'AND (`publ_start`=0 OR `publ_start`<='.$iTimestamp.') ' +     .        'AND '.$iTimestamp.' BETWEEN `publ_start` AND `publ_end` '
-     .        'AND (`publ_end`=0 OR `publ_end`>='.$iTimestamp.') '+
      . 'ORDER BY `block`, `position`';      . 'ORDER BY `block`, `position`';
 $oResult = $oDb->doQuery($sql);      $oResult = $oDb->doQuery($sql);     
Line 39: Line 36:
 $sql = 'SELECT * FROM `'.$oDb->TablePrefix.'sections` ' $sql = 'SELECT * FROM `'.$oDb->TablePrefix.'sections` '
      . 'WHERE `page_id`='.$iPageId.' '      . 'WHERE `page_id`='.$iPageId.' '
-     .        'AND (`publ_start`=0 OR `publ_start`<='.$iTimestamp.') ' +     .        'AND '.$iTimestamp.' BETWEEN `publ_start` AND `publ_end` '
-     .        'AND (`publ_end`=0 OR `publ_end`>='.$iTimestamp.') '+
      . 'ORDER BY `block`, `position`';      . 'ORDER BY `block`, `position`';
 $oResult = $oDb->doQuery($sql);      $oResult = $oDb->doQuery($sql);     
 </code>Let's have a short quiz now:\\ </code>Let's have a short quiz now:\\
-Question: Which of the examples are easier to read, to understand and to modify if required? **1**&**2**  or **3**&**4** ??+Question: Which of the examples are easier to read, understand and modify if required? **1**&**2**  or **3**&**4** ??
 ---- ----
  
Line 98: Line 94:
  
 ==== REPLACE ==== ==== REPLACE ====
-//(mySQL spezifische Erweiterung zu ANSI-SQL)//+//(MySQL specific extension to ANSI SQL)//
  
-Wie überall gibt es fast keine Regel ohne Ausnahme.\\ +Like everywhere else, there is almost no rule without exception.\\ 
-**REPLACE** funktioniert primär identisch wie **INSERT**. Mit einem kleinen, jedoch entscheidenden Unterschied:\\ +**REPLACE** works primarily the same as **INSERT**. With a small but crucial difference:\\ 
-Wird versucht, einen Datensatz einzufügen, der einen Index-Konflikt mit einem bestehende Datensatz auslöst, wird der Prozess nicht abgebrochen, sondern der bestehende, alte Datensatz wird gelöscht und der neue eingefügt.\\ +If you try to insert a record which would cause an index conflict with an existing data set, the process will not be canceled, but the existing, old record is deleted and the new values are added instead.\\ 
-**REPLACE** ist grundsätzlich nicht für Tabellen geeignet, die einen Autoincrement-Wert nutzen, der in einer anderen Tabelle als Fremdschlüssel benutzt wird.\\ +**REPLACE** is fundamentally unsuitable for tables that use an autoincrement value that is used in another table as a foreign key.\\ 
-Der WebsiteBaker-Core benutzt dieses SQL-Kommando z.B. zum Eintragen und Ändern von Werten in die Tabelle `settings`. Damit das Ganze funktioniert, sind gewisse Anforderungen an die Tabelle zu erfüllen.\\+The WebsiteBaker core uses this SQL command, for example, for entering and changing values in the table `settings`. For this to work, certain requirements must be met at the table.\\
 <code sql> <code sql>
 -- Structure of table  '{TABLE_PREFIX}settings' -- Structure of table  '{TABLE_PREFIX}settings'
Line 114: Line 110:
 ){TABLE_ENGINE}; ){TABLE_ENGINE};
 </code> </code>
-Im Statement muss zwingend ein Wert für das Feld mit dem PRIMARY_KEY oder UNIQUE_KEY übergeben werden. +In a statement it is mandatory to pass a value for the field with the PRIMARY_KEY or unique_key. Also, the structure of the statement differs from normal 'SET' - standard, in turn, several records can be changed in one run:
-Auch der Aufbau des Statements weicht vom normalen 'SET' - Standard ab, dafür können als Ausgleich auch mehrere Datensäte in einem Zug geändert werden:+
 <code php snippet.php> <code php snippet.php>
     $sql = 'REPLACE INTO `'.$oDb->TablePrefix.'settings` (`name`, `value`) '     $sql = 'REPLACE INTO `'.$oDb->TablePrefix.'settings` (`name`, `value`) '
Line 125: Line 120:
     }</code>     }</code>
  
-  
en/dev/all/examples/sql.txt · Last modified: 20.02.2019 07:49 by Manuela v.d.Decken

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
CC Attribution-Noncommercial-Share Alike 4.0 International Donate Driven by DokuWiki Template From SyntaxSeed