PHP code example of vimac / myspot

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

vimac / myspot example snippets

use MySpot\SqlMapConfig;
use MySpot\SqlMap;

// Initialize PDO first, deal with your database connection
$pdo = new PDO('sqlite::memory:'); 

// Initialize SqlMapConfig
// Specific your SQL map configuration dir
// Like `__DIR__ . '/../config/myspot'`
$sqlMapConfig = new SqlMapConfig('__PATH_TO_YOUR_CONFIGURATION_DIR__', $pdo);

// Optional, you can setup your default map style, _under_score_ to lowerCamelCase is recommended
$sqlMapConfig->setDefaultResultMapStyle(\MySpot\SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE);

// Initialize SqlMap
// If you are using a framework which support Dependency Injection, it is recommend that you put this in SqlMap your container
// You can checkout the demo project and see how it works
$sqlMap = new SqlMap($sqlMapConfig);

// Example file name: ${projectDir}/config/myspot/test/post.php
// Note: This file should be put in the directory which the initialize code specific
Use MySpot\SqlMapStatement;
return [
    'select' => [
        'sql' => 'SELECT * FROM `test`.`post`',
        'resultType' => \MyProject\DataObject\Test\PostDO::class, // resultType only available when it's a select query
        'resultMapStyle' => SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE // The statement specific map style
    ],
    'selectById' => [
        'sql' => 'SELECT * FROM `test`.`post` WHERE `id` = :id LIMIT 1',
        'resultType' => \MyProject\DataObject\Test\PostDO::class,
        'resultMapStyle' => SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE
    ],
    'selectByIds' => [
        'sql' => 'SELECT * FROM `test`.`post` WHERE `id` in :id:',
        'resultType' => \MyProject\DataObject\Test\PostDO::class,
        'resultMapStyle' => SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE
    ],
    'selectCountByUid' => [
        'sql' => 'SELECT COUNT(*) FROM `test`.`post` WHERE `uid` = :uid'
    ],
    'selectIdUidTitleSummary' => [
        'sql' => 'SELECT `id`, `uid`, `title`, `summary` FROM `test`.`post` :orderByCreatedAt?{ORDER BY `created_at` ASC}',
        'resultType' => \MyProject\DataObject\Test\PostDO::class,
        'resultMapStyle' => SqlMapStatement::MAP_STYLE_UNDERSCORE_TO_LOWER_CAMELCASE
    ],
    'insert' => [
        'sql' => 'INSERT INTO `test`.`post` #INSERT#'
    ],
    'insertUidTitleSummaryCreatedAt' => [
        'sql' => 'INSERT INTO `test`.`post`(`uid`, `title`, `summary`, `created_at`) VALUES (:uid, :title, :summary, :createdAt)'
    ],
    'updateUidTitleSummaryById' => [
        'sql' => 'UPDATE `test`.`post` SET `uid` = :newUid, `title` = :newTitle, `summary` = :newSummary WHERE `id` = :id'
    ],
    'deleteByUid' => [
        'sql' => 'DELETE FROM `test`.`post` WHERE `uid` IN :uid: LIMIT 1'
    ]
];

// ...

/** 
 * Method 'select' parameters: statementId, [statementParameters]
 * e.g
 * statementId: db.user.selectById
 */
$sqlMapResult = $sqlMap->select('configParentDir.configParentFile.statementId', [
    // 'parameterName' => ['parameterValue', parameterType]
    // parameterType could be omit, which will be default value: MySpot\SqlMapConst::PARAM_STR
    'id' => [1, \MySpot\SqlMapConst::PARAM_INT]
]);

// e.g:
// SQL Template: SELECT * FROM `test`.`post` WHERE `id` = :id
$id = 1;
$sqlMapResult = $sqlMap->select('test.post.selectById', [
    'id' => [$id, \MySpot\SqlMapConst::PARAM_INT]
]);

// In most case, it's no difference than normal SELECT query, except its parameter is an array
// e.g:
// SQL Template: SELECT * FROM `test`.`post` WHERE `id` in :id:
$ids = [1, 2, 3];
$sqlMapResult = $sqlMap->select('test.post.selectByIds', [
    'id' => [$ids, \MySpot\SqlMapConst::PARAM_INT]
]);

// SQL template: UPDATE `test`.`post` SET `uid` = :newUid, `title` = :newTitle, `summary` = :newSummary WHERE `id` = :id
$sqlMapResult = $sqlMap->update('test.post.updateUidTitleSummaryCreatedAtById', [
    'newUid' => [$newUid, \MySpot\SqlMapConst::PARAM_INT], 
    'newTitle' => [$newTitle, \MySpot\SqlMapConst::PARAM_STR], 
    'newSummary' => [$newSummary, \MySpot\SqlMapConst::PARAM_STR], 
    'newCreatedAt' => [$newCreatedAt, \MySpot\SqlMapConst::PARAM_STR], 
    'id' => [$id, \MySpot\SqlMapConst::PARAM_INT]
]);

// Insert with auto fields name
// SQL template: INSERT INTO `test`.`post` #INSERT#
$data = [
    'uid' => [$uid, \MySpot\SqlMapConst::PARAM_INT],
    'title' => [$title],
    'summary' => [$summary],
    'created_at' => [$createdAt] // Notice the key name should be original field name in the table
];
$sqlMapResult = $sqlMap->insert('test.post.insert', [], $data);

// Insert with named parameter
// SQL Template: INSERT INTO `test`.`post`(`uid`, `title`, `summary`, `created_at`) VALUES (:uid, :title, :summary, :createdAt)
$sqlMapResult = $sqlMap->insert('test.post.insertUidTitleSummaryCreatedAt', [
    'uid' => [$uid, \MySpot\SqlMapConst::PARAM_INT], 
    'title' => [$title, \MySpot\SqlMapConst::PARAM_STR], 
    'summary' => [$summary, \MySpot\SqlMapConst::PARAM_STR], 
    'createdAt' => [$createdAt, \MySpot\SqlMapConst::PARAM_STR]
]);

// SQL Template: DELETE FROM `test`.`post` WHERE `uid` IN :uid: LIMIT 1
$sqlMapResult = $sqlMap->delete('test.post.deleteByUid', [
    'uid' => [$uid, \MySpot\SqlMapConst::PARAM_INT]
]);

// SQL Template: SELECT `id`, `uid`, `title`, `summary` FROM `test`.`post` :orderByCreatedAt?{ORDER BY`created_at` DESC}

// Equals: SELECT `id`, `uid`, `title`, `summary` FROM `test`.`post` 
$sqlMapResult = $sqlMap->select('test.post.selectIdUidTitleSummary', [
    'orderByCreatedAt' => [false, \MySpot\SqlMapConst::PARAM_BOOL]
]);

// Equals: SELECT `id`, `uid`, `title`, `summary` FROM `test`.`post` ORDER BY `created_at` DESC
$sqlMapResult = $sqlMap->select('test.post.selectIdUidTitleSummary', [
    'orderByCreatedAt' => [true, \MySpot\SqlMapConst::PARAM_BOOL]
]);

// All of the result will be combined into a class instance of `MySpot\SqlMapResult`

// Fetch an array of object or array of array depends on your configuration
$sqlMapResult->fetchAll();

// Fetch first object or array depends on your configuration
$sqlMapResult->fetch();

// Traverse all of the fetched data
while ($result = $sqlMapResult->fetch()) {
    // Do something
}

// Fetch the first column of first row, it's useful for SELECT COUNT query
$sqlMapResult->fetchColumn();

// Fetch the specific column of first row
$sqlMapResult->fetchColumn(3);

// Fetch the last insert Id when you inserted new row
$sqlMapResult->getLastInsertId();

// Fetch the affected lines when you updated or deleted something
$sqlMapResult->getAffectedLines();

// Fetch the execute result in boolean value
$sqlMapResult->getExecutedResult();

// Get the bound PDOStatement
$sqlMapResult->getStatement();