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

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

2016-09-19 Thread Karl DeSaulniers

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

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

2016-09-13 Thread B. Aerts


On 13/09/16 08:42, Karl DeSaulniers wrote:

On Sep 12, 2016, at 2:53 PM, B. Aerts  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 






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;

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

2016-09-13 Thread Karl DeSaulniers

> On Sep 12, 2016, at 2:53 PM, B. Aerts  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 
>> 
>> 
>> 
>> 
>> 
> 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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

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

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

3 matches

Site Navigation

Mail list logo

Footer information