On 17/11/15 01:02, Karl DeSaulniers wrote:
Hello All,
Hoping someone can help me with this query.
I want to select some custom fields from my database that are part of a group of custom fields.
There are several of these groups. I want to (in one sql statement) grab these fields, all of them
and have them grouped in the results like so.
$custom_fields = array(
'Group1' => array(
'field_ID' => '1',
'field_name' => 'myAddressField',
'filed_slug' => 'my-address-field'
),
'Group2' => array(
'field_ID' => '2',
'field_name' => 'myCityField',
'filed_slug' => 'my-city-field'
),
'Group3' => array(
'field_ID' => '3',
'field_name' => 'myStateField',
'filed_slug' => 'my-state-field'
)
)
Here is the clincher... not all the info is in the same table.
This is what I am doing currently and it works, however I would like to eliminate calling the database in a foreach loop as well as multiple times to get my results.
[code]
$FieldGroups = $wpdb->get_results("SELECT DISTINCT Field_Group FROM ".table_name1."");
foreach($FieldGroups as $i=>$FieldGroup) {
$field_group = stripslashes_deep($FieldGroup->Field_Group);
$SQL = "SELECT ft.*, mt.*
FROM ". table_name1." ft
LEFT JOIN ". table_name2." mt
ON mt.Field_ID = ft.Field_ID
WHERE ft.Field_Group='%s' AND mt.Page_ID=%d AND ft.Field_Display='%s'
ORDER BY ft.Field_ID ASC";
$Fields = $wpdb->get_results($wpdb->prepare($SQL, $field_group, $Page_ID, $display));
}
[end code]
How can I combine these into one query that fills the result array the way described above?
I thought of a SELECT inside a SELECT, but my php foo is a little under trained at the moment.
Not sure how to do such and achieve the results I am after. Any help would be appreciated.
TIA
Best,
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Hi Karl,
is this all the code ?
Because, if the FOREACH() loop is running over *all* Field_Group fields,
there is nothing to filter - might as well just run the entire INNER
JOIN on table_name1.
In SQLite, there's an IN clause - maybe works in your database :
SELECT * from TABLE_1
WHERE Id IN ( SELECT Id from OTHER_TABLE)
Same thing can be achieved through an INNER JOIN in a more generic way :
SELECT * from TABLE_1 INNER JOIN
(SELECT * from OTHER_TABLE WHERE Id = 'criterion') AS Q1
ON TABLE_1.Id = Q1.Id
Q1, the inline query, limits the field groups to the ones requested by you.
Because it's an INNER JOIN, the join will only return matches with the
select field groups.
Hope I'm making sense ;-)
B.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
