Common methods of PHP operating MySQL database

A. mysql database

The code is as follows

Copy Code

<?php $conn =mysql_connect ("localhost", "root", "123456") or Die ("database connection failed"); mysql_select_db ("test") or Die ("Select Database Failed");; $sql = "SELECT * from user"; $data =mysql_query ($sql); Echo ' <table border= "1" align= "center" width= ">" while ($row =mysql_fetch_assoc ($result)) { Echo ' <tr> '; foreach ($row as $col) { Echo ' <td> '. $col. ' </td> '; } Echo ' </tr> '; }; Echo ' </table> '; Mysql_close ();

Add

The code is as follows

Copy Code

An SQL statement that extracts information from a table $strsql = "SELECT * from ' Gbook '"; Execute SQL query $result =mysql_db_query ($mysql _database, $strsql, $conn); Get query Results $row =mysql_fetch_row ($result); Echo ' <font face= ' Verdana ' > '; Echo ' <table border= "1" cellpadding= "1" cellspacing= "2" > "; Display field names echo "</b><tr></b>"; for ($i =0; $i <mysql_num_fields ($result); $i + +) { Echo ' <td bgcolor= ' #000F00 ' ><b> '. Mysql_field_name ($result, $i); echo "</b></td></b>"; } echo "</tr></b>"; Navigate to the first record Mysql_data_seek ($result, 0); Looping out Records while ($row =mysql_fetch_row ($result)) { echo "<tr></b>"; for ($i =0; $i <mysql_num_fields ($result); $i + +) { Echo ' <td bgcolor= ' #00FF00 ' > '; echo $row [$i]; Echo ' </td> '; } echo "</tr></b>"; } echo "</table></b>"; echo "</font>"; Releasing resources Mysql_free_result ($result); Close connection Mysql_close ($conn);

Two. Pdo_mysql (recommended)

Connection:

The code is as follows

Copy Code

$DSN = "Mysql:host=localhost;dbname=test"; $username = ' root '; $password = ' 123456 '; $options = Array ( Pdo::mysql_attr_init_command => ' SET NAMES UTF8 ', ); $pdo = new PDO ($DSN, $username, $password, $options); $pdo->setattribute (Pdo::attr_errmode, pdo::errmode_exception); Increase: Method 1: Binding associative arrays $str = $pdo->prepare (INSERT into ' user ' (' username ', ' password ') VALUES (: username,:p assword) "); $str->execute (Array (": Username" => "test", ":p assword" => "passwd")); Method 2: Binding index Array $str = $pdo->prepare (INSERT into ' user ' (' username ', ' password ') VALUES (?,?) "); $str->execute (Array ("Test", "passwd")); By deleting: $str = $pdo->prepare ("Delete from user where ID > 3"); $str->execute (); Change: $str = $pdo->prepare ("UPDATE ' user ' SET username=:username,password=:p assword where Id=:id"); $str->execute (Array (": Username" => "test", ":p assword" => "passwd", ": id" => "3")); Check: Method 1: Single fetch, loop traversal, return to array $str = $pdo->prepare ("select * from user where ID >: ID ORDER by id"); $str->execute (Array (": id" =>2)); $str->setfetchmode (PDO::FETCH_ASSOC); Total three kinds: 1.pdo::fetch_both (default) 2.fetch_assoc 3.ftech_num while ($data = $str->fetch ()) { Print_r ($data); Echo ' <br> '; } Method 2: All out, back to the two-dimensional array $str = $pdo->prepare ("SELECT * from User order by FID"); $str->execute (); $data = $str->fetchall (pdo::fetch_num); Print_r ($data); Method 3: Single fetch, loop traversal, bound field name to variable $str = $pdo->prepare ("Select Fid,username,password from User ID"); $str->execute (); $str->bindcolumn ("id", $id); $str->bindcolumn ("username", $username); $str->bindcolumn (3, $password); while ($str->fetch ()) { echo "$id | $username | $password <br> "; } echo "Total number of records:". $str->rowcount (). " <br> "; Echo Total number of fields:. $str->columncount (). " <br> ";

two. Mysqli

The code is as follows

Copy Code

(link MySQL database with mysqli) Requery_once ("config.ini.php"); $MYSQLIOBJ = new Mysqli ($dbhost, $dbuser, $dbpwd, $dbname); if (Mysqli_connect_errno ()) { echo "Connection Failed". Mysqli_connect_error (); Exit (); } $MYSQLIOBJ->query ("Set name $charName"); (Other actions)   Query ----------------------------------------------------- (single query) $sql = Drop table  if exists user; "; $MYSQLIOBJ->query ($sql); &NBSP (multiple queries) $MUSQLIOBJ->multip_query ($sql)       Returns the number of rows affected by the execution $sql () ---------------------------------------------------- if ($mysqliObj->query ($sql)) Echo $MYSQLIOBJ- >affected_rows;   Insert, returns the inserted ID (useful) ---------------------------------------------------   $num = $ mysqliobj->insert_id; <?php $mysqli =new mysqli ("localhost", "root", "123456", "Test"); $sql = "SELECT * from user order by id"; $result = $mysqli->query ($sql); Echo ' <table align= "center" border= "1" width= ">"; while ($row = $result->fetch_assoc ()) { Echo ' <tr> '; foreach ($row as $col) { Echo ' <td> '. $col. ' </td> '; } Echo ' </tr> '; } Echo ' </table> '; $mysqli->close ();

Three kinds of processing query results
-------------------------------------------------------

code is as follows

copy code

$sql =" SELECT * from user ";   $result = $mysqli->query ($sql);   (1) fetch_row ()    return index array Fetch_row () while ($id, $name, $pwd) = $result->fetch_ Row ()) { Echo ID: ". $id." Name: ". $name."   PWD: ". $pwd." <br> ";   } (2) Fetch_assoc ()   return associative array Fetch_assoc () while ($row = $result->fetch_assoc ()) {  echo "ID:". $row ["UserId"]. "Name:". $row ["UserName"]. "   PWD: ". $row [" Password "]." <br> "; }   (3) Fetch_object () returns object while ($row = $result->fetch_object ()) { Echo ID:. $row-> UserId. "Name:". $row->uername. "   PWD: ". $row->password." <br> ";   }