how to filter sql query results with matching string

Question

I have a database table in which one column contain the string value that is looks like dictionary in php.

Table:College

Id  name    requirement
1   x   {"users": ["A", "B"], "name": "*", "pool": "CSE", "place": "Bangalore"}
2   y   {"users": ["A", "C"], "name": "*", "pool": "CSE", "place": "Chennai"}
3   z   {"users": ["A", "B"], "name": "*", "pool": "ECE", "place": "Bangalore"}
4   r   {"users": ["A", "D"], "name": "*", "pool": "EEE", "place": "UP"}

From this table I need to filter only those rows in which the requirements column contain “pool”:”CSE”. That is if pool is CSE or pool is ECE etc .

Currently my php code for fetching whole table is:

    $query = "SELECT * FROM waitqueue";
    $result = mysql_query($query);

    echo "[";
    echo json_encode(mysql_fetch_assoc($result));
    while ($row = mysql_fetch_assoc($result))
        echo "," . json_encode($row);
    echo "]";

I dont want to change the above sql query.Because I need both conditions(full table and selected pool rows) from single query.So I wish to parse from results.

How to match the "pool" and how can I filter those rows?

Any one please suggest me.

Thanks in advance.

This is a very strange architecture... If you want to use a property for filtering, then you should not hide it deep in a serialized column value but as a separate column or reference, so that you can create an index on that. There are extensions for mysql that allow to access json encoded columns, but this does not change the fact that your architecture is strange. You should think about changing that, since the current setup won't scale. For a quick workaround you can simply create a wrapper function around the mysql_fetch_assoc() function for the filtering. — arkascha
– arkascha, Commented Nov 21, 2015 at 9:25
Only option is to unserialize all that and use array function to search it.You`ll have to get all the data each time,so consider normalizing your data. — Mihai
– Mihai, Commented Nov 21, 2015 at 9:37
Can you put some examples? and also can we use string functions to find the pool? — Nithya.K
– Nithya.K, Commented Nov 21, 2015 at 9:42

Nick · Accepted Answer · 2015-11-24 06:17:19Z

1

In your case you can filter like this

WHERE requirement LIKE '%"pool": "CSE"%'

But this is very big crutch :)

It is prefer to separate column requirement to columns: name, pool, place. Than you may filter like this, for example:

WHERE pool = "CSE"

Other method is to filter rows in your while cycle like this

$output = '';
while ($row = mysql_fetch_assoc($result)) {
    $requirement = json_decode($row['requirement'], true);
    if ($requirement['pool'] === 'CSE') {
        $output .= $output ? ', ' : '';
        $output .= json_encode($row);
    }
}
$output = '[' . $output . ']';
echo $output;

edited Nov 24, 2015 at 6:17

answered Nov 21, 2015 at 11:50

Nick

10.2k8 gold badges65 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Nithya.K Over a year ago

thanks for the response.But I dont have permisiion to spilt the database.can you suggest me any other way?

Nick Over a year ago

mysql_fetch_assoc() returns one row, therefore you need cycle for reading all rows

Nithya.K Over a year ago

I tried your code..But its not working.I think the problem is with comma in echo ",".json_encode($row);Can you please tell me how to resolve this? That is for our case we dont need echo json_encode(mysql_fetch_assoc($result)); this statement

Nick Over a year ago

$requirement['pool'] contains data like this: {"users": ["A", "B"], "name": "*", "pool": "CSE", "place": "Bangalore"}. It is JSON data. json_decode($row['requirement'], true) converts JSON string into associative array.

Nithya.K Over a year ago

Please see the code in the question.There I am using the statement echo json_encode(mysql_fetch_assoc($result)); before while loop.But for our case no need of this line,because we are filtering with pool.So the comma in echo "," . json_encode($row); in your answer should be placed correctly.

|

Collectives™ on Stack Overflow

how to filter sql query results with matching string

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related