Re: MySQL two tables and an uneven number of rows

> On Sep 12, 2016, at 2:53 PM, B. Aerts <ba_aerts@yahoo.com> 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