Page 1 of 1
MySQL Join CSV
Posted: Tue May 23, 2006 9:42 am
by caleb
Hello and thanks for taking the time to look at my post! I just found this forum - I have been looking for a good PHP forum for a while. Anyway, I was wondering if somebody out there could help me!
Here is an example of the data I am working with:
Table: Users
ID | Name | User | Password
Table: Questions
ID | Question
Table: Answers
User ID | Question ID | Answer
Now I am needing to export a CSV file from this data (based per user). To make things more complicated a question can have more than one answer. Do you know of a way I can do this easily with MySQL? Basically, any way we can handle the questions within the CSV file is fine. Any help you could give on this would be great.
Posted: Tue May 23, 2006 10:02 am
by GM
What about "SELECT field1, field2,...fieldn INTO OUTFILE myFile.txt FROM table1, table2 ... tablen WHERE ..." ?
You can use the optional FIELDS TERMINATED BY, FIELDS ESCAPED BY and LINES TERMINATED BY to format the file correctly.
MySQL
Posted: Tue May 23, 2006 10:24 am
by caleb
Thanks for your help. I need this to be done in PHP (connecting to the MySQL DB of course) but I need to do several joins to compile and link all the data - I am just not quite sure how. You will have to forgive me I am pretty good with PHP but I am quite lacking when it comes to MySQL.
Here is what I need the end result to look like:
Name, Email, Question 1, Question 2, Question 3, Question 4
Caleb,
caleb@yahoo.com, answer 1, , answer 3, answer 4
The bumps I am hitting:
Some of the questions are not required to be answered there for I can not use just a for each on a count
Also, I am not sure how toget everything to line up that way and break to another line when a new user is started.
Does anybody have any ideas?
Caleb
Posted: Tue May 23, 2006 12:39 pm
by GM
OK - Firstly, there is no way you will ever get the data out of the database in that format, so you've got to do some formatting in PHP, but you really don't need complicated joins - I've joined the three tables in the simplest way possible:
Code: Select all
mysql> SELECT * FROM T_USERS;
+----+-------+-----------+
| ID | UNAME | EMAIL |
+----+-------+-----------+
| 01 | Dave | Dave@here |
| 02 | Bill | Bill@here |
| 03 | John | John@here |
+----+-------+-----------+
3 rows in set (0.09 sec)
mysql> SELECT * FROM T_QUESTIONS;
+----+--------------------------------+
| ID | QUESTION |
+----+--------------------------------+
| 01 | What is your name? |
| 02 | What is your quest? |
| 03 | What is your favourite colour? |
+----+--------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM T_ANSWERS;
+---------+-------------+--------------------------+
| USER_ID | QUESTION_ID | ANSWER |
+---------+-------------+--------------------------+
| 1 | 1 | My name is Dave |
| 1 | 1 | My name is probably Dave |
| 1 | 2 | |
| 1 | 3 | Blue |
| 2 | 1 | |
| 2 | 2 | I seek the grail |
| 2 | 2 | I still seek the grail |
| 2 | 3 | Yellow |
| 3 | 1 | Ummm. |
| 3 | 2 | I don't know |
| 3 | 2 | Not sure |
| 3 | 3 | Green |
| 3 | 3 | Orange |
+---------+-------------+--------------------------+
13 rows in set (0.00 sec)
mysql> SELECT U.ID, U.EMAIL, Q.QUESTION, A.ANSWER
-> FROM T_USERS U, T_QUESTIONS Q, T_ANSWERS A
-> WHERE U.ID = A.USER_ID
-> AND Q.ID = A.QUESTION_ID;
+----+-----------+--------------------------------+--------------------------+
| ID | EMAIL | QUESTION | ANSWER |
+----+-----------+--------------------------------+--------------------------+
| 01 | Dave@here | What is your name? | My name is Dave |
| 01 | Dave@here | What is your name? | My name is probably Dave |
| 01 | Dave@here | What is your quest? | |
| 01 | Dave@here | What is your favourite colour? | Blue |
| 02 | Bill@here | What is your name? | |
| 02 | Bill@here | What is your quest? | I seek the grail |
| 02 | Bill@here | What is your quest? | I still seek the grail |
| 02 | Bill@here | What is your favourite colour? | Yellow |
| 03 | John@here | What is your name? | Ummm. |
| 03 | John@here | What is your quest? | I don't know |
| 03 | John@here | What is your quest? | Not sure |
| 03 | John@here | What is your favourite colour? | Green |
| 03 | John@here | What is your favourite colour? | Orange |
+----+-----------+--------------------------------+--------------------------+
13 rows in set (0.11 sec)
Then, using this array, you should be able to loop through and structure your CSV rows, starting a new row when the User value changes.
Your problems are going to be that if one user has given two answers to the same question, your formatting is going to be screwed, so you need to handle that by maybe delimiting the field data in such a way that you know after it contains 2 answers, so that your CSV row looks like User A, Email A, "Answer 1 to Question 1|Answer 2 to Question 1", "Answer 1 to Question 2|Answer 2 to Question 2" ... etc, where I've used the pipe | as a delimiter between two answers.
It seems complex, what you are asking, but if you can get the data from the database in the format I've given above, then it's just a case of looping through it in PHP, and formatting the row correctly.
Thanks
Posted: Tue May 23, 2006 12:45 pm
by caleb
Thanks so much! That helps a ton - now I will just go through and play with the php!
Caleb