I have a table called fields_data_for_site which is connected to a table named sites, where fields_data_for_site.site_id = sites.id
I want to use PDO and return the following array in one query:
"site_name" => "MySite",
"site_link" => "mylink",
"f" => [
"callback" => "usr2",
"action" => "usr"
]
element 'f' contains the data in the table fields_data_for_site for the current site_id it is selecting (I want to select all though).
What I tried:
SELECT sites.*, (SELECT `name`, `value` FROM fields_data_for_site) AS f
FROM sites LEFT JOIN fields_data_for_site ON fields_data_for_site.site_id = sites.id;
Getting one error:
Operand should contain 1 column(s)
Advanced explanation
I know the title is a bit confusing, but I am going to explain my problem in advanced.
I have a service, which displays sites people can vote on, and then receive a reward after voting for all sites that are required on that service.
Now the issue is more related to the administrating side, where you have to add a site.
In admin panel, there is a page called site management, where it allows you to add sites you will require the people to vote on.
Now, we don't want people to bullshit their votes, we need to know if they have voted for sure - therefore we will use a callback system.
How does the callback work, I've added two modules
Parameter callback: Admin will use this module if the site that hes adding will send a callback to the service with the voter's username or IP when he voted, with a specific parameter (i.e www.myservice.com/callback/?user=votedusername).
IP check: Admin will use this module only if the site that hes adding supports ip checking - the site's check link will return true if the ip has voted, false if not.
Now what I have is, a very friendly way for developers add new modules, modules can contain the following things:
- Link to the site's callback.
- Name of the parameters the site is going to send
- Link to the site that returns if ip has voted (true/false)
And so on.
So yeah, when admin goes to the add site page, he should see the fields that the module he selected offers.
See this database diagram:
img http://gyazo.com/8a6a41088e52c8e9aee1ca4529d53e5c.png
I forgot to include one box, called Modules, but it just contains the module name and an primary id.
Anyway now you have a little background on this, now I'll explain my issue. I want to display a list of all sites added to the system, including it's information, and all information of the module it is using.
For each field in the module the site is using, there are 2 columns, name and value where name is the name of the field, and value is the value.
Now I want the sites to display in a table, and then let the module's fields be part of the table too, where fields_data_for_site.name will be a column in the table, and fields_data_for_site.value will be it's value.
Example:
We have a module called DualParameter, and it offers the following fields:
field_type: text
field_name: param1
placeholder: first parameter name
field_type: text
field_name: param2
placeholder: first parameter name
I am an admin, using this module now for the new site I am adding with the following 2 parameters: param1: usr, param2: user
The table will look like this:
| Site name | Link for user | module | param1 | param2|
+-----------+---------------+---------------+--------+-------|
| My Site | www.bla.com/..| DualParameter | usr | user |
--------------------------------------------------------------
Note: this is not a database table, it's a table I am going to display in adminCP, sites in the system.
I didn't show the part where I add the site name, url etc because it's not necessary and not related to the modules.
You see how each field in the table has a column with it's name and value?
Now the problem is, that I need a query that will return me a ready array with PHP of the fields for the module of each site, from fields_data_for_site, and in addition the site's information (i.e site name, link, etc.)
What I have tried
SELECT sites.*, fields_data_for_site.name as name, fields_data_for_site.value as value
FROM sites
LEFT JOIN fields_data_for_site
ON fields_data_for_site.site_id = sites.id
This works, yes, however it returns a new array element if the site has more than one module field.
Example data returned for this query:
img2 http://gyazo.com/c0096012ed032d5c1bf14cddecdb01b4.png
You can see how there are 2 rows for site_id 22?
Now my question is, how can I design it, so it returns me a ready array in PHP with sub array for each site if it contains multiple fields, without using 2 queries?
I can use multiple values/columns in one column and then use implode if php, but this is not how database should be designed.
Example of array:
"site_name" => "MySite",
"site_link" => "mylink",
"fields" => [
"callback" => "usr2",
"action" => "usr"
]
callback and action are just names of the fields.
Any ideas?
P.S I use PDO.
I tried my best on explaining my issue, I hope it's enough and not too much. Thanks.
