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”:”([^”]*)firstname.lastname@example.org([^”]*)”’
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.’”([^”]*)”‘”