Handle jSon Text Through Mysql Queries

JSON (JavaScript Object Notation) is an open standard for human-readable text to transmit data objects consisting of attribute–value pairs. It is mainly derived from JavaScript scripting language. Douglas Crockford was the first to specify and popularize the JSON text format. In the initial days, websites used XML for sending or transmitting data over networks but since JSON appeared it has become preferable because of its lightweight, readability and easy management for exchanging data across various platforms.

Many social networking sites like Facebook, Twitter, Google and other well-known websites provide API (Application Program Interface) using JSON as data exchange format. It is a manageable text format by web scripting languages like JavaScript, PHP etc. For more details please click here. While developing web applications and APIs, we sometimes need to create JSON text format, using data encoding and decoding. In this article you would be shown, how you can manage JSON data using PHP language and MySQL queries.
Also Read: Google Drive API Integration Into Your Website
Being a light-weight technology, MySQL database tables are quite handy with JSON encoded values.
jSon Text Form
Here is an examle of MySQL table which contains JSON encoded values. JSON encoding is ery easy to do using PHP. It is one of the leading web scripting languages through out the web market. For “details” column I used JSON encoding. It is a set of data for a wordpress newsletter plugin which I have made for a certain website. For that I just used a PHP array name ‘data’ and passed it through a PHP function for JSon data encoding.
$data2[‘email’] = $_POST[‘email’];

$data2[‘title’] = $_POST[‘title’];

$data2[‘first_name’] = $_POST[‘fname’];

$data2[‘last_name’] = $_POST[‘lname’];

$data2[‘address’] = $_POST[‘text-397’];

$data2[‘hear_about’] = $_POST[h_about’];
Query for data insertion:
“INSERT INTO your_table_name VALUES (NULL, ‘”.jSon_encode($data2).”‘, ‘”.date(“Y-m-d H:i:s”).”‘)”;
“jSon_encode()” php function has been used here to make all the related data into a JSON text format. But sometimes we face troubling issues while reteiving of data. For example, you want to fetch a single data from the bulk using MySQL query.
jSon Text FormatIn such case, the duplicate email id available in the table needs to be checked. This forms the origin of the problem. It is not possible to check while all data resides in a same block.
However, after some experiments, the solution of the said problem was right in front .The solution is quite easy with simple use of regular expressions.
Here is an example of how you can solve such a problem.
SELECT id FROM table_name WHERE details REGEXP ‘“email”:”([^”]*)youremail@emaildomain.com([^”]*)”’
By following this MySQL query you can get the particular id of any given email id. For that you have to make certain area of email id, dynamic making use of a simple PHP variable.
Here is the example:
“SELECT id FROM table_name WHERE details REGEXP ‘”email”:”([^”]*)”’.$email.’”([^”]*)”‘”

#cms #coding

Handle jSon Text Through Mysql Queries


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s