Trouble wrapping head around joins
Posted: Sun Feb 21, 2010 3:14 am
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:
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:
Which for one of my multi-element contacts comes in as such:
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.
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>
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
Code: Select all
UID value
1 1 (408) 555-5544
112 mro@gmail.com
113 1 (408) 555-1212
I could do a few nested queries, but I'm tryin to learn more efficient logic.