PHP connects MySQL database and outputs in JSON format

This article mainly introduces the PHP connection MySQL database and output in JSON format, has a certain reference value, now share to everyone, the need for friends can refer to

PHP Connection database has a number of methods, now describes the common method of MySQL database connection, PHP connection MySQL also has two ways, one is object-oriented, the other is the process-oriented approach, the two methods slightly different. The following code describes two ways to connect MySQL and output in JSON format

1. Introduction

PHP Connection database has a number of methods, now describes the common method of MySQL database connection, PHP connection MySQL also has two ways, one is object-oriented, the other is the process-oriented approach, the two methods slightly different. The following code describes two ways to connect to MySQL and output 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 connection $conn =new mysqli ($servername, $username, $password, $dbname); Detect the 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 each row of data while ($row = $result->fetch_assoc ()) {   $count =count ($row);//cannot be in a looping statement, because each deletion of the 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 encoded $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 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 looping statement, because each time the row array is deleted the length is reduced 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. Output JSON sample

[{"NAME": "Cell 1", "DM": "A", "LNG": "564142.623", "LAT": "4329376.169", "DATE": "2014-1-1", "VAL": "26.8"},{"name": "Cell 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) Gets a row of data and is stored as an array, there are 0,1,2 in addition to the field and value key values ... The subscript is present, as shown below

Array ([0] = = Cell 1 [NAME] = = Cell 1 [1] = a [DM] + a [2] = 564142.623 [LNG] = 564142.623 [3] = 4329 376.169 [LAT] = 4329376.169 [4] = 2014-1-1 [DATE] = 2014-1-1 [5] = = 26.8 [VAL] + 26.8)

This data is redundant, the unset method is to delete the array redundant data, and then add to the $arr array.
In addition, in JSON encoding Json_encode ($arr), will appear in Chinese is UNICODE encoding, php5.3 added the options parameter, 5.4 after the addition of Json_unescaped_unicode, this parameter, Do not need to do escape and Unicode processing. So before 5.4, we need to do a deal with Chinese. 5.4 Inside the code directly fill in the parameters can be.

In order to organize the online method, there are two methods before 5.4, Method 1: There is a problem in the actual application, some characters will be dropped, the reason is not clear.

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: First of all to do urlencode processing, then Json_encode, and finally do urldecode processing

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; }

Mysqli common methods for connecting MySQL databases (object-oriented and process-oriented)

I believe that the partners who start to learn PHP use PHP4, or PHP5, in these versions have a connection database function mysql_connect (), but when using php5.5 and more than 5.5, you will find that using mysql_connect () will directly error, This is because later versions of php5.5 take into account future portability and security, performance and discard the mysql_connect () function, so we can only use Mysqli_connect () and PDO, here I say Mysqli_connect ().

Mysqli_connect () is a two-way, process-oriented and object-oriented connection database method.

(1) Process-oriented connection database:

<!--process-oriented database mysqli Connect-<?php   $conn =mysqli_connect ("localhost", "root", "950609", "user");   Connect database user   if (! $conn) {     # code ... Determine if the link was successful     echo "Connection failed! ";     Echo Mysqli_connect_error ();     Exit ();   }   Mysqli_query ($conn, "Set names UTF8");   Specifies the encoding format   $sql = "SELECT * from goods";   SQL statement   $result =mysqli_query ($conn, $sql);   Executes the SQL statement and returns the execution result to the result set   $row =mysqli_fetch_array ($result);   Get a row from the result set as an array of   echo "<pre>";   Print_r ($row);?>

(2) Object-oriented database connection

<!--object-oriented database mysqli connection--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 a row from the result set as an array of    echo ' <pre> ';   Print_r ($row);    /* Free Result set *    /$result->free ();    /* Close connection *    /$mysqli->close ();  >

This article is introduced here, about PHP connection MySQL database and output in JSON format implementation code, the need for friends can refer to.

Related recommendations:

Basic tips for PHP+MARIADB database Operation Memo Summary

PHP implementation of MySQL read-write separation operation