Usage of phpmysql connection mode pdo

Usage of phpmysql connection mode pdo

$ Dbh = new PDO ('MySQL: host = localhost; dbname = access_control ', 'root ',''); $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); $ Dbh-> exec ('set names utf8 '); /* Add */ // $ SQL = "INSERT INTO 'user' SET 'login' =: login AND 'password' =: password "; $ SQL = "INSERT INTO 'user' ('login', 'password') VALUES (: login,: password )"; $ stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (array (': login' => 'kevin2 ',': password '=> '')); Echo $ dbh-> lastinsertid (); /* Modify */ $ SQL = "UPDATE 'user' SET 'password' =: password WHERE 'user _ id' =: userId "; $ Stmt = $ dbh-> prepare ($ SQL ); $ Stmt-> execute (array (': userid' => '7',': password' => '4607e782c4d86fd5364d7e4248bb10d9 ')); Echo $ stmt-> rowCount (); /* Delete */ $ SQL = "DELETE FROM 'user' WHERE 'login' LIKE 'Kevin _ '"; // kevin % $ Stmt = $ dbh-> prepare ($ SQL ); $ Stmt-> execute (); Echo $ stmt-> rowCount (); /* Query */ $ Login = 'Kevin % '; $ SQL = "SELECT * FROM 'user' WHERE 'login' LIKE: login "; $ Stmt = $ dbh-> prepare ($ SQL ); $ Stmt-> execute (array (': login' => $ login )); While ($ row = $ stmt-> fetch (PDO: FETCH_ASSOC )){ Print_r ($ row ); } Print_r ($ stmt-> fetchAll (PDO: FETCH_ASSOC )); ?> 1. establish a connection $ Dbh = newPDO ('MySQL: host = localhost; port = 3306; dbname = test', $ user, $ pass, array ( PDO: ATTR_PERSISTENT => true )); ?> Persistent link PDO: ATTR_PERSISTENT => true 2. capture errors Try { $ Dbh = newPDO ('MySQL: host = localhost; dbname = test', $ user, $ pass ); $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); $ Dbh-> exec ("set character set utf8 "); $ Dbh = null; // disconnect } Catch (PDOException $ e ){ Print "Error! : ". $ E-> getMessage ()." "; Die (); } ?> 3. pdo transactions Try { $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); $ Dbh-> beginTransaction (); // start the transaction $ Dbh-> exec ("insertintostaff (id, first, last) values (23, 'job', 'bloggs ')"); $ Dbh-> exec ("insertintosalarychange (id, amount, changedate) Values (, NOW ())"); $ Dbh-> commit (); // submit the transaction } Catch (Exception $ e ){ $ Dbh-> rollBack (); // error rollBack Echo "Failed:". $ e-> getMessage (); } ?> 4. Error handling a. silent mode (default mode) Try { $ Dbh = new PDO ($ dsn, $ user, $ password ); $ SQL = 'select * from city where CountryCode =: country '; $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_WARNING ); $ Stmt = $ dbh-> prepare ($ SQL ); $ Stmt-> bindParam (': country', $ country, PDO: PARAM_STR ); $ Stmt-> execute (); While ($ row = $ stmt-> fetch (PDO: FETCH_ASSOC )){ Print $ row ['name']. "/t "; } } // If there is a problem we can handle it here Catch (PDOException $ e ){ Echo 'pdo Exception Caught .'; Echo 'error with the database: '; Echo 'SQL Query:', $ SQL; Echo 'Error: '. $ e-> getMessage (); } ?> 1. use query () $ Dbh-> query ($ SQL); when $ SQL contains variables, you can use $ dbh-> quote ($ params); // escape string data $ SQL = 'select * from city where CountryCode = '. $ dbh-> quote ($ country ); Foreach ($ dbh-> query ($ SQL) as $ row ){ Print $ row ['name']. "/t "; Print $ row ['countrycode']. "/t "; Print $ row ['population']. "/n "; } ?> 2. use prepare, bindParam, and execute [recommended. you can also use add, modify, or delete] $ Dbh-> prepare ($ SQL); generates a PDOStatement object. PDOStatement-> bindParam () PDOStatement-> execute (); // you can put the bound variable here. ?> 3. php pdo transaction example Try { $ Dbh = new PDO ('MySQL: host = localhost; dbname = test', 'root ',''); $ Dbh-> query ('set names utf8 ;'); $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); $ Dbh-> beginTransaction (); $ Dbh-> exec ("Insert INTO 'test'. 'Table' ('name', 'age') VALUES ('Mick', 22 );"); $ Dbh-> exec ("Insert INTO 'test'. 'Table' ('name', 'age') VALUES ('Lily', 29 );"); $ Dbh-> exec ("Insert INTO 'test'. 'Table' ('name', 'age') VALUES ('Susan ', 21 );"); $ Dbh-> commit (); } Catch (Exception $ e ){ $ Dbh-> rollBack (); Echo "Failed:". $ e-> getMessage (); } ?>