Thanks Bert, Sorry for late response, but I had to step away from this for a moment to work on other things. Will most likely be back though as things are not working still. Thank you for your responses. Best, Karl DeSaulniers Design Drumm http://designdrumm.com > On Sep 13, 2016, at 2:15 AM, B. Aerts <ba_aerts@xxxxxxxxx> wrote: > > On 13/09/16 08:42, Karl DeSaulniers wrote: >>> 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 >> > > Hi Karl, > > indeed this should work with a left JOIN. Did a quick test in SQLite, and got this to work (just to indicate that your principle is correct) : > > sqlite> select * from table1 ; > 1|100 > 2|200 > 4|400 > sqlite> select * from table2 ; > 1|1000 > 2|2000 > 3|3000 > sqlite> select * from table1 join table2 on table1.ID = table2.ID ; > 1|100|1|1000 > 2|200|2|2000 > sqlite> select * from table1 left join table2 on table1.ID = table2.ID ; > 1|100|1|1000 > 2|200|2|2000 > 4|400|| > sqlite> select * from table1 right join table2 on table1.ID = table2.ID; > Error: RIGHT and FULL OUTER JOINs are not currently supported > > > I don't use MySQL (too advanced for my needs) - but a quick Google led me to this : http://dev.mysql.com/doc/refman/5.7/en/left-join-optimization.html > The 7th bullet talks of a snag based on the NULL - NOT NULL attribute of columns. Could this create your problem ? > > > If you simplify your query to the most basic form ( $SQL = "SELECT * > FROM `CUSTOM_FIELDS` ft > LEFT JOIN `CUSTOM_FIELDS_META` mt > ON mt.Field_ID = ft.Field_ID;) , what does it yield ? > > If you create 2 equally simple test tables with no additional attributes per column, what does it yield ? > > If nothing else helps, and no better placed poster comes up with a solution, one way to solve this, is the following. > Everytime a user creates a new custom field, basically what he does, is changing the "template" for an order or a form. > Retro-actively adapt the template on all old orders using that template, with a default value. > > > Hope this helps, > > Bert > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
