通过公共名称字段从更多表mysql（PHP）中选择数据

Question

My problem is that I would like get a query mysql for PHP to select all data from 3 different columns ordered by a field that name is the same, (in my case, a 'data' field). My target is printing all rows in this particular order and NOT all rows of a tabel after the rows of the another one. Can someone provide me the right way to get?

An example: I've four tables:

users_type1(**id**, name, surname, registration_date), 
users_type2(**id**, town, school, registration_date), 
users_type3(**id**, business, town, registration_date), 
users_type4(**id**, age, school, registration_date) 

So, my target is print the name of all this users order by the registration date.

Answer 1

Ok, I will take a crack at this. I am not positive I understand your question, but let's see. I think what you may want here is a union statement in your query.

Given the following test tables:

CREATE TABLE `users_type1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

CREATE TABLE `users_type2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `school` varchar(255) DEFAULT NULL,
  `registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

CREATE TABLE `users_type3` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `business` varchar(255) DEFAULT NULL,
  `registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

CREATE TABLE `users_type4` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `age` varchar(255) DEFAULT NULL,
  `registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

And then inserting the following test data:

INSERT INTO users_type1(name) VALUES ("Bob");
INSERT INTO users_type2(business) VALUES ("Wendy's");
INSERT INTO users_type3(business) VALUES ("Wendy's");
INSERT INTO users_type2(school) VALUES ("LSU");
INSERT INTO users_type4(age) VALUES ("61");
INSERT INTO users_type3(business) VALUES ("IBM");
INSERT INTO users_type3(business) VALUES ("Apple");
INSERT INTO users_type4(age) VALUES ("23");
INSERT INTO users_type4(age) VALUES ("29");
INSERT INTO users_type2(school) VALUES ("Penn State");
INSERT INTO users_type1(name) VALUES ("Amanda");
INSERT INTO users_type1(name) VALUES ("Amir");
INSERT INTO users_type2(school) VALUES ("Ohio State");
INSERT INTO users_type4(age) VALUES ("41");
INSERT INTO users_type2(school) VALUES ("UC Berkley");

I can use this select (note that the FIRST select in the union statement determines what the column names in the final result will be):

(SELECT id, name AS data, registration_date FROM users_type1) 
UNION 
(SELECT id, school, registration_date FROM users_type2) 
UNION 
(SELECT id, business, registration_date FROM users_type3) 
UNION 
(SELECT id, age, registration_date FROM users_type4) 
ORDER BY registration_date;

And the result is:

+----+------------+---------------------+
| id | data       | registration_date   |
+----+------------+---------------------+
|  1 | Bob        | 2014-04-12 15:48:30 |
|  1 | Wendy's    | 2014-04-12 15:48:52 |
|  1 | LSU        | 2014-04-12 15:49:06 |
|  1 | 61         | 2014-04-12 15:49:15 |
|  2 | IBM        | 2014-04-12 15:49:22 |
|  3 | Apple      | 2014-04-12 15:49:25 |
|  2 | 23         | 2014-04-12 15:49:29 |
|  3 | 29         | 2014-04-12 15:49:34 |
|  2 | Penn State | 2014-04-12 15:49:42 |
|  2 | Amanda     | 2014-04-12 15:49:48 |
|  3 | Amir       | 2014-04-12 15:49:54 |
|  3 | Ohio State | 2014-04-12 15:50:01 |
|  4 | 41         | 2014-04-12 15:50:06 |
|  4 | UC Berkley | 2014-04-12 15:50:13 |
+----+------------+---------------------+

All of that being said, I would think hard about using 4 different tables to represent users. I would think you could save a lot of complexity by using a single table, with a 'user_type' field and either combine all of your additional fields into this single table, or have separate tables (eg: type1_user_properties, type2_user_properties, etc) which you join in.