What is a best way to maintain a table?

[7webcreator]

Hello,
I have a table in which I would like to change the type of one element. Currently the table contains more than 1000 entries and the element I would like to modify is a text field type.
What is the best way to change the type element to a databasejoin type without losing the current data of this element?

 

[cheesegrits]

So you want to extract the data currently in that field into a separate table, and convert the field in the existing table from being the data itself, to the FK value joining it to the new table?

So say the existing field was 'color', with values like red, green and blue. You want to create a new table 'colors', like ...

id,color
1,red
2,green
3,blue

... and convert the existing color field to a join, with values 1 for red, 2 for green etc.

-- hugh

 

[7webcreator]

Thank you for your reply.
Yes indeed. Does it mean that I have to create the "color" table and populate it with the corresponding data (red, green, blue) before amending the field element (color) in the main table?
So when I amend the field element of the main table as a database join element, will the main table still keep its data (1000 lines)?

 

[troester]

I would
in Fabrik:
create the new color table, populate, e.g
id, color
1 green
2 red

in phpMyAdmin: assuming your data table is mydata with a column color
backup mydata table!!!
rename column color to color_text
add new column color
run SQL

update mydata set color = ( case 
when color_text = 'green' then '1' 
when color_text = 'red' then '2' 
else null end)

in Fabrik
change your element color to databasejoin

if all is ok you can remove column color_text

 

[7webcreator]

Thank you. If I follow your code text above, it is fine for colors as there are not many. But in my case, does it mean that I have to manually enter the 1000 lines with their id?

 

[cheesegrits]

Yup, that was going to be my next question, is how many different values we were talking about.

OK, so the way to do it is like this. I'll stick with the colors example, just substitute as necessary, and I'll call the original table 'original'. You'll need to run some manual queries in your MySQL client (probably phpMyAdmin).

As Troester said, first thing is BACK UP YOUR TABLES!

Create your new table, either in Fabrik (create a list) or in your MySQL client. Make sure it has an auto incrementing primary key, 'id' (which will happen automatically if you create it in Fabrik) and a field for 'color'. So at the very least you have two fields in the new 'colors' table ... id and color. The 'color' field must obviously be the same type (probaby VARCHAR) as the original field.

Create the new rows in the colors table by running this:

INSERT INTO colors (color) SELECT DISTINCT color FROM original

That should create one row for each distinct color used in original. So if you have 1000 rows in original which between them use 200 different colors, you'll have 200 rows in in color. If they were only chosen from 5 colors, you'll have 5 rows. if every row had a different color, you'll have 1000 rows.

Now we have to change the 'color' on the original table to be a foreign key pointing to the relevant row in the colors table. To be safe, we'll do this by setting a temporary copy of the field. So create a new field on 'original' called color_2, making it an INT (unsigned). You don't have to do that in Fabrik, just create it in MySQL, we're going to get rid of it soon.

Then run this query:

UPDATE original LEFT JOIN colors ON color.color = original.color SET original.color_2 = color.id

That will set color_2 to the id of the corresponding row in colors.

Sanity check this. Examine lots of example rows, and make sure color_2 does indeed have the right value in it, matching the color in the colors table.

Now either drop the original color field, or rename it to color_3 (if youw ant to be extra safe and keep it in case we messed up).

Rename the color_2 field we created to just 'color', so it is now the "real" field.

Go in to Fabrik, and change the 'color' element to be a join to the new colors table, using 'id' as the value, and 'color' as the label.

-- hugh

 

[7webcreator]

Many thanks for your valuable info. You made me cleverer.
I managed to create the new rows in the new table using your code. I have a question: what is if my new table has more than one column? Currently the new table (colors=exhibitors) has the fields below:


For the second step, I have created a new column exhibitors2 with the specifications below:


and then I ran the below code. I encountered an error while running the second code on the original table (orders) after having created a new color_2 column (exhibitors2).
I replaced color, colors and original by
colors=exhibitors
color=Exhitors
color_2=exhibitors2

UPDATE orders LEFT JOIN exhibitors ON Exhibitors.Exhibitors = orders.Exhibitors SET orders.exhibitors2 = Exhibitors.id



Could you please let me know what I'm doing wrong and how can I apply the same process to the other fields of the new table.

Thank you

 

[troester]

Maybe a case issue, your table name is exhibitors (not Exhibitors)
UPDATE orders LEFT JOIN exhibitors ON exhibitors.Exhibitors = orders.Exhibitors SET orders.exhibitors2 = exhibitors.id

 

[7webcreator]

Thank you. I tried your code and got this error message, why

 

[troester]

As the error says: your columns orders.Exhibitors and exhibitors.Exhibitors have different collations (Interclassement)

 

[7webcreator]

What does that mean?

 

[troester]

If you don't know I assume you didn't set it yourself during table creation.

Collation is the character set used in your database. It can also be set on table and column level.
It seems your table orders has collation utf8_general_ci, your table exhibitors utf8_unicode_ci (I really don't know what's the difference between these ones).

To get the SQL running and to avoid later issues you should change the table and column collations to be alle the same

But I don't know your database collation, why your tables have different ones and which is the "correct" one.

 

[7webcreator]

Thank you. Thank you thousand times. It works
I changed the collation of the tables so that they both agree and it works perfectly now. That's great.
I have now the other fields of the new table (exhibitors) to update. What is the best way to do it so that all the corresponding fields have the right values?

 

[cheesegrits]

First ... BACK YOUR TABLES UP again before doing this.

Create the coresponding columns in the new table (you can do it through Fabrik, by adding them as elements to the new form) then in MySQL ...

UPDATE exhibitors AS e LEFT JOIN orders AS o ON o.Exhibitor = e.id SET e.foo = o.foo, e.bar = o.bar

Replace foo and bar with the field names, and add more (comma separated) as required. Test to make sure it worked, then in Fabrik delete the old elements and empty the Element trash - select "trashed" from the "status" filter, select everything, "Empty Trash", and say Yes to "Drop database field?".

Well, when I say "select everything", make sure you aren't selecting any elements you may have deleted prior to this, which might still exist in other copies of lists.

-- hugh

 

[7webcreator]

I have created the corresponding fields in the new table and replace any foo and bar with the respective fields as below
UPDATE exhibitors AS e LEFT JOIN orders AS o ON o.Exhibitors = e.id SET o.Custname = e.Custname, o.CustPhone = e.CustPhone, o.CustEmail = e.CustEmail, o.custo_info_add = e.custo_info_add, o.3d_image = e.3d_image

The result is that both the tables have their respective columns empty. Where is the issue?

 

[cheesegrits]

You have the e's and o's the wrong way round. You may have seen my last post before I corrected it, to have "e.foo = o.foo". I edited my post about 2 minutes after originally posting it when I realized I had them the wrong way round.

So what you are doing is setting the order fields to equal the exhibitor fields ... which are of course empty.

Luckily you backed up before doing it, right?

-- hugh

 

[7webcreator]

Fortunately, as I'm not a rocket in php, I followed your instructions to the letter. Yes I had backed up before

So now it is working as it should be. However, I noticed that in the process, it does not select the last changes made on a designated id. For example, on the orders table, the exhibitors has several shows during the year and has his contact details changed during the year. How can I have it that the code selects the last update info found?

Also, you suggest to go in Fabrik, to delete the old elements and empty the Element trash. What is if I want to keep those fields on the orders form. In another words, to be able to update the exhibitors table from the orders table. Is it possible?

 

[cheesegrits]

Use the autofill form plugin.


Sent from my Nexus 7 using Tapatalk

 

[7webcreator]

Thank you, that work perfectly for the autofill form plugin. It took me some times to find my way around but I managed. You are the best.
Just need to know how to get the last entries to be selected using the code you kindly gave me before. Please see my first question in the post above.

 

[cheesegrits]

I'm not sure what you mean. The exhibitors table will presumably just have one row per exhibitor, with their current details. So when you select and existing exhibitor on the order form, the autofill should grab those details.

-- hugh