PHP code example of amcsi / amysql

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

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

    

amcsi / amysql example snippets

$this->_amysql = new AMysql;
$this->_amysql->setConnDetails(array (
    'host' => 'localhost',
    'username' => 'user',
    'password' => 'pass',
    'db'        => 'db',
));

$this->_amysql = new AMysql(array (
    'host' => 'localhost',
    'username' => 'user',
    'password' => 'pass',
    'db'        => 'db',
));

$conn = mysql_connect($host, $user, $pass);
$amysql = new AMysql($conn);
$amysql->selectDb($db);

$data = array (
    'name' => 'adam',
    'description' => 'blah'
);
$amysql->insert('tablename', $data);

$date = $amysql->expr('CURRENT_TIMESTAMP');
// or new AMysql_Expr($amysql, 'CURRENT_TIMESTAMP');
$data = array (
    'name' => 'adam',
    'description' => 'blah',
    'date' => $date
);
$insertId = $amysql->insert('tablename', $data);
// INSERT INTO `tablename` (`name`, `description`, `date`) VALUES ('adam', 'blah', CURRENT_TIMESTAMP);

$materialsIn = $amysql->expr(
    AMysql_Expr::COLUMN_IN,
    'material',
    array('wood', 'metal', 'paper', 'plastic')
);
$sql = "SELECT * FROM tableName WHERE :materialsIn";

$amysql->query($sql, array('materialsIn' => $materialsIn));
// SELECT * FROM tableName WHERE `material` IN ('wood', 'metal', 'paper', 'plastic')

$descriptionLike = $amysql->expr(
    AMysql_Expr::ESCAPE_LIKE,
    'part'
));

$sql = "SELECT * FROM articles WHERE description LIKE :descriptionLike";
$amysql->query($sql, array('descriptionLike' => $descriptionLike));
// SELECT * FROM articles WHERE description LIKE '%part%' ESCAPE '='



// "begins with" example

$descriptionLike = $amysql->expr(
    AMysql_Expr::ESCAPE_LIKE,
    '100% success',
    '%%%s', // begins with
));

$sql = "SELECT * FROM articles WHERE description LIKE :descriptionLike";
$amysql->query($sql, array('descriptionLike' => $descriptionLike));
// SELECT * FROM articles WHERE description LIKE '%100=% success' ESCAPE '='
// note the automatic escaping of the (%) sign

$data = array (
    array (
        'name' => 'adam',
        'description' => 'blah'
    ),
    array (
        'name' => 'bob',
        'description' => 'blahblah'
    )
);
$id = $amysql->insert('tablename', $data);
$affectedRows = $amysql->lastStatement->affectedRows();

// or you can achieve the same result by having the column names be the outer indexes.

$data = array (
    'name' => array (
        'adam', 'bob'
    ),
    'description' => array (
        'blah', 'blahblah'
    )
);
$id = $amysql->insert('tablename', $data);
$affectedRows = $amysql->lastStatement->affectedRows();

/**
 * Update the name to bob and the description to blahmodified for all rows
 * with an id of 2.
 */
$data = array (
    'name' => 'bob',
    'description' => 'blahmodified'
);
$success = $amysql->update('tablename', $data, 'id = ?', array('2'));
$affectedRows = $amysql->lastStatement->affectedRows();

/**
 * Update the name to bob and the description to blahmodified for all rows
 * with an id of 2, and update the name to carmen and the description to
 * anothermodified for all rows with an id of 3.
 */
$data = array (
    array (
        'id'            => 2
        'name'          => 'bob',
        'description'   => 'blahmodified'
    ),
    array (
        'id'            => 3
        'name'          => 'carmen',
        'description'   => 'anothermodified'
    )
);
$success = $amysql->updateMultipleByData('tablename', $data, 'id');
$affectedRows = $amysql->multipleAffectedRows;

$where = 'id = ?';
$success = $amysql->delete('tablename', $where, array ('2'));
$affectedRows = $amysql->lastStatement->affectedRows();

try {
    $amysql->query("SELECTbad-syntaxError-!#");
}
catch (AMysql_Exception $e) {
    trigger_error($e, E_USER_WARNING); // the exception converted to string
    // contains the mysql error code, the error message, and the query string used.
    // $e->getCode() contains the mysql error code
}

try {
    $data = array('passwordHash' => $passwordHash, 'email' => '[email protected]');
    $amysql->insert('tableName', $data);
} catch (AMysql_Exception $e) {
    if (AMysql_Exception::CODE_DUPLICATE_ENTRY === $e->getCode()) {
        // $e->getProps(0) === '[email protected]'
        if ('emailUniqueIndexName' === $e->getProps(1)) {
            $formError = 'A user with this email has already registered';
        } else {
            // code shouldn't get to here
            $formError = 'An unknown error has occured';
            trigger_error($e, E_USER_WARNING);
        }
    } else {
        $formError = 'An unknown error has occured';
        trigger_error($e, E_USER_WARNING);
    }
}

// with named parameters (do not use apostrophes)
$binds = array (
    'id' => 1,
    'state' => 'active'
);
$stmt = $amysql->query("SELECT * FROM tablename WHERE id = :id AND state = :state", $binds);
$results = $stmt->fetchAllAssoc();

// with unnamed parameters (never use apostrophes when binding values)
$binds = array (1, 'active');
$stmt = $amysql->query("SELECT * FROM tablename WHERE id = ? AND state = ?", $binds);
$results = $stmt->fetchAllAssoc();
$numRows = $stmt->numRows();

$binds = array (
    'id' => 1,
    'state' => 'active'
);
$stmt = $amysql->prepare("SELECT * FROM tablename WHERE id = :id AND state = :state");
$stmt->execute($binds);
$results = $stmt->fetchAllAssoc();

$select = $mysql->select();
$select 
    ->option('DISTINCT')
    ->option('SQL_CALC_FOUND_ROWS')
    ->from(array ('table1', 't2alias' => 'table2'))
    ->from(array ('t3alias' => 'table3'), array ('t3_col1' => 'col1', 't3_col2' => 'col2'))
    ->column('t2alias.*')
    ->column (array ('t1_col1' => 'table1.col1'))
    ->columnLiteral('table7, table8, CURRENT_TIMESTAMP AS ctimestamp')
    ->join(
        '',
        array ('t4alias' => 'table4'),
        't4alias.t1_id = table1.id',
        array ('t4lol', 't4lol2aliased' => 't4lol2')
    )
    ->join('left', array ('table5'), 't2alias.colx = table5.coly', array (), true)
    ->join('cross', array ('table6'), 't3alias.colx = table6.coly', array ())
    ->groupBy('t2alias.col1')
    ->groupBy('t2alias.col2', true, true)
    ->groupBy('t2alias.col3', true)
    ->having('1 = 1')
    ->having('2 = 2')
    ->orderBy('t3alias.col1')
    ->orderBy('t3alias.col2', true, true)
    ->orderBy('t3alias.col3', true)
    ->where('3 = :aBind')
    ->where("'yes' = :anotherBind")
    ->limit(100)
    ->offset(200)
;
$select->execute(array ('aBind' => 3, 'anotherBind' => 'yes'));
/*
SELECT DISTINCT `t3alias`.`col1` AS `t3_col1`, `t3alias`.`col2` AS `t3_col2`,
    `t2alias`.*, `table1`.`col1` AS `t1_col1`, `t4alias`.`t4lol`,
    `t4alias`.`t4lol2` AS `t4lol2aliased`,
    table7, table8, CURRENT_TIMESTAMP AS ctimestamp'
    FROM `table1`, `table2` AS `t2alias`, `table3` AS `t3alias`
    LEFT JOIN `table5` ON (t2alias.colx = table5.coly)
    JOIN `table4` AS `t4alias` ON (t4alias.t1_id = table1.id)
    CROSS JOIN `table6` ON (t3alias.colx = table6.coly)
    WHERE 3 = 3 AND 'yes' = 'yes'
    GROUP BY `t2alias`.`col2` DESC, `t2alias`.`col1`, `t2alias`.`col3` DESC
    HAVING 1 = 1 AND 2 = 2
    ORDER BY `t3alias`.`col2` DESC, `t3alias`.`col1`, `t3alias`.`col3` DESC
    LIMIT 100
    OFFSET 200
 */
$foundRows = $amysql->foundRows(); // resolves "SELECT FOUND_ROWS()" for "SQL_CALC_FOUND_ROWS"

$profiler = $amysql->getProfiler();

// ... execute a bunch of MySQL queries

$view->profiler = $profiler; // add it to your view.
$view->render('layout.phtml');

// layout.phtml:

<html>
    <head>
    </head>
    <body>