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.
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.