Thread

I have a weblog with some custom fields, 2 of which are dropdown menus. The weblog is populated with data (1000+ entries).

To aid I would maintainability I would like to migrate the dropdown menus to relationship fields.

I have set up two new weblogs, and populated these with entries with titles exactly matching the options in the two dropdown menus in the first weblog.

In the first weblog I have set up the two new relationship fields linked to each one of the new weblogs.

I thought that a weblog relationship field would just store the ID of the related weblog entry in the custom field in the DB, but checked and it uses a separate relationship table.

I am having trouble working out how I can automatically, via a query or a bit of php create all these relationships using the current data in the dropdown fields.

Could you advise how I can achieve this please.

Many thanks,

Meirion

I’m not entirely clear on what you’re doing- sounds like an import?  But take a look at this wiki entry to get a feel for how relationships work in terms of the db.  That help clarify?

Thanks for that link Robin.

Sorry it was a little hard for me to explain.

I have a weblog full of entries for ‘certificates’. Certificates have an ‘issueing authority’ and relate to a ‘product’.

Currently I have two dropdown fields on the certificates weblog containing authorities and products, so that data is stored as text against each certificate entry.

I want to abstract this data into two linked weblogs - authorities and products, for obvious reasons (maintainablity etc). So instead of using text dropdowns, it uses related fields.

Now, I’ve actually got part of the way there, but have come across a new problem. With some digging around and the info on that wiki entry I came up with these SQL statements:

Initially, I created the new entries in the authorities weblog for each option that was on the certificates.authority dropdown field.

1) This inserts a relationship for each of the entries in my certificates weblog, pulling in the parent and child IDs from the certificates weblog and the authorities weblog titles (that I just set up). Field 32 is the dropdown (text) field with the authorities.

INSERT INTO exp_relationships (exp_relationships.rel_parent_id, exp_relationships.rel_child_id)
SELECT
certs.entry_id AS certid,
certauths_titles.entry_id AS certauth_id
FROM
exp_weblog_data AS certs
Inner Join exp_weblog_titles AS certauths_titles ON certs.field_id_32 = certauths_titles.title

affected 1138

2) Now a little tweak just to add the ‘blog’ field, manually editing the rel_id:

UPDATE exp_relationships SET exp_relationships.rel_type = 'blog' WHERE rel_id >= '1226';

affected 1138

3) Now I am putting the relationship IDs into the certificates weblog (id 9). field 51 is the new relationship field.

UPDATE exp_weblog_data, exp_relationships SET exp_weblog_data.field_id_51 = exp_relationships.rel_id WHERE exp_weblog_data.weblog_id = '9' AND exp_weblog_data.entry_id = exp_relationships.rel_parent_id

affected 1138

Now… this looks like it worked. When I browse the certificates weblog, the authorities relationship field is correctly set for each entry.

However I now cannot add any new entries to ANY weblog, and I cannot add any new fields in the weblog section.
When I try to do either of these actions the page goes blank and loads for a long time, then firefox displays:
“Connection Interrupted      

The document contains no data.”

I’ve emptied all caches, and tried an optimize and repair on all the tables (no errors found).
What could be the problem? Thanks!

Hm- in truth, I’d have probably done it via php so I could output the updates before actually running them.  And I hope you have a good backup of the database, because you may end up rolling back.

That said- the best way to spot what’s wrong is to pick one case and go through the tables, trying to spot where it doesn’t match. 

I suspect you may have created some duplicates, but hard to say w/out running some tests.  But if you track one- from the related id in the exp_weblog_data field to the exp_relationships content, I’m betting you’ll spot where it went wrong.

And since we’re talking custom coding, I’m going to shift this over to ‘How to’.  But- can you spot where the matchup fails- looking at the current data?