PHP connects to MySQL database and outputs in json format

1. Introduction

There are many ways to connect PHP to database. This paper introduces the commonly used connection method of MySQL database. There are also two ways to connect PHP to MySQL, one is object-oriented, the other is process-oriented, and the two methods are slightly different. The following two methods are introduced by code to connect MySQL and output it in json format.

2. Object-oriented approach

<?php 
header("content-Type: text/html; charset=utf-8");// Character encoding setting  
$servername = "localhost"; 
$username = "root"; 
$password = "123456"; 
$dbname = "mydb"; 
 
//  Create a connection  
$conn =new mysqli($servername, $username, $password, $dbname); 
//  Detect a connection  
if ($conn->connect_error) { 
  die("Connection failed: " . $conn->connect_error); 
} 
 
$sql = "SELECT * FROM power WHERE DATE='2014-1-1'"; 
$result = $conn->query($sql); 
 
$arr = array(); 
//  Output data per row  
while($row = $result->fetch_assoc()) { 
  $count=count($row);// Cannot be in a loop statement, because every time you delete row Array length decreases  
  for($i=0;$i<$count;$i++){ 
    unset($row[$i]);// Delete redundant data  
  } 
  array_push($arr,$row); 
 
} 
//print_r($arr); 
echo json_encode($arr,JSON_UNESCAPED_UNICODE);//json Code  
$conn->close(); 
 
?> 

3. Process-oriented approach

<?php 
header("content-Type: text/html; charset=utf-8");// Character encoding setting  
$servername = "localhost"; 
$username = "root"; 
$password = "123456"; 
$dbname = "mydb"; 
 
//  Create a connection  
$con =mysqli_connect($servername, $username, $password, $dbname); 
//  Detect a connection  
if (mysqli_connect_errno()) 
{ 
  echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
} 
 
$sql = "SELECT * FROM power WHERE DATE='2014-1-1'"; 
$result = mysqli_query($con,$sql); 
 
$arr = array(); 
while($row = mysqli_fetch_array($result)) { 
  $count=count($row);// Cannot be in a loop statement, because every time you delete  row Array length decreases  
  for($i=0;$i<$count;$i++){ 
    unset($row[$i]);// Delete redundant data  
  } 
 
  array_push($arr,$row); 
 
} 
echo json_encode($arr,JSON_UNESCAPED_UNICODE); 
mysqli_close($con); 
 
?> 

4. Outputting the json sample

[{"NAME":" Community 1","DM":"A","LNG":"564142.623","LAT":"4329376.169","DATE":"2014-1-1","VAL":"26.8"},{"NAME":" Community 2","DM":"B","LNG":"563089.677","LAT":"4329311.017","DATE":"2014-1-1","VAL":"26.8"}] 

5. json output description

Since $row = mysqli_fetch_array ($result) fetches one row of data and stores it as an array, there are 0, 1, 2 … subscripts in addition to field and value keys by default, as shown below

Array ( [0] =>  Community 1 [NAME] =>  Community 1 [1] => A [DM] => A [2] => 564142.623 [LNG] => 564142.623 [3] => 4329376.169 [LAT] => 4329376.169 [4] => 2014-1-1 [DATE] => 2014-1-1 [5] => 26.8 [VAL] => 26.8 )  

The data becomes redundant, and the unset method removes the redundant data from the array and then adds it to the $arr array.
In addition, in json encoding, json_encode ($arr); It will appear that Chinese is encoded by unicode, options parameter is added to php5.3, and JSON_UNESCAPED_UNICODE is added after 5.4. This parameter does not need to be processed by escape and unicode. Therefore, before 5.4, Chinese needs to be processed. 5.4 The parameters in the code can be directly supplemented.

There are two ways to sort out the online methods. There are two ways to deal with them before 5.4. Method 1: There is a problem in practical application, and some characters will drop. The reason is not clear for the time being.

function encode_json($arr){ 
  $code = json_encode($arr); 
  return preg_replace("#\\\u([0-9a-f]+)#ie", "iconv('UCS-2', 'UTF-8', pack('H4', '\\1'))", $code); 
} 

Method 2: Do urlencode first, then json_encode, and finally do urldecode

function encode_json($str) { 
  return urldecode(json_encode(url_encode($str)));   
} 
 
/** 
 * 
 */ 
function url_encode($str) { 
  if(is_array($str)) { 
    foreach($str as $key=>$value) { 
      $str[urlencode($key)] = url_encode($value); 
    } 
  } else { 
    $str = urlencode($str); 
  } 
   
  return $str; 
} 

Common Methods of Connecting mysqli to mysql Database (Object-Oriented and Process-Oriented)

I believe that all the partners who started to learn PHP use php4 or php5. In these versions, there is a function mysql_connect () to connect to the database. However, when using php5.5 and above, you will find that using mysql_connect () will directly report errors. This is because mysql_connect () is abandoned in the later versions of php5.5 in consideration of future portability, security and performance, so we can only use mysqli_connect () and PDO. Here I say mysqli_connect ().

mysqli_connect (), process-oriented and object-oriented methods of connecting to databases.

(1) Process-oriented connection database:

<!--  Process-oriented database mysqli Connect  --> 
<?php 
  $conn=mysqli_connect("localhost","root","950609","user"); 
  //  Connect to a database user 
  if (!$conn) { 
    # code... Determine whether the link was successful  
    echo " Connection failed! "; 
    echo mysqli_connect_error(); 
    exit(); 
  } 
  mysqli_query($conn,"set names utf8"); 
  //  Specify the encoding format  
  $sql="select * from goods"; 
  // sql Statement  
  $result=mysqli_query($conn,$sql); 
  //  Execute sql Statement to return the execution results to the result set  
  $row=mysqli_fetch_array($result); 
  //  Get from the result set 1 Rows as arrays  
  echo "<pre>"; 
  print_r($row); 
?> 

(2) Object-oriented database connection

<!--  Object-oriented database mysqli Connect  --> 
lt;?php 
$mysqli=new mysqli("localhost","root","950609","user"); 
if ( $mysqli -> connect_error ) { 
  die( 'Connect Error (' . $mysqli -> connect_errno . ') ' 
      . $mysqli -> connect_error ); 
} 
$sql = "select * from goods"; 
  $mysqli->set_charset("utf8");  
  $result = $mysqli->query($sql);  
  $row = $result->fetch_array(); //  Get from the result set 1 Rows as arrays   
  echo '<pre>'; 
  print_r($row);  
  /* free result set */  
  $result->free();  
  /* close connection */  
  $mysqli->close();  
> 

This article is introduced to this, on the PHP connection MySQL database and json format output of the implementation code, the need for friends can refer to 1.