Selecting data from multiple tables.

[eluser]Twisted1919[/eluser]
Hi , my question is related to sql more than codeigniter , but because i use codeigniter , and i don't have other forum where to ask , i hope i can get some help here .
So , i have 8 tables into a mysql database , each table is a category that has more records and is like this :

category1
category1_id | category1_member_name | category1_member_votes | category1_other datas |

category2
category2_id | category2_member_name | category2_member_votes | category2_other data |

[...]
[...]

category8
category8_id | category8_member_name | category2_member_votes | category8_other data |

Last few days , i tryed to find a method to select just the categoryX_member_name AND the categoryX_member_votes for all categories(just one sql query) and to do a top based on members votes for all categories .

For example , let's say that my tables wolud be like:
cat_html
cat_html_id | cat_html_member_name | cat_html_member_votes
1 | just_first_username | 10

cat_php
cat_php_id | cat_php_member_name | cat_php_member_votes
1 | just_second_username | 25
2 | just_third_username | 30

cat_xml
cat_xml_id | cat_xml_member_name | cat_xml_member_votes
1 | xml_first_username | 55
2 | xml_second_username | 65

[...]

And at the end , i woult obtain an result as follows :
First Place : xml_second_username -> 65 votes
Second Place: xml_first_username -> 55 votes
Third Place : just_third_username -> 30 votes

And so on , i think you got the ideea ...
Is just a can't figure out how i should do it ...

Please , is very important , help me a bit .
Have a nice day

[eluser]TheFuzzy0ne[/eluser]
I'm a bit confused by your schema, so I have some advice for you that you may wish to act on to make things easier for you and those helping you. You will find that things are easier to work with if you only have a single categories table, and add an extra column for the category number. You'll then find that you can do what you want in just a single query, rather than 8 queries. I believe that if you do this, you might even be able to figure out the answer to your question yourself.

[eluser]darkhouse[/eluser]
Try this.

Code:

//this is your model method
function top_member_names(){
   $sql = "SELECT * FROM cat_html
           UNION
           SELECT * FROM cat_php
           UNION
           SELECT * FROM cat_xml
           ORDER BY cat_html_member_votes DESC";
   $query = $this->db->query($sql);
   return $query->result();
}

I tested it, and it works, but I think you could've built your database much better. I think you could've done it in 1 table, like this:

categories
id | category_type | member_name | member_votes
1 | html | just_first_username | 10
2 | php | just_second_username | 25
3 | php | just_third_username | 30
4 | xml | xml_first_username | 55
5 | xml | xml_second_username | 65

And then your SQL is just "SELECT * FROM categories ORDER BY member_votes DESC"

Edit: I didn't realize TheFuzzy0ne had suggested the same thing.

[eluser]Twisted1919[/eluser]
OMG .. i really screw this up , you might be right , one table may be enough .
Thank you very much for your answers , i appreciate a lot .
I'll do the changes , and i'll come back with an answer regarding to this issue .

[eluser]TheFuzzy0ne[/eluser]
I wouldn't go as far as to say that you screwed it up, but we can't always know everything and get things right first time. We are all learning, and we all have to start somewhere.

[url="http://www.keithjbrown.co.uk/vworks/mysql/"]This is a fantastic tutorial on database normalisation[/url] if you can find the time to read it. It's very long, but broken down into sections, and you might learn something from it.

Good luck.

[eluser]darkhouse[/eluser]
I think it's a question of planning. At my office, we try to plan as much as we can, from sitemaps to database schemas to flowcharts. And as long as we have a good understanding of how the application is supposed to work, development usually goes pretty smooth. Planning gives us the chance to come up with ideas and then refine them and make them better (or scrap them) so that the final product is solid. It takes a bit of time at the beginning, but saves a lot of time in the end.

Now, planning on your own is a little more difficult because it's hard to look at your idea and tear it apart, but you have to. There are 6 different roles on problem solving, and you have to play each one. Check out this article [url="http://en.wikipedia.org/wiki/Six_Thinking_Hats"]http://en.wikipedia.org/wiki/Six_Thinking_Hats[/url]. This concept was part of a programming class I had in high school like 15 years ago, and at the time I thought it was stupid, but when you actually apply it, it's pretty useful.

[eluser]TheFuzzy0ne[/eluser]
So now you can say you've been there, didn't get the T-shirt, but got six thinking hats.

[eluser]darkhouse[/eluser]
LOL true.

[eluser]Twisted1919[/eluser]
Ok guys , that was the trick , i modified the database , and not just that i reduced the number of queryes , but now i can sort all the data as i want to .
I will read those artcles that you recomended .
Yes , you are right , it is all about planning , but i was in a hurry when i created the database , and i made that mistake .
Thank you once again

[eluser]darkhouse[/eluser]
Glad you sorted it out. Hope you see how much time you can ultimately lose by not spending the time to plan things out beforehand. Happy coding.