Trouble wrapping head around joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Phix
Forum Newbie
Posts: 21
Joined: Tue Jan 12, 2010 11:50 pm

Trouble wrapping head around joins

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Trouble wrapping head around joins

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Phix
Forum Newbie
Posts: 21
Joined: Tue Jan 12, 2010 11:50 pm

Re: Trouble wrapping head around joins

Post 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 :)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Trouble wrapping head around joins

Post by Eran »

what exactly created this database structure? don't you have any control over it?
Post Reply