Common methods of PHP operating MySQL database

Source: Internet
Author: User
Tags foreach dsn prepare 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> ";
 
}
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.