Database manipulation using statement builders

Db manages PDO database connections and acts as a factory for all SQL statement builder objects.

SQL table creation codes for example tables:

  1. Example code for locations
    Example tables as MySQL
    1. CREATE TABLE `locations` (
    2.   `id` bigint(20) UNSIGNED NOT NULL,
    3.   `name` varchar(60) COLLATE utf8_bin NOT NULL,
    4.   `street` varchar(256) COLLATE utf8_bin NOT NULL,
    5.   `zipcode` varchar(30) COLLATE utf8_bin NOT NULL,
    6.   `city` varchar(60) COLLATE utf8_bin NOT NULL,
    7.   `country` varchar(60) COLLATE utf8_bin NOT NULL,
    8.   `maplink` varchar(256) COLLATE utf8_bin DEFAULT NULL
    9.  
    10. ALTER TABLE `locations`
    11.   ADD PRIMARY KEY (`id`),
    12.   ADD UNIQUE KEY `name` (`name`);
    13.  
    14. ALTER TABLE `locations`
    15.  
    Highlighted with GeSHi 1.0.9.1
    table
  2. Example code for address
    Example tables as MySQL
    1. CREATE TABLE `address` (
    2.   `id` bigint(20) UNSIGNED NOT NULL COMMENT 'Primary key',
    3.   `street` varchar(256) COLLATE utf8_bin NOT NULL COMMENT 'Street address',
    4.   `zipcode` varchar(15) COLLATE utf8_bin DEFAULT NULL COMMENT 'Zipcode',
    5.   `city` varchar(256) COLLATE utf8_bin NOT NULL COMMENT 'City or district',
    6.   `country` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 'Country name'
    7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Geographical addresses';
    8.  
    9. ALTER TABLE `address`
    10.   ADD PRIMARY KEY (`id`);
    11.  
    12. ALTER TABLE `address`
    13.  
    Highlighted with GeSHi 1.0.9.1
    table
  3. Example code for persons
    Example tables as MySQL
    1. CREATE TABLE `person` (
    2.   `id` bigint(20) UNSIGNED NOT NULL COMMENT 'Primary key',
    3.   `fnames` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'List of given names',
    4.   `lname` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'Last name',
    5.   `sex` set('f','m','unknown') COLLATE utf8_bin NOT NULL DEFAULT 'unknown' COMMENT 'Sex',
    6.   `dob` date DEFAULT NULL COMMENT 'Date of birth',
    7.   `address` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Home address'
    8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Personal information';
    9.  
    10. ALTER TABLE `person`
    11.   ADD PRIMARY KEY (`id`),
    12.   ADD UNIQUE KEY `fnames` (`fnames`,`lname`),
    13.   ADD KEY `address` (`address`);
    14.  
    15. ALTER TABLE `person`
    16.  
    17. ALTER TABLE `person`
    18.   ADD CONSTRAINT `person_ibfk_1` FOREIGN KEY (`address`) REFERENCES `address` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
    19.  
    Highlighted with GeSHi 1.0.9.1
    table

Inserting records with Insert object

The Insert object executes declarative INSERT statement in SQL databases.

  • PHP code
    1. <?php
    2.  
    3. namespace Sphp\Database;
    4.  
    5. try {
    6.   $address = Db::query()
    7.           ->get('id')
    8.           ->from('address')
    9.           ->where(['street', '=', '2210 Quisque Rd.'])
    10.           ->fetchColumn();
    11.  
    12.   echo "\nRows deleted: " . DB::delete()->from('person')
    13.           ->where(['fname', '=', 'Sami'], ['lname', '=', 'Holck'])
    14.           ->affectRows() . "\n";
    15.  
    16.   echo "\nRows inserted: " . DB::insert()
    17.           ->into('person')
    18.           ->columnNames('fname', 'lname', 'sex', 'dob')
    19.           ->values('Sami', 'Holck', 'm', '1975-09-16')
    20.           ->affectRows() . "\n";
    21.  
    22.  
    23.   print_r(Db::query()->get("CONCAT(fname, ' ', lname) as name")
    24.                   ->from('person')
    25.                   ->where(['fname', '=', 'Sami'], ['lname', '=', 'Holck'])
    26.                   ->fetchFirstRow());
    27. } catch (\Throwable $ex) {
    28.   echo $ex;
    29. }
    30.  
    Highlighted with GeSHi 1.0.9.1
  • Execution result as highlighted code
    1.  
    2. Rows deleted: 1
    3.  
    4. Rows inserted: 1
    5. Array
    6. (
    7.     [name] => Sami Holck
    8. )
    9.  
    Highlighted with GeSHi 1.0.9.1
  • PHP code
    1. <?php
    2.  
    3. namespace Sphp\Database;
    4.  
    5. // print_r(Db::query()->get('*')->from('locations')->execute()->fetchAll());
    6. try {
    7.   $data[] = [
    8.       'street' => 'Koivuluodontie 2',
    9.       'zipcode' => '20240',
    10.       'city' => 'Turku',
    11.       'country' => 'Finland',
    12.   ];
    13.   $data[] = [
    14.       'street' => 'Rakuunatie 59 A 3',
    15.       'zipcode' => '20720',
    16.       'city' => 'Turku',
    17.       'country' => 'Finland',
    18.   ];
    19.   $data[] = [
    20.       'street' => 'W2 2UH',
    21.       'zipcode' => '12538',
    22.       'city' => 'London',
    23.       'country' => 'UK',
    24.   ];
    25.   $inserter = Db::insert()
    26.           ->into('address')
    27.           ->valuesFromArray($data);
    28.   echo $inserter->statementToString();
    29.   $inserter->affectRows();
    30.   var_dump(Db::insert()
    31.                   ->into('address')
    32.                   ->columnNames('street', 'zipcode', 'city', 'country')
    33.                   ->valuesFromArray($hydeparkData)
    34.                   ->affectRows());
    35. //echo Db::insert()->into('locations')->values($hydeparkData)->affectRows();
    36. } catch (\Throwable $ex) {
    37.   echo $ex->getMessage();
    38. }
    39.  
    40.  
    Highlighted with GeSHi 1.0.9.1
  • Execution result as highlighted code
    1. INSERT INTO `address` VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?)<div class="callout alert-box warning" data-closable><div class="type"><span class='icon'><i class="fas fa-exclamation-triangle fa-2x" ></i></span> E_NOTICE</div><div class="message">A non well formed numeric value encountered</div><div class="file-info">on line <strong>67</strong> of file:<div class="file-path"><wbr>/home<wbr>/int48291<wbr>/public_html<wbr>/playground<wbr>/sphp<wbr>/php<wbr>/Sphp<wbr>/Database<wbr>/AbstractStatement.php</div></div><button data-close class="close-button" aria-label="close"><span aria-hidden="true">&times;</span></button></div>SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1
    Highlighted with GeSHi 1.0.9.1

Quering data with Query object

The Query object executes declarative SELECT queries in SQL databases. It retrieves data from one or more SQL tables, or expressions and it has no persistent effects on the database.

Some essential Query methods:

  • A Simple QUERY Example
    1. <?php
    2.  
    3. namespace Sphp\Database;
    4.  
    5. use Sphp\Database\Rules\Rule;
    6.  
    7. //Db::createFrom($statisticsDb, 'stats');
    8. try {
    9.   $query1 = Db::query('stats')->get('visitors.uid', 'userAgents.userAgent', 'visitors.visits')
    10.           ->from('visitors INNER JOIN userAgents ON visitors.uaid = userAgents.id')
    11.           ->where(Rule::compare('visits', '<', 10));
    12.  
    13.   print_r($query1
    14.                   ->orderBy('visitors.visits DESC')
    15.                   ->setLimit(3)
    16.                   ->fetchAll());
    17.   //echo $query1->statementToString();
    18. } catch (\Throwable $ex) {
    19.   echo $ex;
    20. }
    21.  
    22.  
    23. try {
    24.   $query = Db::query()->get('fname', 'lname', 'country')
    25.           ->from('person LEFT JOIN address ON address.id = person.address')
    26.           ->where(Rule::isIn('country', ['Finland', 'Sweden', 'Myanmar']));
    27.   //->where(Rule::is('address.country', 'Poland'));
    28.   //var_dump($query->count());
    29.   echo $query->statementToString();
    30.   print_r($query
    31.                   ->groupBy('address.country ASC', 'person.lname')
    32.                   ->setLimit(5)
    33.                   ->setOffset(1)
    34.                   ->fetchAll());
    35. } catch (\Throwable $ex) {
    36.   echo $ex;
    37. }
    38.  
    Highlighted with GeSHi 1.0.9.1
  • Execution result as highlighted code
    1. Array
    2. (
    3.     [0] => Array
    4.         (
    5.             [uid] => ecd54d07-59e4-5778-8fee-d14af7ff829b
    6.             [userAgent] => Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36
    7.             [visits] => 9
    8.         )
    9.  
    10.     [1] => Array
    11.         (
    12.             [uid] => 2d608bb0-3bfe-5aba-8f4d-5f9fe54d721f
    13.             [userAgent] => Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36
    14.             [visits] => 9
    15.         )
    16.  
    17.     [2] => Array
    18.         (
    19.             [uid] => 1c0b5cd8-c908-54b9-905b-c487704521b4
    20.             [userAgent] => Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36
    21.             [visits] => 9
    22.         )
    23.  
    24. )
    25. SELECT fname, lname, country FROM person LEFT JOIN address ON address.id = person.address WHERE (country IN (?, ?, ?))Array
    26. (
    27.     [0] => Array
    28.         (
    29.             [fname] => Nero
    30.             [lname] => Kane
    31.             [country] => Myanmar
    32.         )
    33.  
    34.     [1] => Array
    35.         (
    36.             [fname] => Germaine
    37.             [lname] => Mcintosh
    38.             [country] => Myanmar
    39.         )
    40.  
    41.     [2] => Array
    42.         (
    43.             [fname] => Hayden
    44.             [lname] => Snyder
    45.             [country] => Myanmar
    46.         )
    47.  
    48.     [3] => Array
    49.         (
    50.             [fname] => Hall
    51.             [lname] => Hendrix
    52.             [country] => Sweden
    53.         )
    54.  
    55.     [4] => Array
    56.         (
    57.             [fname] => Athena
    58.             [lname] => Lancaster
    59.             [country] => Sweden
    60.         )
    61.  
    62. )
    63.  
    Highlighted with GeSHi 1.0.9.1

Updating records with Update object

The Update object executes declarative UPDATE statement in SQL databases.

An Update object changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen Update::where().

  • A Simple UPDATE Query Example
    1. <?php
    2.  
    3. namespace Sphp\Database;
    4.  
    5. use Sphp\Database\Rules\Rule;
    6.  
    7. $update = Db::update()
    8.         ->table('locations')
    9.         ->set(['country' => 'United Kingdom'])
    10.         ->where(Rule::is('name', 'Hyde Park'));
    11. var_dump($update->affectRows());
    12.  
    Highlighted with GeSHi 1.0.9.1
  • Execution result as highlighted code
    1. int(0)
    2.  
    Highlighted with GeSHi 1.0.9.1

Deleting records with Delete object

The Delete object removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.

  • A Simple DELETE Query Example
    1. <?php
    2.  
    3. namespace Sphp\Database;
    4.  
    5. use Sphp\Database\Rules\Rule;
    6.  
    7. var_dump(Db::delete()
    8.                 ->from('address')
    9.                 ->where(Rule::isIn('street', ['Koivuluodontie 2', 'Rakuunatie 59 A 3', 'W2 2UH']))
    10.                 ->affectRows());
    11.  
    Highlighted with GeSHi 1.0.9.1
  • Execution result as highlighted code
    1. int(0)
    2.  
    Highlighted with GeSHi 1.0.9.1

References: