Yii2-05 How to manipulate a database

Preparing the database

DROP TABLE IF EXISTS ' pre_user ';
CREATE TABLE ' pre_user ' (
    ' id ' int () auto_increment PRIMARY KEY,
    ' username ' varchar (255) Not  NULL,
    '  Password ' varchar NOT null default ',
    ' password_hash ' varchar (255) NOT null default ',
    ' email ' varchar (255) Not null default ',
    ' status ' smallint (6) is not null default,
    ' Created_at ' smallint (6) is not NULL default 0,
    ' Updated_at ' smallint (6) not NULL default 0
) engine=innodb default charset=utf8mb4;

Configuring Connections

config\db.php

<?php return

[
    ' class ' => ' yii\db\connection ',
    ' DSN ' => ' Mysql:host=localhost;dbname=yii2 ',
    ' username ' => ' root ', '
    password ' => ' root ',
    ' charset '
    => ' utf8mb4 ', ' Tableprefix ' => ' Pre_ '
];

To see if the database connection was successful
In the controller print:

Var_dump (\yii:: $app->db);

How to execute the SQL statement.

Delete and change

Receive the data for the form
 $username = ' Jack ';

 $sql = "INSERT into {{%user}}} (Username,status) VALUES (: Username,:status)";

 Returns the number of affected rows
 $row = \yii:: $app->db->createcommand ($sql, [' username ' => $username, ' status ' =>8])-> Execute ();

 Get the self-increasing ID
 echo \yii:: $app->db->getlastinsertid ();

Inquire

$sql = "Select * FROM {%user}} WHERE id>:id";

The query result is a two-dimensional array
$USERARR = \yii:: $app->db->createcommand ($sql, [' ID ' =>1])->queryall ();


If you are querying a
$user = \yii:: $app->db->createcommand ($sql, [' ID ' =>1])->queryone ();

If you want to return a single value
//For example, select COUNT (*) statement
$count = \yii:: $app->db->createcommand ($sql, [' ID ' =>1])-> Queryscalar ();