Pull data together

[radman08]

I have 7 tables with unique data in them collected over an extensive application. I want to bring all the data from each person who applies into one place, format it, and e-mail it. Each entry in each table has the username of the applicant posted in it. So for example if radman is my username, each time I submit a page in the application it stores my entries and my username in a table in MYSQL. Now when the application is finished and submitted I want to collect all the entries with userName=radman, format the data so that it is readable and coincides with the questions asked, then e-mail it. How do I do this most efficiently? Would a recordset (query) be best that joins them? If so HOW do I do that? Any help would be appreciated...

RADMAN

[bpat1434]

Easiest query would be:

SELECT table1., table2., table3., table4., table5., table6., table7.*
FROM table1, table2, table3, table4, table5, table6, table7
WHERE table1.username = 'radman'

But, that is a large result set, and you could have clashing column names. So your best bet is to define what columns you want from each table and then join them together using a LEFT or INNER join (note that the above query uses a cartesian join which helps inflate the size of the result set).

If you gave us your database structure, we might be able to give you a more defnitive answer, and possibly an easier way to set it up and do it.

[radman08]

Between 7 tables I have well over 100 fields of data that I collect. Listing the structures sounds like a nightmare. I would want to pull all the data so. Each table has a userName field that is filled by the session variable. Is there a way to maybe even have a form that I can enter the username and have it pull all the data for the entered username and spit it out on the page in a table?

[bpat1434]

It's possible, something like the following query:

SELECT t1.*, t2.*, t3.*, t4.*, t5.*, t6.*, t7.*
FROM [color=red]table1[/color] AS t1
LEFT JOIN [color=red]table2[/color] AS t2 ON t1.`username` = t2.`username`
LEFT JOIN [color=red]table3[/color] AS t3 ON t1.`username` = t3.`username`
LEFT JOIN [color=red]table4[/color] AS t4 ON t1.`username` = t4.`username`
LEFT JOIN [color=red]table5[/color] AS t5 ON t1.`username` = t5.`username`
LEFT JOIN [color=red]table6[/color] AS t6 ON t1.`username` = t6.`username`
LEFT JOIN [color=red]table7[/color] AS t7 ON t1.`username` = t7.`username`
WHERE t1.`username` = '[color=blue]<username>[/color]'

Now, it's a large query, but it will get what you want. I'd suggest, if at all possible, running this through PHPMyAdmin to see the results first before you start coding around what's returned. Make sure it gets the data you expect, then start messing around with the PHP side.

Also, just change the table names (those in red) to be the table names you use, and replace the blue "<username>" part with the session username (escaped by [man]mysql_real_escape_string[man]).

[Piranha]

Unfortunatley that query will give lots of duplicates if there is relationships that is not one to one. If it is only one to one relationships it is the way to go.

Let's study it with "only" 3 tables. The tables contains the following:

T1.username
abc

T2.username            T2.other
abc                    t2_1
abc                    t2_2

T3.username            T3.other
abc                    t3_1
abc                    t3_2

The result of the query bpat suggests would be the following:

T1.username        T2.username     T2.other      T3.username      T3.other
abc                abc             t2_1          abc              t3_1
abc                abc             t2_1          abc              t3_2
abc                abc             t2_2          abc              t3_1
abc                abc             t2_2          abc              t3_2

As you see it is duplicates of T2 and T3. Now, with 6 joined tables and only 2 rows per table it would be 64. With 3 rows in each it will be 729 rows. 10 rows would generate 1000000 rows. Yes, one million rows. This query would be slow, memory intensive and require lots of processor power to generate. And in PHP the handling would be a nightmare.

I suggest that you do it with lots of queries instead. Use one query to get the information from T1 and handle it in PHP. Another query will get the information from T2, and you can handle it in PHP. A third query for T3 and so on. The structure of the mail may or may not benefit from it, depending on if you want to present all the information from one table together or not.

[edited]As bpat pointed out it will of course only return results that have the same username as the one selected. What I ment was that if you have 10 rows each t2 to t6 with the username you enter (in the example above 'abc') it will return one million results. If you have other usernames (for example 'def') it won't add to the result.[/edited]

[bpat1434]

Piranha, not necessarily true. If you use an ____ join, and join the data on the one column they all share in common, then duplicates are alleviated. So running 7 different queries is the same as running 1 large query. My first query that i posted would indeed be a cartesian join which would in fact make multiple rows essentially the same data (although not exactly the same). My second query (which defines a join) would physically limit the result set to be those rows whose username is defined as "<username>" and only the data from the other tables who meet that same criteria.

Without structure of how the tables are defined, and how the relationships are mapped, this is a difficult question to answer definitively; however, either solution will work: (1) Run a large query to get a huge result set, or (2) run 7 small queries to get 7 small result sets.

[Piranha]

Bpat, of course you are right. It won't return rows with other usernames. I didn't point out explicitly that it would only return rows from each of the tables where username is what you search for. I thought it was clear that was implied. I have now edited the post above.

I still say that it is a better solution in this case (if not normally) to use 7 small queries if, and only if, there are one to many or many to many relationships. One to one relationships should be done in the same query.

[bpat1434]

I understand your point; however, I have to disagree. Just because there is a one to many, or many to many relationship does not automatically mean that you should not use one query to fetch data. There are plenty of times I use one to many relationships and one query to fetch data and it works as I expect every time.

For example, when I want to follow what posts a user has made inside a forum, I have a 1 to many relationship (1 user, many posts). So I execute the following query:

SELECT u.*, t2.*
FROM `users` u
INNER JOIN `posts` p
  ON u.id=p.poster_id
WHERE u.id=???

Thus I get all the posts related to that one person. And if I wanted to know what topic and forum they were in, it'd be as easy as:

SELECT u.*, p.*, t.*, f.*
FROM `users` u
INNER JOIN `posts` p
  ON u.id=p.poster_id
INNER JOIN `topics` t
  ON p.topic_id = t.id
INNER JOINT `forums` f
  ON t.forum_id = f.id
WHERE u.id=???

So what you would do in 4 queries, I've sliced down to one. When you get to larger datasets, that's when you have to start worrying about memory and execution time. Most likely it would take the MySQL server less time to traverse through the data and join the tables than it would for PHP to execute 4 different queries and manually loop over each result-set and then combine them in some fashion so it all made sense. And the best way to decide which method to use is to time both and see which one costs less time. And if possible, run "top" while executing that script to see how CPU and memory intensive the script becomes.

I understand what you're saying; however, using multiple queries is not always the best practice, especially if you have your tables set up right. Once again, without radman08 showing us his structure, it might be faster to do 7 queries, it could be faster to use joins. It could be slow either way and they need to tweak the structure before trying either of the methods presented here.

[radman08]

Cool! I feel like I am getting an understanding on where I am headed now. I would love to share my stucture with you. I am not sure how to though. Should I just copy and paste it here or give you the sql? What is the best way to post my structures so I can get the greatest help on this project?

Thank you.

[bpat1434]

Just do a structure dump from PHPMyAdmin and post it here in between &#91;code&#93; tags.

[Piranha]

I'm sorry, but I don't think you understand the point. I use joins when possible, and know them well. But you are missing one important difference between your example and radman's query.

In your example for forums I would use left joins exactly the way you do. In the forum example you have one relationship from users to posts, one from posts to topics and one from topics to forums. You have a chain of relationships. It means that the result will only contain one more row with a new post no matter if it is in a old topic or a new one.

But in radman's example all the relationships are to t1. It means that every combination of rows from the different tables will be shown, as long as the username is the same.

I suggest that you try it out to fully understand the difference. Set up one database with a chain of relationships as you mention with user, posts, topics and forums and add a user and 10 rows in the other tables with with relationships to each other. It could of course be 10 topics in the same post or 1 topic for each post. The result should be 10-28 rows returned. Set up another database (or another set of tables) with t1, t2, t3 and t4 and set up one row in t1 and 10 rows in each of the other tables with relationship to t1. Now run the query, it should return 1000 rows.

[bpat1434]

Piranha: I fully understand the example however, you're missing my point. If I run the first query, yes, I'd get a Cartesian join in which all rows would be returned with every possible combination of data from t2 - t7 merged to t1. But if I specify the column on which the tables are to be joined, then I remove the redundancy in the join and thus have only a select few rows that I expect. I'm very well versed in the SQL ways and I have done this plenty of times to know the difference between a typical Cartesian join and a left, Inner, Natural, Outer, or right join.

I think I see where you're getting mixed up. Yes, multiple rows for one "username" will be returned; however, it will only be the maximum number of rows contained in one table. So if in the table posts a user has 5 rows, but only one row in the "topics" table, then there will be 5 rows returned for the result-set. The only difference being the specific post data. It's not a straight cartesian join where each row of every table is matched against each row of every other table. It's possible to use this type of joining query, you just have to be careful.

If we had a real structure to work with, one which he could show us, we could definitely help him better. Maybe even clean it up so he doesn't require 7 tables 😉 Then this whole argument about joins and stuff would be irrelevant (in a manner of speaking).

[Piranha]

bpat, are you saying that it would return only two rows with the example I made a few posts ago? There are a maximum of two rows in each table, but an left join the way you suggest returns 4 rows. I tried it out to make sure after your first objection, and it works as I expected. But I won't continue this discussion since it won't lead anywhere. Still I'm a bit suprised that you won't test it out for yourself.

But I do agree that it would be much better if we had the table structure, it would be much easier to help out.

radman, I suggest that you try both the way I suggest and the way bpas suggest and decide for yourself what way is best. You don't have to do it in PHP to start with, just try it out in phpmyadmin or whatever database handler you use.

[bpat1434]

I have tested it myself, with 7 tables all having one row except for another which has 2. I then queried using a similar query as above and I got 2 rows, exactly what I expected.

[Piranha]

With one row in 6 of the tables and 2 rows in the seventh you would get that result. My point is that you would get 64 rows if all tables 2-7 have 2 rows each.

[radman08]

Here is my structure.

-- 
-- Table structure for table `student_app1`
-- 

CREATE TABLE `student_app1` (
  `userName` text NOT NULL,
  `First Name` text NOT NULL,
  `Last Name` text NOT NULL,
  `Nickname` text NOT NULL,
  `E-mail Address` varchar(50) NOT NULL default '',
  `E-mail Address Conf` varchar(50) NOT NULL default '',
  `month` varchar(40) NOT NULL default '',
  `day` varchar(40) NOT NULL default '',
  `year` varchar(40) NOT NULL default '',
  `Age` int(2) NOT NULL default '0',
  `Gender` text NOT NULL,
  `Citizenship` text NOT NULL,
  `Marital Status` text NOT NULL,
  `Current Address` text NOT NULL,
  `Current Phone` varchar(20) NOT NULL default '',
  `Permanent Address` text NOT NULL,
  `Permanent Phone` varchar(20) NOT NULL default '',
  `Cell Phone` varchar(20) NOT NULL default '',
  `Best Time to Reach` text NOT NULL,
  `Fathers Name` text NOT NULL,
  `Fathers Phone` varchar(20) NOT NULL default '',
  `Fathers E-Mail` varchar(50) NOT NULL default '',
  `Mothers Name` text NOT NULL,
  `Mothers Phone` varchar(20) NOT NULL default '',
  `Mothers E-Mail` varchar(50) NOT NULL default '',
  `Emergency Name` text NOT NULL,
  `Emergency Relation` text NOT NULL,
  `Emergency Phone` varchar(20) NOT NULL default '',
  `Emergency E-mail` varchar(50) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Student Application Page 1';

-- --------------------------------------------------------

-- 
-- Table structure for table `student_app2`
-- 

CREATE TABLE `student_app2` (
  `userName` text NOT NULL,
  `other` text NOT NULL,
  `Highschool Completion` int(11) NOT NULL default '0',
  `Highscholl GPA` int(11) NOT NULL default '0',
  `Highschool Graduation` int(11) NOT NULL default '0',
  `Coll/Univ Completion` int(11) NOT NULL default '0',
  `Coll/Univ GPA` int(11) NOT NULL default '0',
  `Coll/Univ Graduation` int(11) NOT NULL default '0',
  `Sch/Coll Name` text NOT NULL,
  `Degree/Major` text NOT NULL,
  `Minor(s)` text NOT NULL,
  `Extra Curr Activities` text NOT NULL,
  `Recieving credit` text NOT NULL,
  `Area of academia` text NOT NULL,
  `Credit Responsibilities` longtext NOT NULL,
  `How did you hear about RAD` text,
  `Search enginge` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Student Application Page 2';

-- --------------------------------------------------------

-- 
-- Table structure for table `student_app3`
-- 

CREATE TABLE `student_app3` (
  `userName` text NOT NULL,
  `Autobiography` longtext NOT NULL,
  `Self Portrait` text NOT NULL,
  `img` int(11) NOT NULL default '0',
  `data` longtext NOT NULL,
  PRIMARY KEY  (`img`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Student Application Page 3';

-- --------------------------------------------------------

-- 
-- Table structure for table `student_app4`
-- 

CREATE TABLE `student_app4` (
  `userName` text NOT NULL,
  `Ref1 Relation` text NOT NULL,
  `Ref1 Name` text NOT NULL,
  `Ref1 Address` text NOT NULL,
  `Ref1 City` text NOT NULL,
  `Ref1 State` text NOT NULL,
  `Ref1 Zip` int(11) NOT NULL default '0',
  `Ref1 Phone` varchar(20) NOT NULL default '',
  `Ref1 E-Mail` varchar(50) NOT NULL default '',
  `Ref2 Relation` text NOT NULL,
  `Ref2 Name` text NOT NULL,
  `Ref2 Address` text NOT NULL,
  `Ref2 City` text NOT NULL,
  `Ref2 State` text NOT NULL,
  `Ref2 Zip` int(11) NOT NULL default '0',
  `Ref2 Phone` varchar(20) NOT NULL default '',
  `Ref2 E-mail` varchar(50) NOT NULL default '',
  `Ref3 Relation` text NOT NULL,
  `Ref3 Name` text NOT NULL,
  `Ref3 Address` text NOT NULL,
  `Ref3 City` text NOT NULL,
  `Ref3 State` text NOT NULL,
  `Ref3 Zip` int(11) NOT NULL default '0',
  `Ref3 Phone` varchar(20) NOT NULL default '',
  `Ref3 E-mail` varchar(50) NOT NULL default '',
  `PBI 1` text NOT NULL,
  `PBI 2` text NOT NULL,
  `PBI 3` text NOT NULL,
  `PBI 4` text NOT NULL,
  `PBI 5` text NOT NULL,
  `PBI 6` text NOT NULL,
  `PBI 7` text NOT NULL,
  `PBI 8` text NOT NULL,
  `PBI 9` text NOT NULL,
  `PBI 10` text NOT NULL,
  `PBI 11` text NOT NULL,
  `PBI 12` text NOT NULL,
  `PBI 13` text NOT NULL,
  `PBI 14` text NOT NULL,
  `PBI 15` text NOT NULL,
  `PBI 16` text NOT NULL,
  `PBI 17` text NOT NULL,
  `PBI 18` text NOT NULL,
  `PBI 19` text NOT NULL,
  `PBI Explanation` text,
  `Tendency 1` text,
  `Tendency 2` text,
  `Tendency 3` text,
  `Tendency 4` text,
  `Tendency 5` text,
  `Tendency 6` text,
  `Tendency 7` text,
  `Tendency 8` text,
  `Tendency 9` text,
  `Tendency 10` text,
  `Tendency 11` text,
  `Tendency 12` text,
  `Tendency 13` text,
  `Tendency 14` text,
  `Tendency 15` text,
  `Tendency 16` text,
  `Tendency 17` text,
  `Tendency 18` text,
  `Tendency 19` text,
  `Tendency 20` text,
  `Tendency 21` text,
  `Tendency 22` text,
  `Tendency 23` text,
  `Tendency 24` text,
  `Tendency 25` text,
  `Tendency 26` text,
  `Tendency 27` text,
  `Tendency 28` text,
  `Tendency 29` text,
  `Tendency 30` text,
  `Tendency 31` text,
  `Tendency 32` text,
  `Tendency 33` text,
  `Tendency 34` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Student Application Page 4';

-- --------------------------------------------------------

-- 
-- Table structure for table `student_app5`
-- 

CREATE TABLE `student_app5` (
  `userName` text NOT NULL,
  `Why are you applying` longtext NOT NULL,
  `Do you have a hunger and passion` longtext NOT NULL,
  `What are you spiritual gifts` longtext NOT NULL,
  `Experiences of sharing faith` longtext NOT NULL,
  `Testimony` longtext NOT NULL,
  `Who is God` longtext NOT NULL,
  `What is a personal relationship` longtext NOT NULL,
  `Experience with Holy Spirit` longtext NOT NULL,
  `Devotional Time` longtext NOT NULL,
  `Been Learning` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Student Application Page 5';

-- --------------------------------------------------------

-- 
-- Table structure for table `student_app6`
-- 

CREATE TABLE `student_app6` (
  `userName` text NOT NULL,
  `Home Life` longtext NOT NULL,
  `Parental Approval` longtext NOT NULL,
  `Role Model` longtext NOT NULL,
  `Church Life` longtext NOT NULL,
  `Team Experience` longtext NOT NULL,
  `Strengths and weaknesses` longtext NOT NULL,
  `Finacial Support` longtext NOT NULL,
  `If not accepted` longtext NOT NULL,
  `What changes` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Student Application Page 6';

-- --------------------------------------------------------

-- 
-- Table structure for table `student_app7`
-- 

CREATE TABLE `student_app7` (
  `userName` text NOT NULL,
  `Agreement` text NOT NULL,
  `Release Initial` text NOT NULL,
  `Release Date` varchar(40) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Student Application Page 7';

[bpat1434]

I guess Ihave a question. Is there a reason you don't use an id number to link up all the users to their information. With large tables, it can be faster than text sometimes.

My other question is why don't you just make this one large table? Then it's one simple query. And unless you're storing actual images, it will be fine.

Now for some tips:

1.) Name your tables something meaningful like "users" or "students" for student_app1, "education" for student_app2 etc. This will make it more clear when you write your PHP code just what is inside those tables.

2.) Student_app4 (which I'm going to call "references") can be trimmed down. You don't need all 3 references as columns, you could just do "ref Name", "ref Relation", "Ref Address" etc. and have an extra row for each reference. Keeps it cleaner and can make it faster as well.

3.) In Student_app4 you should probably break out PBI and Tendancies into their own tables so that you can just have multiple rows for a user rather than having 30 some columns in one table 😉

Those are the basics. I'd definitely think of adding an "id" field and referencing that during joining. So the student_app1 table would have an id field, the rest would have "id" and "user_id" where "user_id" is a foreign key to the users table (student_app1) and every "id" field is an auto_increment field. Making sense now?

If you have any questions, feel free to ask.

[radman08]

Once I have all the data collected how can I get it to e-mail? Right now I am thinking about having the last page of the application bring in all the data then when submitted it will e-mail. How do I e-mail the contents of a page when submit button is hit?

[Piranha]

I have a few things to add to bpats list:

1. Read up on database normalization. And use it. The way you do it now will cause trouble now and in the future. It is better to put in the extra hours to make it right now than do workarounds all the time. A few examples: How do you handle a change to allow 5 references instead of 3? Or a search in tendency? Or a person that have double college educations? If you decide to solve only one of the points we have here this is the one to solve.

2. It is good that you use NOT NULL on the username. But as it is now you could easily end up with more users with the same username. You should do it unique as well as not null in your base table, which I guess is the students table.

3. You should think about the data types in the database. For example, do you think that you will get user names that are more than 255 characters? Or role model that is more than 12 pages long?

Just for the record I agree to everything on bpats list.

[radman08]

I will work on normalizing my database. You guys have a lot of good advice. So after I have my database normalized and ready to go, I have an application that is efficiently entering data, I have a page that pulls all the data for x person into a formated html web page with the application questions and the input of x listed neatly side by side how do I then have x press submit and have the formated html page submit/send to an e-mail address? I would like to preserve the layout and look of the html/php page in the e-mail and have it sent in for review, etc. How?

Thank you so much for all the help you have been giving me. This is great.