Re: MySQL two tables and an uneven number of rows

"B. Aerts" <ba_aerts@xxxxxxxxx> · Tue, 13 Sep 2016 09:15:46 +0200

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