Re: [PHP-DB] 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 <[email protected]> wrote:
>=20
> On 13/09/16 08:42, Karl DeSaulniers wrote:
>>> On Sep 12, 2016, at 2:53 PM, B. Aerts <[email protected]> wrote:
>>>=20
>>> 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.
>>>>=20
>>>> 	custom_fields			custom_fields_meta
>>>>=20
>>>> 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.
>>>>=20
>>>> 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.
>>>>=20
>>>> Currently I have this code:
>>>>=20
>>>> $SQL =3D "SELECT ft.*, mt.Meta_Value
>>>> 		FROM `CUSTOM_FIELDS` ft
>>>> 		LEFT JOIN `CUSTOM_FIELDS_META` mt
>>>> 		ON mt.Field_ID =3D ft.Field_ID
>>>> 		WHERE mt.Order_ID=3D%d
>>>> 		ORDER BY ft.Field_ID ASC";
>>>>=20
>>>> 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.
>>>>=20
>>>> 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?
>>>>=20
>>>> TIA,
>>>>=20
>>>> Best,
>>>>=20
>>>> Karl DeSaulniers
>>>> Design Drumm
>>>> http://designdrumm.com <http://designdrumm.com/>
>>>>=20
>>>>=20
>>>>=20
>>>>=20
>>>>=20
>>> Hi Karl,
>>>=20
>>> I can't really follow your problem ... Any chance to post 2 dummy =
table layouts to show what you want, and what you get ?
>>>=20
>>> And it isn't something you could solve with a UNION ?
>>>=20
>>>=20
>>=20
>>=20
>> Hello,
>> Thanks fro your reply. I can try. :)
>>=20
>> BEFORE:
>>=20
>> CUSTOM_FIELDS:
>>=20
>> `Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, =
`Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, =
`Field_Required`, `Field_Date_Created`
>> =
|=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=
=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=
=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=
=97=97=97=97=97|
>> (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')
>>=20
>> CUSTOM_FIELDS_META:
>>=20
>> `Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
>> |=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97|
>> (1, 1, 1003, '555-123-4567'),
>> (2, 2, 1003, ''),
>> (3, 3, 1003, '')
>>=20
>>=20
>> Then lets say the user wants to add a cell phone field.
>>=20
>> AFTER:
>>=20
>> CUSTOM_FIELDS:
>>=20
>> `Field_ID`, `Field_Group`, `Field_Label`, `Field_Name`, `Field_Slug`, =
`Field_Type`, `Field_Description`, `Field_Values`, `Field_Display`, =
`Field_Required`, `Field_Date_Created`
>> =
|=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=
=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=
=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=
=97=97=97=97=97|
>> (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')
>>=20
>> CUSTOM_FIELDS_META:
>>=20
>> `Meta_ID`, `Field_ID`, `Order_ID`, `Meta_Value`
>> |=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97=97|
>> (1, 1, 1003, '555-123-4567'),
>> (2, 2, 1003, ''),
>> (3, 3, 1003, '')
>>=20
>> 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.
>>=20
>> Hope that clears it up and not mud it up.. :P
>>=20
>> Best,
>>=20
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com
>>=20
>=20
> Hi Karl,
>=20
> 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) :
>=20
> 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 =3D table2.ID ;
> 1|100|1|1000
> 2|200|2|2000
> sqlite> select * from table1 left join table2 on table1.ID =3D =
table2.ID ;
> 1|100|1|1000
> 2|200|2|2000
> 4|400||
> sqlite> select * from table1 right join table2 on table1.ID =3D =
table2.ID;
> Error: RIGHT and FULL OUTER JOINs are not currently supported
>=20
>=20
> 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 ?
>=20
>=20
> If you simplify your query to the most basic form ( $SQL =3D "SELECT *
> 		FROM `CUSTOM_FIELDS` ft
> 		LEFT JOIN `CUSTOM_FIELDS_META` mt
> 		ON mt.Field_ID =3D ft.Field_ID;) , what does it yield ?
>=20
> If you create 2 equally simple test tables with no additional =
attributes per column, what does it yield ?
>=20
> 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.
>=20
>=20
> Hope this helps,
>=20
> Bert
>=20
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php