-1

I have a sql table with events for booking clients for a clinic. The table values are collected from a Joomla Extension. My problem is, that all information about the client are collected in one table cell like in the example below.

How do I take out the values 

"I_WANT_CLIENT_FIRST_NAME", "I_WANT_CLIENT_LAST_NAME", "I_WANT_CLIENT_EMAIL", "I_WANT_CLIENT_PHONE" and "I_WANT_CLIENT_GENDER"

so I can work with them separately?

I am not that much in to sql, so I have never seen this technique before. What is it called?

Here is the content of my column:

[{  "id":"1",
    "fieldname":"First Name",
    "fieldtype":"text",
    "varname":"firstname",
    "size":"60",
    "is_email":null,
    "is_required":"1",
    "is_first_name":"1",
    "is_last_name":"0",
    "values":null,
    "ordering":"0",
    "data":"I_WANT_CLIENT_FIRST_NAME"
},

{   "id":"2",
    "fieldname":"Last Name",
    "fieldtype":"text",
    "varname":"lastname",
    "size":"60",
    "is_email":null,
    "is_required":"1",
    "is_first_name":"0",
    "is_last_name":"1",
    "values":null,"ordering":"0",
    "data":"I_WANT_CLIENT_LAST_NAME"
},

{   "id":"3",
    "fieldname":"Email",
    "fieldtype":"text",
    "varname":"email",
    "size":"60",
    "is_email":"1",
    "is_required":"1",
    "is_first_name":"0",
    "is_last_name":"0",
    "values":null,
    "ordering":"0",
    "data":"I_WANT_CLIENT_EMAIL"},

    {"id":"4","fieldname":"Mobile","fieldtype":"text","varname":"mobile","size":"60","is_email":null,"is_required":"1","is_first_name":"0","is_last_name":"0","values":null,"ordering":"0","data":"I_WANT_CLIENT_PHONE"},{"id":"5","fieldname":"Gender","fieldtype":"radio","varname":"gender","size":"60","is_email":"0","is_required":"1","is_first_name":"0","is_last_name":"0","values":"Male|Female","ordering":"0","data":"I_WANT_CLIENT_GENDER"
}]
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Lars
  • 29
  • 3
  • use json_decode() – TarangP Mar 08 '18 at 12:10
  • 2
    So just to be clear, you are saying that you have one column in a single row in your table that contains all of this JSON String right? – RiggsFolly Mar 08 '18 at 12:28
  • Each client booking creates a new row. The column "customfields_data" contain the information above. I want to take out the 5 values to work with - maybe in an array. Hope it makes sence... – Lars Mar 08 '18 at 12:48
  • `What is it called`. This is called violating the [first normal form](https://en.wikipedia.org/wiki/First_normal_form), a common mistake done by those who confuse a relational database with a document oriented database. – RandomSeed Mar 08 '18 at 13:15
  • 1
    @RandomSeed Could be OP is using a JSON column type https://dev.mysql.com/doc/refman/5.7/en/json.html – RiggsFolly Mar 08 '18 at 13:32
  • Seems like it is just a json string. I had no idea. Used json_decode() to make it into an array. Now I know that for later. Thank you so much guys. You comments was very helpful! – Lars Mar 08 '18 at 14:00
  • @RiggsFolly It could very well be. Oh my, I forgot about this one. Typical symptom of repression. – RandomSeed Mar 08 '18 at 14:07

1 Answers1

-1

Use DISTINCT:

SELECT DISTINCT first_name, last_name,email, phone, gender FROM your_table WHERE email = 'specified_mail';

You can use any field for the WHERE clause, especially if it's unique.

Isisco
  • 141
  • 9