Because of its small size, fast speed and low overall cost of ownership, especially with the characteristics of open source, many small and medium-sized websites choose mysql as the website database in order to reduce the overall cost of ownership. The solution of database system using mysql database management system and PHP scripting language is being adopted by more and more websites, among which LAMP(linux+apche+mysql+php) mode is the most popular one.
PHP has standard functions to manipulate databases. Mysqli is a new addition to PHP 5 and an improvement on mysql extensions. However, due to historical problems, many old projects are developed using mysql in PHP4. If the original project is redeveloped, the mysql expansion function is required. If it is a newly designed project, mysqli extension or PDO technology is recommended. This paper mainly introduces mysql extension function in PHP.
Omnibus
Several steps of operating MySQL database in PHP script are as follows:
1. Connect to MySQL database server and determine whether the connection is correct
2. Select the database and set the character set (optional)
3. Executing SQL commands
4. Processing result sets
5. Close database connection
Connect to MySQL database server and determine if the connection is correct
mysql_connect()
The mysql_connect() function is used to open a connection to the MySQL server. Return a resource if successful, or FALSE if failed
resource mysql_connect ([ string $server [, string $username [, string $password [, bool $new_link [, int $client_flags ]]]]] )
mysql_errno()
The mysql_errno() function is used to return the digital encoding of error information in the previous MySQL operation
int mysql_errno ([ resource $link_identifier ] )
mysql_error()
The mysql_error() function is used to return the text error information generated by the previous MySQL operation. If no connection resource number is specified, the error message is extracted from the MySQL server using the last successfully opened connection
string mysql_error ([ resource $link_identifier ] )
<?php$link = mysql_connect('localhost','root','123456');var_dump($link);//Resource (3, MySQL link) if (!$link) {die ('failed connection:'. mysql_error());
}?>
Select the database and set the character set (optional)
Usually, the creation of a database is first created by a database administrator (DBA) and then used by PHP programmers in scripts. For example, create a database called bookstore
After establishing a connection with MySQL server using PHP script, in order to avoid specifying the target database for each call to the MySQL extension function of PHP, it is better to select a default database for subsequent operations using the mysql_select_db() function, which is similar to the function of the SQL command "USE bookstore"
mysql_select_db()
The mysql_select_db() function is used to select the MySQL database
bool mysql_select_db ( string $database_name [, resource $ link_identifier ] )
<?php$link = mysql_connect('localhost','root','zhiaihebe0123');var_dump($link);//Resource (3, MySQL link) if (!$link) {die ('failed connection:'. mysql_error());
}mysql_select_db('bookstore',$link) or die('Unable to select database bookstore:' .mysql_error());mysql_query('set names utf8');//Setting Character Sets (Usually Not Used)?>
Execute SQL commands
First, create a book table in the books tore database
CREATE TABLE books( id INT NOT NULL AUTO_INCREMENT, bookname VARCHAR(80) NOT NULL DEFAULT '', publisher VARCHAR(60) NOT NULL DEFAULT '', author VARCHAR(20) NOT NULL DEFAULT '', price DOUBLE(5,2) NOT NULL DEFAULT 0.00, ptime INT NOT NULL DEFAULT 0, pic CHAR(24) NOT NULL DEFAULT '', detail TEXT, PRIMARY KEY(id)); ));
In PHP, as long as the SQL command is passed as a string to the mysql_query() function, it is sent to the MYSQL server and executed.
mysql_query()
The mysql_query() function is used to send a MySQL query. Mysql_query() returns only one resource to SELECT, SHOW, DESCRIBE, EXPLAIN and other statements, FALSE if the query errors occur; for other types of SQL statements, such as INSERT, UPDATE, DELETE, DROP, mysql_query() returns TRUE upon successful execution and FALSE upon error.
resource mysql_query ( string $query [, resource $link_identifier = NULL ] )
The three INSERT statements to be inserted are declared as a string
$insert = "insert into books(bookname, publisher, author, price, detail) values
('PHP','Electronic Industry Press','Zhang San','80.00','PHP Relevant'),
('ASP','Electronic Industry Press','Li Si','90.00','ASP Relevant'),
('JSP','Electronic Industry Press','Wang Wu','70.00','JSP Relevant')";Send an INSERT statement using the mysql_query() function, and return false if true is returned successfully and false if failed
$result = mysql_query($insert);var_dump($result);
mysql_affected_rows()
The mysql_affected_rows() function is used to get the number of rows of records affected by the previous MySQL operation. Successful execution returns the number of rows affected, and if the last query failed, the function returns - 1
int mysql_affected_rows ([ resource $link_identifier = NULL ] )
var_dump(mysql_affected_rows());//int3
The success of data manipulation is usually determined by determining whether the value of mysql_affected_rows() function is greater than 0.
mysql_insert_id()
The mysql_insert_id() function is used to obtain the ID generated by the previous INSERT operation
int mysql_insert_id ([ resource $link_identifier ] )
<?php$insert = "insert into books(bookname, publisher, author, price, detail) values
('PHP','Electronic Industry Press','Zhang San','80.00','PHP Relevant'),
('ASP','Electronic Industry Press','Li Si','90.00','ASP Relevant'),
('JSP','Electronic Industry Press','Wang Wu','70.00','JSP Relevant')";$result = mysql_query($insert);if($result && mysql_affected_rows() > 0){ //After refreshing the page twice, it is equivalent to inserting data twice. The page shows that the insertion of the data record was successful, and the last insertion of the data record id is:4
echo "The data record was inserted successfully, and the last inserted data record id by:".mysql_insert_id()."<br>";
}else{ //If the data table is deleted, the insertion failure of the data record is displayed. Error number: 1146, error reason: La table'bookstore. books'n'existe pas
echo "Data record insertion failed, error number:".mysql_errno()."，Reasons for error:".mysql_error()."<br>";
}?>
In fact, the last id should be 6, but since the statements 4, 5, and 6 are inserted at the same time, the first id should be 4.
Next, change the author of the record with id 4 to Xiaobai
$result = mysql_query("UPDATE books SET author='Xiao Bai' WHERE id='4'");if($result && mysql_affected_rows() > 0){ echo "Successful modification of data records<br>";
}else{ echo "Data record modification failed, error number:".mysql_errno()."，Reasons for error:".mysql_error()."<br>";
}
Next, delete the author's record as Li Si
$result = mysql_query("DELETE FROM books WHERE author='Li Si'");if($result && mysql_affected_rows() > 0){ echo "Successful deletion of data records<br>";
}else{ echo "Data record deletion failed, error number:".mysql_errno()."，Reasons for error:".mysql_error()."<br>";
}
Processing result sets
Executing SELECT query command in PHP script also calls mysql_query() function, but different from executing DML, after executing SELECT command, the return value of mysql_query() function is a reference pointer (result set) of PHP resource. This return value can be processed in various result set processing functions for each field of the result data table.
mysql_num_fields()
The mysql_num_fields() function obtains the number of fields in the result set
int mysql_num_fields ( resource $result )
mysql_num_rows()
The mysql_num_rows() function obtains the number of rows in the result set
int mysql_num_rows ( resource $result )
$result = mysql_query("SELECT * FROM books");$rows = mysql_num_rows($result);$cols = mysql_num_fields($result);var_dump($rows,$cols);//int 4 int 8As you can see from the results, there are four rows and eight columns in the result set.
If you need to access the data in the result set, you can choose any of the four functions: mysql_fetch_row(), mysql_fetch_assoc(), mysql_fetch_array(), mysql_fetch_object().
mysql_fetch_row()
The mysql_fetch_row() function takes a row from the result set as an enumeration array
array mysql_fetch_row ( resource $result )
If you need to access the data in the result set, you can choose any of the four functions: mysql_fetch_row(), mysql_fetch_assoc(), mysql_fetch_array(), mysql_fetch_object().
mysql_fetch_row()
The mysql_fetch_row() function takes a row from the result set as an enumeration array
array mysql_fetch_row ( resource $result )
$result = mysql_query("SELECT * FROM books");$row = mysql_fetch_row($result);//Array ([0]=>>1 [1]]==> PHP [2]=>PHP [2]]=>Electronic Industry Press [3]=>Zhang San [4]=>80.00 [5]=>0 [6]=>> [6]=> [7]=> [7]=>> [7]=>PHP-related) print_r ($); $row = mysql_fetch_row ($result); //Array //Array ([0]][0]=>> 3 [1]=>> JSP [2]]=> Electronic Industry Press [3]=> Wang Wu [4]]=> Wang Wu [4]]=> Wang Wu [4]]=> [4]]]6]=> [7]=> JSP-related) print_r($row);
mysql_fetch_assoc()
The mysql_fetch_assoc() function takes a row from the result set as an associative array
array mysql_fetch_assoc ( resource $result )
$result = mysql_query("SELECT * FROM books");$assoc = mysql_fetch_assoc($result);//Array ([id]=> 1 [bookname]=> PHP [publisher]=> Electronic Industry Publishing House [author]=> Zhang San [price]=> 80.00 [ptime]=> 0 [pic]=> [detail]=> PHP-related) print_r($assoc); $assoc = mysql_fetch_assoc ($result); //Array ([bookid]=> 3 [publisher]=> Electronic Industry Publishing House [Wang Wu]> [price] => 70.00 [ptime]=> 0 [pic]=> [detail]=> JSP-related) print_r($assoc);
mysql_fetch_array()
The mysql_fetch_array() function takes a row from the result set as an associative array, or a numeric array, or both. The second optional parameter result_type in mysql_fetch_array() is a constant that accepts the following values: MYSQL_ASSOC, MYSQL_NUM and MYSQL_BOTH, and the default value is MYSQL_BOTH.
array mysql_fetch_array ( resource $result [, int $ result_type ] )
$result = mysql_query("SELECT * FROM books");$array = mysql_fetch_array($result);//Array ([0]=>> 1 [id]==>>1 [1]]==> [1]=> PHP [bookname] =>PHP [bookname]] =>PHP [2]]=>> PHP [2]]=>> Electronic Industry Press [publisher] =>Electronic Industry Press [3]=>> [publisher]=>> Zhang San [author]=>>> Zhang San [4]=>>>>>>>>>80.00 [price]]=>>>>>>80.00 [price] =>>80.00 [price] =>>>80.00 [5]==>> 0 [ptime]]=> 0 [ptime]=>>0 [6] = =>> [pic] =l_fetch _ array ($result); // / Array ([0]=> 3 [id]]=> 3 [1]=>JSP [bookname]=> 3 [1]=>>JSP [bookname]=>>JSP [2]=>>>electronics Industry Press [publisher]=>electronics Industry Press [3]=>> Wang Wu [author]=>Wang Wu [4]]=>>> Wang Wu [4]=>>> 70.00 [price]=>>>70.00 [price]=>>>70.00 [5]=>>>>>> 0 [ptime] => 0 [ptime]] => [0]]]]]]]]]]]]]]]]]]]> [6]]]r ($array);
mysql_fetch_object()
The mysql_fetch_object() function takes a row from the result set as an object
object mysql_fetch_object ( resource $result )
$result = mysql_query("SELECT * FROM books");$object = mysql_fetch_object($result);//StdClass Object ([id]=> 1 [bookname]=> PHP [publisher]=> Electronic Industry Publishing House [author]=> Zhang San [price]=> 80.00 [ptime]=> 0 [pic]=> [detail]=> PHP-related) print_r ($object); $object = mysql_fetch_object ($result); //stdClass Object ([id]=> 3 [book name]=> JSP => publisher [auth]> Electronic Industry Publishing House Or]=> Wang Wu [price]=> 70.00 [ptime]=> 0 [pic]=> [detail]=> JSP-related) print_r($object);
For the four functions above, the default pointer points to the first row of records. After obtaining a row of records, the pointer moves down automatically. If it is the last committee, the function returns false. In general, mysql_fetch_assoc(), a function that returns associative arrays, is more commonly used.
mysql_data_seek()
The mysql_data_seek() function moves pointers to internal results
[Note] $row_number starts at 0
bool mysql_data_seek ( resource $result , int $row_number )
$result = mysql_query("SELECT * FROM books");$assoc = mysql_fetch_assoc($result);mysql_data_seek($result , 2);$assoc = mysql_fetch_assoc($result);Array ( [id] => 4 [bookname] => PHP [publisher] => Electronic Industry Press [author] => Xiao Bai [price] => 80.00 [ptime] => 0 [pic] => [detail] => PHP Relevant )print_r($assoc);mysql_data_seek($result , 0);$assoc = mysql_fetch_assoc($result);//Array ([id]=> 1 [bookname]=> PHP [publisher]=> Electronic Industry Press [author]=> Zhang San [price]=> 80.00 [ptime]=> 0 [pic]=> detail]=> PHP-related) print_r($assoc);
Next, use the while loop and mysql_fetch_assoc() function to display the result set in tabular form
<style>
.table{
border:1px solid black;
border-collapse:collapse;
table-layout:fixed;
}</style>$result = mysql_query("SELECT id,bookname,publisher,author,price FROM books");echo '<table border="1" width="800" class="table">';echo '<tr>';echo '<th>number</th>';echo '<th>Title</th>';echo '<th>Press</th>';echo '<th>author</th>';echo '<th>Price</th>';echo '</tr>';while($assoc = mysql_fetch_assoc($result)) { echo '<tr>'; echo "<td>{$assoc['id']}</td>"; echo "<td>{$assoc['bookname']}</td>"; echo "<td>{$assoc['publisher']}</td>"; echo "<td>{$assoc['author']}</td>"; echo "<td>{$assoc['price']}</td>"; echo '</tr>';
}echo '</table>';
mysql_free_result()
The mysql_free_result() function is used to release the result memory
bool mysql_free_result ( resource $result )
mysql_free_result() only needs to be invoked when considering how much memory it takes to return a large result set. At the end of the script, all associated memory will be freed automatically.
Close database connection
mysql_close()
The mysql_close() function is used to close MySQL connections
bool mysql_close ([ resource $link_identifier = NULL ] )
mysql_close() closes the non-persistent connection to the MySQL server associated with the specified connection identifier. If no link_identifier is specified, the last open connection is closed
Therefore, a relatively complete php operation database extension function program is as follows
<?php//Connect database $link = mysql_connect ('localhost','root','******'); if (!$link) {die ('connection failure:'. mysql_error());
}//Select the database mysql_select_db('bookstore',$link) or die('cannot select the database bookstore:'. mysql_error ();//execute the SQL command $insert = insert into books (bookname, publisher, author, price, detail) values
('PHP','Electronic Industry Press','Zhang San','80.00','PHP Relevant'),
('ASP','Electronic Industry Press','Li Si','90.00','ASP Relevant'),
('JSP','Electronic Industry Press','Wang Wu','70.00','JSP Relevant')";$result = mysql_query($insert);//Operation result set $result = mysql_query ("SELECT id, bookname, publisher, author, price, FROM books"); echo'< table border = "1" width = "800" class = "table" >'; echo'< tr >'; echo'< tr >'; echo'< th >>> number </th>'; echo'< th < th >>>>>> </th>>'; echo'< th < th < th < th >>>> </th>>'; echo'< th < th < th < th < th < th </th>>>'; echo'< echo < echo '< th < th'</tr>'; while ($assoc = mysql_fetch_assoc ($result)) Echo {{{{echo'<echo'<tr>'<techo'<echo'''<td > <td > {$assoc ['id']]]]}{{$assoc ['bookname']]] {{$assoc['assoassoc''''''''''''''''''''''''[[[publisher'']]]]] {{{{echecho {{{echechechecho "<td > <td > <td >{{{<td >{{{{{{{{{{{{$assoc [[[[[assoc [[[```````"echo' </tr>';
}echo '</table>';//Release result set mysql_free_result($result); //close database connection mysql_close($link);?>