Re: [PHP-DB] MySQL two tables and an uneven number of rows

On 13/09/16 08:42, Karl DeSaulniers wrote:
>> On Sep 12, 2016, at 2:53 PM, B. Aerts <[email protected]> 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