Mysql-PHP(PDO)

delimiter //

mysql> create procedure getall2() begin select * from user; end//

delimiter ;

drop procedure test1;

delimiter //

Create procedure:

mysql> create procedure test1(a text) begin insert into gender (name) values (a); end //

delimiter //

mysql> create trigger trup before update on user for each row begin insert into logs(name, date_created) values ("update", TIMESTAMP(CURRENT_DATE())); end; //

delimetr ;

DELIMITER |

CREATE FUNCTION sf_past_movie_return_date (return_date DATE)

  RETURNS VARCHAR(3)

   DETERMINISTIC

    BEGIN

     DECLARE sf_value VARCHAR(3);

        IF curdate() > return_date

            THEN SET sf_value = 'Yes';

        ELSEIF  curdate() <= return_date

            THEN SET sf_value = 'No';

        END IF;

     RETURN sf_value;

    END|

Create user

create user 'mark'@'localhost' identifield by '12345678';

grant select, update on person.* to 'mark'@'localhost';

revoke select, update on person.* from 'mark'@'localhost';

flush privileges;

mysql> delimiter //

mysql> create trigger up_tr before update on users for each row begin insert into logs(action, user_name, in_date) values ("update", session_user(), current_timestamp); end; //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

Создание подключения к БД 

try {

    $dbh = new PDO('mysql:host=********;port=****;dbname=*******', 'root', '**************');

    } catch (PDOException $e) {

    print "Error!: " . $e->getMessage() . "<br/>";

    die();

    }

Простой вывод результата из БД

$stm=$dbh->query("select * from table");

while($row=$stm->fetch()){

    echo $row[1].'<br>';

}

Подготовленные запросы к БД

$dbh = new PDO('mysql:host=********;port=****;dbname=*******', 'root', '**************');

$smt = $dbh->prepare("insert into users (name, value) values (:name, :age)");

$smt->bindParam(':name', $name);

$smt->bindParam(':value', $value);

$name='olga';

$value=18;

$smt->execute();

$smt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");

$smt->bindParam(1, $name);

$smt->bindParam(2, $value);

$name = 'one';

$value = 1;

$smt->execute();

$smt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");

$smt->execute([$_GET['name']]);

foreach ($smt as $row) {

  print_r($row);

}