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

> 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


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`
=
|=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')

CUSTOM_FIELDS_META:

`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, '')


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`
=
|=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')

CUSTOM_FIELDS_META:

`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, '')

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