> On Sep 12, 2016, at 2:53 PM, B. Aerts <ba_aerts@xxxxxxxxx> wrote: > > On 12/09/16 05:24, Karl DeSaulniers wrote: >> Hello All, >> Hoping you can help clear my head on this. I have two MySQL tables for custom fields data to be stored. >> >> custom_fields custom_fields_meta >> >> custom_fields is the info for the actual field displayed in the html and custom_fields_meta is the data stored from entering a value on said field in the form. >> >> Custom fields can be added and removed at will by the user and so when for instance, adding a field, >> it currently creates an uneven number of rows in the custom_fields_meta if there were any entries with fields create prior to this new one. >> >> Currently I have this code: >> >> $SQL = "SELECT ft.*, mt.Meta_Value >> FROM `CUSTOM_FIELDS` ft >> LEFT JOIN `CUSTOM_FIELDS_META` mt >> ON mt.Field_ID = ft.Field_ID >> WHERE mt.Order_ID=%d >> ORDER BY ft.Field_ID ASC"; >> >> I have tried JOIN, FULL JOIN, FULL OUTER JOIN, OUTER JOIN and LEFT JOIN. >> If I manually put in the missing rows in the meta table, left join works. >> However, manually updating prior entries is not going to happen. >> >> So my question is how do I get all the table rows in both tables even if there is not a row to match on the meta table? >> or >> How would I update the prior entries to include this new field in the meta table and keep things orderly? >> The meta is stored per order id and so there is groups of meta data per order id. I would like to avoid scattered data. >> Is there a way to push the index down to fit them in or is this just going to be too costly on server resources? >> >> TIA, >> >> Best, >> >> Karl DeSaulniers >> Design Drumm >> http://designdrumm.com <http://designdrumm.com/> >> >> >> >> >> > Hi Karl, > > I can't really follow your problem ... Any chance to post 2 dummy table layouts to show what you want, and what you get ? > > And it isn't something you could solve with a UNION ? > > Hello, Thanks fro your reply. I can try. :) BEFORE: CUSTOM_FIELDS: `Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, `Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, `Field_Required`, `Field_Date_Created` |————————————————————————————————————————————————————————————————————————————————| (1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'), (2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'), (3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12') CUSTOM_FIELDS_META: `Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value` |—————————————————————| (1, 1, 1003, '555-123-4567'), (2, 2, 1003, ''), (3, 3, 1003, '') Then lets say the user wants to add a cell phone field. AFTER: CUSTOM_FIELDS: `Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, `Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, `Field_Required`, `Field_Date_Created` |————————————————————————————————————————————————————————————————————————————————| (1, 'Pickup Info', 'Phone 1', 'Origin_Phone1', 'origin-phone1', 'phone', 'Pickup Main phone number', '', 'Yes', 'No', '2015-04-19 08:46:10'), (2, 'Pickup Info', 'Phone 2', 'Origin_Phone2', 'origin-phone2', 'phone', 'Pickup alternate phone number 1', '', 'Yes', 'No', '2015-04-19 08:46:11'), (3, 'Pickup Info', 'Phone 3', 'Origin_Phone3', 'origin-phone3', 'phone', 'Pickup alternate phone number 2', '', 'Yes', 'No', '2015-04-19 08:46:12'), (4, 'Pickup Info', 'Cell', 'Origin_Cell', 'origin-cell', 'phone', 'Pickup cell phone number', '', 'Yes', 'No', '2015-04-19 08:46:13') CUSTOM_FIELDS_META: `Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value` |—————————————————————| (1, 1, 1003, '555-123-4567'), (2, 2, 1003, ''), (3, 3, 1003, '') The 4th field id is not in the meta table. So when I read out what is in the custom fields and matching meta data, cell phone does not show up for order that were processed before adding the custom field cell phone. I am trying to show the cell phone field on old orders as well even if there is not a row representing data in the meta table. Hope that clears it up and not mud it up.. :P Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
