Page 1 of 1

Trouble wrapping head around joins

Posted: Sun Feb 21, 2010 3:14 am
by Phix
Hey all.. using MySQL 5.0.

Through google searches and a little elbow grease, I was able to extract the contacts data off my iPhone and put it into a mysql database, well, two. That's where my problem comes in.

The structure of what the sqlite manager spit out was wonky, IMO. Taking out all the NULL and never used tables for what I need (essentially just an XML file), it's as such:

-ABMultiValue-
UID : From what I can gather this is just a unique id number for each contact entry, i.e. mobile, home, email, etc.
record_id : The AUTO_INCREMENTing id of each contact entry.
value : The value of the field, phone number, email address, web, etc.

-ABPerson-
ROWID : Points to the record_id of the previous table.
First : ...name
Last : ...name

Here's my problem - I'd like to connect via php and extract all that data into a singular XML file, like so:

Code: Select all

 
 
<contacts>
   <entry id="103">
      <name>Andrew Smith</name>
      <phone id="1">4085551212</phone>
      <phone id="2">4085551245</phone>
      <email>smithA@gmail.com</email>
   </entry>
</contacts>
 
 
The problem I'm running into is getting the information out of MySQL in such a way that I can loop through, I'm guessing a nested WHILE loop in the PHP script generating the XML doc (via the DOM, case that helps).

Current query:

Code: Select all

 
SELECT ABMultiValue.UID, ABMultiValue.value, ABPerson.first
FROM ABPerson
LEFT JOIN ABMultiValue ON ABMultiValue.record_id = ABPerson.ROWID ORDER BY ABMultiValue.UID DESC
 
Which for one of my multi-element contacts comes in as such:

Code: Select all

 
UID        value
1   1 (408) 555-5544
112 mro@gmail.com
113 1 (408) 555-1212
 
Looks easy, but maybe I'm just exhausted at this point to see it.

I could do a few nested queries, but I'm tryin to learn more efficient logic.

Re: Trouble wrapping head around joins

Posted: Sun Feb 21, 2010 4:13 am
by VladSun
Blah :mrgreen:

That's quite an awful export DB structure :)
I think that before you try to parse/extract contacts from this data, you should try other options - what about saving your contacts as vCard? I do think it will be far easier to parse them, put data into a normalized DB and generate XMLs.

Re: Trouble wrapping head around joins

Posted: Sun Feb 21, 2010 11:10 am
by Phix
Yea no kidding. I'm glad I at least got this far, but I wonder about DB structures sometimes...

I'll probably just do it dirty and have nested queries. I was planning on exporting to vCard after I got an XML structure I was happy with, so we're on the same page there :)

Re: Trouble wrapping head around joins

Posted: Sun Feb 21, 2010 11:32 am
by Eran
what exactly created this database structure? don't you have any control over it?