Re: MySQL two tables and an uneven number of rows

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@yahoo.com> wrote: > > On 13/09/16 08:42, Karl DeSaulniers wrote: >>> 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 >> > > 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