PHP and MySQL databases, PHP supports many databases, and MySQL is a powerful combination of mysql. The basic knowledge of MySQL database is necessary to understand how to operate MySQL database, data table methods.
What is a database, what can a database do, what are the benefits of a database, the basic prerequisites for a database, and the methods for backup and recovery.
mysql has the advantages of powerful, cross-platform support, fast running, object-oriented support, low cost, support for various development languages, large database storage capacity, and support for powerful built-in functions.
Start MySQL Server
net start mysql
Connect to the database:
mysql –u root –h127.0.0.1 –p password
Disconnect MySQL server:
quit;
Stop the MySQL server:
net stop mysql; mysqladmin –uroot shutdown –proot
Operation of the database:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] Data Table Name [(create_definition,…)][table_options] [select_statement]
Temporary means to create a temporary table if not exists indicates whether the table already exists Some features of the create_definition table select_statement Quick Create Table
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY ] [reference_definition]
create table table_name (Column Name 1 Property,Column Name 2 Attribute...);col_name field name Type field type not null | null indicates whether the column allows control Default stands for default auto_increment indicates whether auto-numbering is automatic Primary key indicates whether or not it is the primary key A table can only have one primary key. If there is no primary key in the table, mysql returns the first unique key without any null columns as the primary key. reference_definition adds a comment to the field
create table tb_admin ( id int auto_increment primary key, user varchar(30) not null, password varchar(30) not null, createtime datetime );
View table structure:
SHOW [FULL] COLUMNS FROM data table name [FROM database name]; DESCRIBE data table name;
Modify table structure:
ALTER[IGNORE] TABLE data table name alter_spec[,alter_spec]...
Rename table:
RENAME TABLE Datasheet Name 1 To Datasheet Name 2
Delete table:
DROP TABLE data table name;
drop table if exists data table name;
Operation of database: insert resolved insert, query select, modify update, delete record.
Insert record insert
insert into Data Table Name(column_name,column_name2, … ) values (value1, value2, … )
Query database:
select selection_llist from Data Table Name where primary_constraint group by grouping_columns order by sorting_columns having secondary_constraint limit count
Table 1. Fields = Table 2. Fields and other query conditions Selectect field names from Table 1, Table 2...where Table 1. Field=Table 2. Field and other query conditions update data table name set column_name = new_value1,column_name2 = new_value2,...where condition delete from data table name where condition
Database backup and recovery:
Use the MYSQLDUMP command to back up your data.
mysql -uroot –proot db_database <F:\db_database.txt"
php operation database
mysql_connect() function connects mysql server mysql_select_db() function selects database mysql_query() function executes sql statement The mysql_fetch_array() function gets information from an array result set The mysql_fetch_row() function gets each record in the result set row by row The mysql_num_rows() function gets the number of records in the query result set insert dynamic add select statement query update dynamic modification Delete dynamic delete
MySQL is a popular database
Open Source Semi-Commercial Software
High market share
PHP has powerful database support
Query, Display, Insert, Update, Delete
Close MySQL Server
Each time the mysql_connect() or mysql_query() function is used, system resources are consumed.
Use the mysql_close() function to close the connection to the MySQL server to save system resources.
mysql_close($Link);
<?php
$link = mysql_connect("localhost", "root", "root") or die("Wrong username password!".mysql_error()); //Connect to Mysql Server
if($link){
echo "Successful data source connection!";
}
?>
// mysql_connect('hostname','username','password');<?php
$link = mysql_connect("localhost", "root", "root") or die("Wrong username password!".mysql_error()); //Connect to Mysql Server
$db_selected=mysql_select_db("db_database1",$link);
//$db_selected=mysql_query("use db_database1",$link);
if($db_selected){
echo "Database Selection Successful!";
}
?>
// Mysql_select_db (string database name [, resource link_identifier])
// mysql_query("use database name" [, resource link_identifier]);<?php
$db = array (
'server' => 'localhost',
'port' => '3306',
'username' => 'root',
'password' => 'root',
'database' => 'dashu'
);
$conn = @mysql_connect($db['server'].':'.$db['port'],$db['username'],$db['password']);
if (! $conn) {
echo "Server not connected!" . mysql_error();
} else {
// Declare Character Set
mysql_set_charset('utf8', $conn);
// Select Database
mysql_select_db($db['database'], $conn);
}<?php
$link = mysql_connect("localhost", "root", "root") or die("Database Connection Failure".mysql_error());
mysql_select_db("db_database",$link);
mysql_query("set names gb2312");
$sql=mysql_query("select * from tb_book");
$info= mysql_fetch_array($sql);
if($_POST[Submit] == "query"){
$tet_book = $POST[txt_book];
$sql = mysql_query("select * from tb_book where bookname like '%".trim($txt_book)."%' ");
$info=mysql_fetch_array($sql);
}The mysql_fetch_array() function gets information from the array result set:
array mysql_fetch_array ( resource result [, int result_type] )
The mysql_fetch_object() function takes a row from the result set as an object
object mysql_fetch_object ( resource result )
object
<?php echo $info -> id; ?></td>
<?php echo $info -> issuDate; ?></td>
<?php echo $info -> first_name; ?></td>
//array
<?php echo $info[id]; ?></td>
do{
}while($info=mysql_fetch_array($sql));The mysql_fetch_row() function gets each record in the result set row by row
array mysql_fetch_row ( resource result )
The mysql_num_rows() function gets the number of records in the query result set
int mysql_num_rows ( resource result )
<?php $nums = mysql_num_rows($sql); echo $nums; ?>
PHP operates on MySQL databases
<?php
function chinesesubstr($str,$start,$len) {
$strlen=$start+$len;
for($i=0;$i<$strlen;$i++) {
if(ord(substr($str,$i,1))>0xa0) {
$tmpstr.=substr($str,$i,2);
$i++;
}
else
$tmpstr.=substr($str,$i,1);
}
return $tmpstr;
}
?>Example:
onClick="return check(form1);"
<script language="javascript">
function check(form){
if(form.txt_title.value==""){
alert("Please enter a bulletin title!");form.txt_title.focus();return false;
}
if(form.txt_content.value==""){
alert("Please enter the announcement!");form.txt_content.focus();return false;
}
form.submit();
}
</script><?php
$conn=mysql_connect("localhost","root","root") or die("Database Server Connection Error".mysql_error());
mysql_select_db("db_database18",$conn) or die("Database Access Error".mysql_error());
mysql_query("set names gb2312");
$title=$_POST[txt_title];
$content=$_POST[txt_content];
$createtime=date("Y-m-d H:i:s");
$sql=mysql_query("insert into tb_affiche(title,content,createtime)values('$title','$content','$createtime')");
echo "<script>alert('Successful addition of announcement information!');window.location.href='add_affiche.php';</script>";
mysql_free_result($sql);
mysql_close($conn);
?>
<script language="javascript">
function check(form){
if(form.txt_keyword.value==""){
alert("Please enter a query keyword!");form.txt_keyword.focus();return false;
}
form.submit();
}
</script>
<?php
function chinesesubstr($str,$start,$len) {
$strlen=$start+$len;
for($i=0;$i<$strlen;$i++) {
if(ord(substr($str,$i,1))>0xa0) {
$tmpstr.=substr($str,$i,2);
$i++;
}
else
$tmpstr.=substr($str,$i,1);
}
return $tmpstr;
}
?>update statement dynamic
<script language="javascript">
function check(form){
if(form.txt_title.value==""){
alert("Announcement Title cannot be empty!");form.txt_title.focus();return false;
}
if(form.txt_content.value==""){
alert("Notice content cannot be empty!");form.txt_content.focus();return false;
}
form.submit();
}
</script>
<?php
$conn=mysql_connect("localhost","root","root") or die("Database Server Connection Error".mysql_error());
mysql_select_db("db_database18",$conn) or die("Database Access Error".mysql_error());
mysql_query("set names gb2312");
$id=$_GET[id];
$sql=mysql_query("select * from tb_affiche where id=$id");
$row=mysql_fetch_object($sql);
?>
<form name="form1" method="post" action="check_modify_ok.php">
<table width="520" height="212" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
<tr>
<td width="87" align="center">Announcement Subject:</td>
<td width="433" height="31"><input name="txt_title" type="text" id="txt_title" size="40" value="<?php echo $row->title;?>">
<input name="id" type="hidden" value="<?php echo $row->id;?>"></td>
</tr>
<tr>
<td height="124" align="center">Notice content:</td>
<td><textarea name="txt_content" cols="50" rows="8" id="txt_content"><?php echo $row->content;?></textarea></td>
</tr>
<tr>
<td height="40" colspan="2" align="center"><input name="Submit" type="submit" class="btn_grey" value="modify" onClick="return check(form1);"> <input type="reset" name="Submit2" value="Reset"></td></tr>
</table>
</form>
<?php
$conn=mysql_connect("localhost","root","root") or die("Database Server Connection Error".mysql_error());
mysql_select_db("db_database18",$conn) or die("Database Access Error".mysql_error());
mysql_query("set names gb2312");
$title=$_POST[txt_title];
$content=$_POST[txt_content];
$id=$_POST[id];
$sql=mysql_query("update tb_affiche set title='$title',content='$content' where id=$id");
if($sql){
echo "<script>alert('Announcement information edited successfully!');history.back();window.location.href='modify.php?id=$id';</script>";
}else{
echo "<script>alert('Editing announcement information failed!');history.back();window.location.href='modify.php?id=$id';</script>";
}
?>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<!-- Page flip bar -->
<td width="37%"> Page number:<?php echo $_GET[page];?>/<?php echo $page_count;?>page Record:<?php echo $message_count;?> strip </td>
<td width="63%" align="right">
<?php
/* If the current page is not the home page */
if($_GET[page]!=1){
/* Show Home hyperlink */
echo "<a href=page_affiche.php?page=1>home page</a> ";
/* Show Previous Page Hyperlink */
echo "<a href=page_affiche.php?page=".($_GET[page]-1).">Previous page</a> ";
}
/* If the current page is not the last page */
if($_GET[page]<$page_count){
/* Show Next Page Hyperlink */
echo "<a href=page_affiche.php?page=".($_GET[page]+1).">next page</a> ";
/* Show "End Page" Hyperlink */
echo "<a href=page_affiche.php?page=".$page_count.">End Page</a>";
}
mysql_free_result($sql);
mysql_close($conn);
?>Edit:
<?php
$conn=mysql_connect("localhost","root","root") or die("Database Server Connection Error".mysql_error());
mysql_select_db("db_database18",$conn) or die("Database Access Error".mysql_error());
mysql_query("set names gb2312");
$title=$_POST[txt_title];
$content=$_POST[txt_content];
$id=$_POST[id];
$sql=mysql_query("update tb_affiche set title='$title',content='$content' where id=$id");
if($sql){
echo "<script>alert('Announcement information edited successfully!');history.back();window.location.href='modify.php?id=$id';</script>";
}else{
echo "<script>alert('Editing announcement information failed!');history.back();window.location.href='modify.php?id=$id';</script>";
}
?>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312"><?php
$conn=mysql_connect("localhost","root","root") or die("Database Server Connection Error".mysql_error());
mysql_select_db("db_database18",$conn) or die("Database Access Error".mysql_error());
mysql_query("set names gb2312");
$title=$_POST[txt_title];
$content=$_POST[txt_content];
$id=$_POST[id];
$sql=mysql_query("update tb_affiche set title='$title',content='$content' where id=$id");
if($sql){
echo "<script>alert('Announcement information edited successfully!');history.back();window.location.href='modify.php?id=$id';</script>";
}else{
echo "<script>alert('Editing announcement information failed!');history.back();window.location.href='modify.php?id=$id';</script>";
}
?>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">Practice:
<html>
<head>
<title>Journalism</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link href="css/style.css" rel="stylesheet">
</head>
<body>
<?php
function chinesesubstr($str,$start,$len) {
$strlen=$start+$len;
for($i=0;$i<$strlen;$i++) {
if(ord(substr($str,$i,1))>0xa0) {
$tmpstr.=substr($str,$i,2);
$i++;
}
else
$tmpstr.=substr($str,$i,1);
}
return $tmpstr;
}
?>
<table width="600" height="100" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFCC">
<tr>
<td width="600" height="257" align="center" valign="top" background="images/image_08.gif"><table width="579" height="271" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="579" height="50" align="center" class="word_orange"><span class="style1">Hot news</span></td>
</tr>
<tr>
<td height="249" align="center" valign="top"> <table width="460" border="1" align="center" cellpadding="1" cellspacing="1" bordercolor="#FFFFCC" bgcolor="#DFDFDF">
<?php
$conn=mysql_connect("localhost","root","root") or die("Database Server Connection Error".mysql_error());
mysql_select_db("db_database18",$conn) or die("Database Access Error".mysql_error());
mysql_query("set names gb2312");
$sql=mysql_query("select * from tb_affiche order by createtime desc limit 0,10");
$info=mysql_fetch_array($sql);
if($info==false){
echo "No announcement information available on this site!";
}
else{
$i=0;
do{
?>
<tr bgcolor="#E3E3E3">
<td height="24" align="left" bgcolor="#FFFFFF">
<?php
$i=$i+1;
echo $i.",".chinesesubstr($info[title],0,40);
if(strlen($info[title])>40){
echo "...";
}
?>
<em> [<?php echo $info[createtime];?>]</em> </td>
</tr>
<?php
}while($info=mysql_fetch_array($sql));
}
mysql_free_result($sql); //Close Recordset
mysql_close($conn); //Close MySQL database server
?>
</table></td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>Encapsulate Class Database Connection, Operation, Paging, String Interception
<?php
//Database Connection Class
class ConnDB{
var $dbtype;
var $host;
var $user;
var $pwd;
var $dbname;
var $conn;
function ConnDB($dbtype,$host,$user,$pwd,$dbname){ //Construction method, assigning values to member variables
$this->dbtype=$dbtype;
$this->host=$host;
$this->user=$user;
$this->pwd=$pwd;
$this->dbname=$dbname;
}
function GetConnId(){ //Implement a connection to the database and return the connection object
$this->conn=mysql_connect($this->host,$this->user,$this->pwd) or die("Database Server Connection Error".mysql_error());
mysql_select_db($this->dbname,$this->conn) or die("Database Access Error".mysql_error());
mysql_query("set names gb2312"); //Set encoding format for database
return $this->conn; //Return Connection Object
}
function CloseConnId(){ //Define how to close the database
$this->conn->Disconnect(); //Perform a close operation
}
}
//Database Management Class
class AdminDB{
function ExecSQL($sqlstr,$conn){ //Define methods, parameters for SQl statements and objects returned by connecting databases
$sqltype=strtolower(substr(trim($sqlstr),0,6)); //Intercepts the first six strings in SQL and converts them to lowercase
$rs=mysql_query($sqlstr); //Execute SQL statement
if($sqltype=="select"){ //Determine if the type of SQL statement is SELECT
$array=mysql_fetch_array($rs); //Execute the statement to get the query results
if(count($array)==0 || $rs==false) //Determine whether the statement was executed successfully
return false; //Returns false if query result is 0 or execution fails
else
return $array; //Otherwise returns an array of query results
}elseif ($sqltype=="update" || $sqltype=="insert" || $sqltype=="delete"){
//Determine if the SQL statement type is not select, do the following
if($rs)
return true; //Execution successfully returned true
else
return false; //Whether to return false
}
}
}
//Paging Class
class SepPage{
var $rs;
var $pagesize; //Define the number of records displayed per page
var $nowpage; //CurrentPage
var $array;
var $conn;
var $sqlstr; //Executed SQL statement
var $total;
var $pagecount; //Total number of records
function ShowDate($sqlstr,$conn,$pagesize,$nowpage){ //Definition method
$arrays=array();
$array_title=array();
$array_content=array();
if(!isset($nowpage) || $nowpage=="" || $nowpage==0) //Determine if the current page variable value is empty
$this->nowpage=1; //Define the value of the current page
else
$this->nowpage=$nowpage; //Get the value of the current page
$this->pagesize=$pagesize; //Define the number of records output per page
$this->conn=$conn; //Identity returned from connection database
$this->sqlstr=$sqlstr; //Query statement executed
$this->pagecount=$pagecount; //Total number of records
$this->total=$total; //Total number of records
$this->rs=mysql_query($this->sqlstr."limit ".$this->pagesize*($this->nowpage-1).",$this->pagesize",$this->conn);
$this->total=mysql_num_rows($this->rs); //Get Number of Records
if($this->total==0){ //Judge that if the query result is 0, the output is as follows
return false;
}else{ //otherwise
if(($this->total % $this->pagesize)==0){ //Determine if the total number of records divided by the number of records displayed per page equals 0
$this->pagecount=intval($this->total/$this->pagesize); //Assign a value to the variable pagecount
}else if($this->total<=$this->pagesize){
$this->pagecount=1;//If the query result is less than or equal to the number of records per page, assign a value of 1 to the variable
}else{
$this->pagecount=ceil($this->total/$this->pagesize); //Otherwise output variable value
}
while($this->array=mysql_fetch_array($this->rs)){
array_push($array_title,$this->array[title]);
array_push($array_content,$this->array[content]);
}
array_push($arrays,$array_title,$array_content);
return $arrays;
}
}
function ShowPage($contentname,$utits,$anothersearchstr,$class){
$allrs=mysql_query($this->sqlstr,$this->conn); //Execute Query Statement
$record=mysql_num_rows($allrs);
$pagecount=ceil($record/$this->pagesize); //Calculate total pages
$str.="Share".$contentname." ".$record." ".$utits." Display per page ".$this->pagesize." ".$utits." No. ".$this->nowpage." page/common ".$pagecount." page";
$str.=" ";
$str.="<a href=".$_SERVER['PHP_SELF']."?page=1".$anothersearchstr." class=".$class.">home page</a>";
$str.=" ";
if(($this->nowpage-1)<=0){
$str.="<a href=".$_SERVER['PHP_SELF']."?page=1".$anothersearchstr." class=".$class.">Previous page</a>";
}else{
$str.="<a href=".$_SERVER['PHP_SELF']."?page=".($this->nowpage-1).$anothersearchstr." class=".$class.">Previous page</a>";
}
$str.=" ";
if(($this->nowpage+1)>=$pagecount){
$str.="<a href=".$_SERVER['PHP_SELF']."?page=".$pagecount.$anothersearchstr." class=".$class.">next page</a>";
}else{
$str.="<a href=".$_SERVER['PHP_SELF']."?page=".($this->nowpage+1).$anothersearchstr." class=".$class.">next page</a>";
}
$str.=" ";
$str.="<a href=".$_SERVER['PHP_SELF']."?page=".$pagecount.$anothersearchstr." class=".$class.">End Page</a>";
if(count($this->array)==0 || $this->rs==false)
return "";
else
return $str;
}
}
//System Common Methods
class UseFun{
function chinesesubstr($str,$start,$len) {
$strlen=$start+$len;
for($i=0;$i<$strlen;$i++) {
if(ord(substr($str,$i,1))>0xa0) {
$tmpstr.=substr($str,$i,2);
$i++;
}
else
$tmpstr.=substr($str,$i,1);
}
return $tmpstr;
}
}
?><?php
require("system..php");
//Database Connection Class Instantiation
$connobj=new ConnDB("mysql","localhost","root","root","db_database");
$conn=$connobj->GetConnId();
//Database Action Class Instantiation
$admindb=new AdminDB();
//Paging Class Instantiation
$seppage=new SepPage();
//String Interception Class
$unhtml=new UseFun();
?>afterword
Okay, please leave a message in the message area to share your experience and experience.
Thank you for learning today. If you find this article helpful, you are welcome to share it with more friends. Thank you.
Thank!Thank you!Your sincere appreciation is my greatest motivation to move forward!
