CODEWITHSUNDEEP
phpmysqljson
Rooster
Rooster

Reputation: 10077

Querying JSON db objects from mysql in php

So I've got an app and a really annoying client who changes their mind alot, so I've decided to store some data as JSON objects.

I kind of have a match system where I'm storing the users registerd for a match as a JSON object version of an array of their user ids.

I want to have a display of all the matches that a user is already registered for as one of my filter options so I basically need to see if that user's id is stored in the text type data object.

The two ways I can think to do this are:

  1. to query every single match and JSON-decode the objects then use an in_array to see if they're in the match (this I did not do).

  2. Just use a LIKE query to search the JSON text for "[userid]" (which is what I did).

Is there a better way to do something like this? Like does MySQL have a better built-in function for something like this?

Upvotes: 0

Views: 1306

Answers (1)

Chris Hanson
Chris Hanson

Reputation: 2083

It's a mistake to try to store your user data in a JSON object within a relational database (such as MySQL). Your database is not object oriented, it is relational. Following the rules of relational databases will serve you well. You would be better off building a good database (DB) structure and conducting accurate MySQL queries.

If you keep your DB in at least the third normal form, you'll find that you can add and change user properties when your clients change their minds (again... and again... and again...) because less data will be kept in a given table so you can add and alter small tables without huge system-wide effects.

Another option in the DB would be to create a table that has various properties by id, name, and value.

Upvotes: 4

Related Questions