Re: MySQL two tables and an uneven number of rows

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 ?

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