I was wondering if there would be a speed difference between using the mySQL ORDER_BY option, or the PHP array_multi_sort function.A database server is designed for that not a scripting language.yeah, if all possible make the db work, not your code.Ok, for the last set of pages I was doing, all I had to do was pull data from 1 table of a db, and have it put in order by _______. I did this: SELECT * FROM tblname WHERE whatever='whatever' ORDER_BY column
The problem is, now I need to select everything from 8 tables, and have it all put in order. The tables are all formatted the same (same colums)...like this:
DATABASE:
table1:
ID info1 info2
1 stuff stuff
2 info info
table2:
ID info1 info2
5 data data
6 things things
table3:
ID info1 info2
3 data3 data3
4 stuff3 stuff3
I want to pull all that data, and display it like this:1 stuff stuff
2 info info
3 data3 data3
4 stuff3 stuff3
5 data data
6 things things
The thing is, the only way that I see to do it is to pull all the data from each table into arrays(1 for each column). Then use array_multi_sort(column1_array, column2_array). The problem with that is that A. it seems messy, and B. it is case sensitive (which I do not want), so I would have to either make another set of arrays that have the info run through strtoupper, or something (I want it to be sorted as case-insensitive, but I would like to preserve case). Is there a way of making the database do what I want?Well look at the keyword "union"
select * from (select 0,orderfield from table1
union all
select 0,orderfield from table2
union all
select 0,orderfield from table3) as m1 order by orderfieldGRRRRR!!!! Ok, I had to get that out. Union seems to be EXACTLY what I need, BUT...UNION is available from MySQL 4.0.0 on. My provider uses...MySQL 3.23.56
Union doesn't even exist!
I asked them to upgrade to 4.0 in March, and it still hasn't happened. I suppose I should look for a new place...but I have like 7 mos of service left that is already paid for.Originally posted by AaronCampbell
GRRRRR!!!!
Isn't that putts line from last month? Wait no it's Grrrreat.... dohjust left/inner join all the tables and order by whatever column. seeing how they have the same columsn it should be pretty easyDoing a inner join will not help. Its not the an horizonal table structure. It grows vertical if you see the table structures very common, but only when you want a many to many relationship rather than "unrelated"...
The only thought is that related data should be in the same table if you ask me. It should expand inside of only 1 table, then its just an order by statement. ..... otherwise there is no point to querying the data... as your going to have a mix of data types and lengths.. or unrelated data inside the output.
The joining of tables is an huge exponent of itself.
((RecordCount*JoinDataLength)^(RecordCount*JoinDataLength)) after where statement elimination....
union is not much better as it has also lookup the table schema to determine worse case field length for strings....
Or type conversions from any type of integer to another.tinyint to smallint....
So my suggestion is to not build the tables until you fully understand the complexity involved, otherwise painting yourself in the corner......yeah I forgot if they are not joined by some means. correct, afterburn, it could be a waste of code.
I was thinking they all are joined some how. I am stuck in join mode
I know this isn't the correct forum for this, but do either of you know of a good (but INEXPENSIVE) web-host that would run the latest stable version of mySQL?I use <!-- w --><a class="postlink" href="http://www.powweb.com">www.powweb.com</a><!-- w --> , they are pretty good.
The problem is, now I need to select everything from 8 tables, and have it all put in order. The tables are all formatted the same (same colums)...like this:
DATABASE:
table1:
ID info1 info2
1 stuff stuff
2 info info
table2:
ID info1 info2
5 data data
6 things things
table3:
ID info1 info2
3 data3 data3
4 stuff3 stuff3
I want to pull all that data, and display it like this:1 stuff stuff
2 info info
3 data3 data3
4 stuff3 stuff3
5 data data
6 things things
The thing is, the only way that I see to do it is to pull all the data from each table into arrays(1 for each column). Then use array_multi_sort(column1_array, column2_array). The problem with that is that A. it seems messy, and B. it is case sensitive (which I do not want), so I would have to either make another set of arrays that have the info run through strtoupper, or something (I want it to be sorted as case-insensitive, but I would like to preserve case). Is there a way of making the database do what I want?Well look at the keyword "union"
select * from (select 0,orderfield from table1
union all
select 0,orderfield from table2
union all
select 0,orderfield from table3) as m1 order by orderfieldGRRRRR!!!! Ok, I had to get that out. Union seems to be EXACTLY what I need, BUT...UNION is available from MySQL 4.0.0 on. My provider uses...MySQL 3.23.56
Union doesn't even exist!
I asked them to upgrade to 4.0 in March, and it still hasn't happened. I suppose I should look for a new place...but I have like 7 mos of service left that is already paid for.Originally posted by AaronCampbell
GRRRRR!!!!
Isn't that putts line from last month? Wait no it's Grrrreat.... dohjust left/inner join all the tables and order by whatever column. seeing how they have the same columsn it should be pretty easyDoing a inner join will not help. Its not the an horizonal table structure. It grows vertical if you see the table structures very common, but only when you want a many to many relationship rather than "unrelated"...
The only thought is that related data should be in the same table if you ask me. It should expand inside of only 1 table, then its just an order by statement. ..... otherwise there is no point to querying the data... as your going to have a mix of data types and lengths.. or unrelated data inside the output.
The joining of tables is an huge exponent of itself.
((RecordCount*JoinDataLength)^(RecordCount*JoinDataLength)) after where statement elimination....
union is not much better as it has also lookup the table schema to determine worse case field length for strings....
Or type conversions from any type of integer to another.tinyint to smallint....
So my suggestion is to not build the tables until you fully understand the complexity involved, otherwise painting yourself in the corner......yeah I forgot if they are not joined by some means. correct, afterburn, it could be a waste of code.
I was thinking they all are joined some how. I am stuck in join mode
I know this isn't the correct forum for this, but do either of you know of a good (but INEXPENSIVE) web-host that would run the latest stable version of mySQL?I use <!-- w --><a class="postlink" href="http://www.powweb.com">www.powweb.com</a><!-- w --> , they are pretty good.