PHP Handy notes compiled PHP script and Java connection MySQL database _php instance

Environment

Development Kit: appserv-win32-2.5.10

Server: Apache2.2

Database: PhpMyAdmin

Language: Php5,java

Platform: Windows 10

Java Driver: mysql-connector-java-5.1.37

Demand

Write a PHP scripting language that connects to the test library of the phpMyAdmin database

Write a Java Web server that connects to the test library of the phpMyAdmin database

Code

PHP connection mode

mysql.php

<?php/****************************** database connection *****************************/$conn = @mysql_connect ("localhost", " Root "," 123 "), if (! $conn) {die  (" Connection Database failed: ". Mysql_error ());} mysql_select_db ("Test", $conn);//character conversion, read library mysql_query ("Set character set UTF8"); mysql_query ("Set names UTF8");? >

test.php Test

<?php   error_reporting (0);     Prevent error  include (' mysql.php ');  $result =mysql_query ("SELECT * from user"); Based on the previous calculation of the starting record and number of records  //loop out the record  $six;  while ($row =mysql_fetch_row ($result))  {    echo $row [0];  echo $row [1];  }? >

Run:

Java Connection mode

1. Create a new Java project for Mysqltest

2. Load the JDBC driver, mysql-connector-java-5.1.37

Mysqlconnection.java

package com.mysqltest;import java.sql.connection;import Java.sql.drivermanager;import java.sql.sqlexception;/* * **MYSQL Connection * * * parameter: * conn connection * URL MySQL database connection address * User Database login account * Password Database login Password * Method: * Con  n Get connection */public class Mysqlconnection {public static Connection conn = null;  public static String Driver = "Com.mysql.jdbc.Driver";  public static String URL = "Jdbc:mysql://127.0.0.1:3306/post";  public static String user = "root";  public static String password = "123";   /* * Create MySQL data connection first step: Load driver Class.forName (Driver) Second step: Create connection * drivermanager.getconnection (URL, user, password);    */Public Connection conn () {try {class.forname (driver);      } catch (ClassNotFoundException e) {System.out.println ("Driver loading error");    E.printstacktrace ();    } try {conn = drivermanager.getconnection (URL, user, password);      } catch (SQLException e) {System.out.println ("Database link error");    E.printstacktrace ();  } return conn; }}

Work.java

Package Com.mysqltest;import Java.sql.connection;import Java.sql.preparedstatement;import java.sql.ResultSet;    Import java.sql.sqlexception;/* * MySQL Add/delete change */public class Work {/* * Insert increase */public static int insert () {    Mysqlconnection connection = new Mysqlconnection (); Connection Conns; Get the connection PreparedStatement PST;    Execute SQL statement int i = 0;    String sql = "INSERT into user (Username,password) VALUES (?,?)";      try {Conns = Connection.conn ();      PST = conns.preparestatement (SQL);      Pst.setstring (1, "Lizi");      Pst.setstring (2, "123");      i = Pst.executeupdate ();      Pst.close ();    Conns.close ();      } catch (SQLException e) {System.out.println ("Data write Failed");    E.printstacktrace ();  } return i;    }/* * Select writes */public static void Select () {mysqlconnection connection = new Mysqlconnection (); Connection Conns; Get the connection PreparedStatement PST; Execute SQL statement (Statement) ResultSet rs; Gets the result of a String that returns a sql = "SELECT * FROM useR ";      try {Conns = Connection.conn ();      PST = conns.preparestatement (SQL);      rs = pst.executequery (sql);//Execute SQL statement System.out.println ("---------------------------------------");    System.out.println ("name |      Password ");    while (Rs.next ()) {System.out.println (rs.getstring ("username") + "|      "+ rs.getstring (" password "));      } System.out.println ("---------------------------------------");      Conns.close ();      Pst.close ();    Rs.close ();      } catch (SQLException e) {System.out.println ("Data query failed");    E.printstacktrace ();    }/* * Update modified */public static int update () {mysqlconnection connection = new Mysqlconnection (); Connection Conns; Get the connection PreparedStatement PST;    Execute SQL statement (Statement) int i = 0; String sql = "Update user set password =?"    where username =? ";      try {Conns = Connection.conn ();      PST = conns.preparestatement (SQL);      Pst.setstring (1, "123");      Pst.setstring (2, "Lizi"); I = Pst.executeupdate ();      Pst.close ();    Conns.close ();      } catch (SQLException e) {System.out.println ("Data modification failed");    E.printstacktrace ();  } return i;    }/* * Delete Delete */public static int Delete () {mysqlconnection connection = new Mysqlconnection (); Connection Conns; Get the connection PreparedStatement PST;    Execute SQL statement (Statement) int i = 0;    String sql = "Delete from user where username =?";      try {Conns = Connection.conn ();      PST = conns.preparestatement (SQL);      Pst.setstring (1, "Lizi");      i = Pst.executeupdate ();      Pst.close ();    Conns.close ();      } catch (SQLException e) {System.out.println ("Data deletion failed");    E.printstacktrace ();  } return i;     }/* * Test */public static void main (string[] args) {//SYSTEM.OUT.PRINTLN (insert ());    Select ();    SYSTEM.OUT.PRINTLN (update ());  System.out.println (delete ()); }}

Test

Ps:php manipulating statements in MySQL database

We often use the conn.php file to establish a link to the database and then invoke it with include in the desired file. This effectively prevents changes to the database properties and causes other errors related to file calls to the data.

Now look at a conn.php file with the following code:

<?php $conn = @mysql_connect ("localhost", "root", "") or Die ("Database connection Error");//Link Database server mysql_select_db ("Messageboard", $ conn);//Select the database named Messageboard mysql_query ("Set names ' utf ');//Use UTF encoding, which cannot be written here utf-otherwise it will be garbled, but UTF is not case-sensitive?>

Learn to accumulate and collect several basic functions of PHP operation MySQL:

. Use the mysql_connect () function to connect to the MySQL server: mysql_connect ("hostname", "username", "password");
For example, $link = mysql_connect ("localhost", "root", "") or Die ("Cannot connect to the database server! The database server may not be started, or the user name password is incorrect! ". Mysql_error ());

Using the mysql_select_db () function, select the database file: mysql_query ("Use database name", $link);

For example, $db _selected=mysql_query ("Use example", $link);

Execute the SQL statement using the mysql_query () function: mysql_query (string query (SQL statement), $link);

Such as:

Added members: $result =mysql_query ("INSERT into tb_member values (' A ', ')", $link);

Modified member: $result =mysql_query ("Update tb_member setuser= ' B ', pwd= ' where user= ' a '", $link);

Delete Member: $result =mysql_query ("Delecte from Tb_member where user= ' B '", $link);

Enquiry Member: $sql =mysql_query ("SELECT * from Tb_book");

Fuzzy query: $sql =mysql_query ("select * from Tb_book where bookname like '%". Trim ($txt _book). %'");

The universal character% represents 0 or any number of characters.

Display table structure: $result =mysql_query ("DESC tb_member");

Use the mysql_fetch_array () function to obtain information from the array result set:

Syntax structure: Array mysql_fetch_array (Resource Result[,int result_type])

Parameters of the parameter result resource type, shaped parameters, to pass in the data pointer returned by the mysql_fetch_array () function;

Parameter result_type: Optional, PHP operation MYSQL Database statement base integer parameter, to pass in the MYSQL_ASSOC (associated index), Mysql_num (numeric index) Mysql_both (including the first two, the default value)

Such as:

<> $sql =mysql_query ("SELECT * from Tb_book"), $info =mysql_fetch_object ($sql);<> $sql =mysql_query ("Select * FROM Tb_book where bookname like '% '. Trim ($txt _book). % ' "); $info =mysql_fetch_object ($sql);

. Use the Mysql_fetch_object () function to get a row from the result set as an object:

Grammatical structure: Object Mysql_fetch_object (resource result);

Such as:

<> $sql =mysql_query ("SELECT * from Tb_book"), $info =mysql_fetch_object ($sql);<> $sql =mysql_query ("Select * FROM Tb_book where bookname like '% '. Trim ($txt _book). % ' "); $info =mysql_fetch_object ($sql);

The Mysql_fetch_object () function is similar to the mysql_fetch_array () function, with only a little difference, that is, to return an object instead of an array, which can only access the array by word by. Syntax structure for accessing elements of rows in the result set: $row->col_name (column name)

Use the Mysql_fetch_row () function to get each record in the result set row by line:

Syntax structure: Array mysql_fetch_row (resource result)

Such as:

<> $sql =mysql_query ("SELECT * from Tb_book"), $row =mysql_fetch_row ($sql);<> $sql =mysql_query ("SELECT * From Tb_book where bookname like '% '. Trim ($txt _book). % ' "); $row =mysql_fetch_row ($sql);

. Use the Mysql_num_rows () function to get the number of records in the result set:

Syntax structure: int mysql_num_rows (resource result)

Such as:

$sql =mysql_query ("SELECT * from Tb_book"), ... <?php $nums =mysql_num_rows ($sql); Echo $nums;? >

Note: To get the data affected by an INSERT, UPDATE, DELETE statement, you must use the Mysql_affected_rows () function.

. mysql_query ("Set names GB");//set MySQL encoding format to GB type to block garbled characters.

. Close Recordset: Mysql_free_result ($sql);

. Close MySQL Database server: mysql_close ($conn);

