i nf data table structure from json data

Question

I have a database (mysql) table like

id |  party  |  rights    | m_id
---+---------+------------+---------
9  |abc      | 3,5,6      | ["12,","15,"6"]
20 |xyz      |  5,2       | ["6,","2,"9","12"]
21 |xyz 1    |  5,2       | ["6,","9,"12"]

Now I want to make my table in this way search result for rights 5 is ["12,","15,"6"] ["6,","2,"12"] ["6,","9,"12"]

12 | abc , xyz,xyz1 |

15 | abc|

6 | abc , xyz,xyz1 |

9 | xyz,xyz1 |

Answer 1

Let's start with what I believe you already have. This is an sscce. If you adjust the mysql credentials it should run on your system, creating only a temporary MySQL table. It uses PDO to access the MySQL server. Which API you actually use is not important (i.e. as long as the other API is mysqli, because the mysql_* functions are depreacted ;-))

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(
    PDO::ATTR_EMULATE_PREPARES=>false,
    PDO::MYSQL_ATTR_DIRECT_QUERY=>false,
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
));
setup($pdo);

$statement = $pdo->prepare('
    SELECT
        *
    FROM
        soFoo
    WHERE
        FIND_IN_SET(:right, rights)
');

$statement->execute( array(':right'=>5) );

/* in one way or another you have a loop where you fetch the records
having '5' in the `rights` field
*/
foreach( $statement as $row ) {
    echo $row['party'], ' ', $row['show_ids'], "\r\n";
}


function setup($pdo) {
    $pdo->exec('
        CREATE TEMPORARY TABLE soFoo
    (`id` int, `party` varchar(9), `exc` varchar(13), `rights` varchar(5), `show_ids` varchar(27))
   ');
  $pdo->exec( <<< eos
        INSERT INTO soFoo
            (`id`, `party`, `exc`, `rights`, `show_ids`)
        VALUES
            (9, 'Percept', 'Non-Exclusive', '3,5,6', '["12,","15,"6"]'),
            (20, 'Tata Sky', 'Non-Exclusive', '5,4,', '["6,","9,"11"]'),
            (21, 'Tata Sky', 'Exclusive', '5,4', '["6,","13","15,"2","4","9"]'),
            (22, 'Simbiotic', 'Exclusive', '6,2', '["12,","15,"1","6","7","8"]')
eos
    );
}

this prints

Percept ["12,","15,"6"]
Tata Sky ["6,","9,"11"]
Tata Sky ["6,","13","15,"2","4","9"]

and is (as I understand the question) as far as you've already got.

Now let's decode the JSON array and check whether it contains the element 9. If it does add inforamtion from the current row to an array called $parties

$parties = array();
/* in one way or another you have a loop where you fetch the records
having '5' in the `rights` field
*/
foreach( $statement as $row ) {
    $ids = json_decode($row['show_ids'], true);
    if ( in_array('9', $ids) ) {
        $parties[$row['id']] = $row['party'];
    }
}
var_export($parties);

prints

array (
  20 => 'Tata Sky',
  21 => 'Tata Sky',
)

But ... from a relational database point of view this is ....suboptimal.
The FIND_IN_SET clause hinders MySQL from using indices effectively; you're searching (compound) data within a single field. It's amazing what the database server implementations can do to improve performance; but it has limits.
And you're also transfering possibly unnecessary data from the MySQL server to the php instance (those records that have 5 in rights but not 9 in show_ids). If possible, this should be avoided. Networks/Network stacks are fast and can be optimized, RAM is cheap ...but again, there are limits.

So, I suggest you look into Database normalization on the one hand and/or document-oriented databases on the other hand.