Page 1 of 1
Selecting data from multiple tables
Posted: Sat May 22, 2010 1:32 pm
by Mechaworx
Hi All,
First post here. I am trying to display the most recent 5 records per page from my database from multiple tables on the home page of my web site.
My database is set up in a one to many relationship via the categories table. My table structures are set up as below:
categories table
--------------------------------------
catid(PK)
category
urlPath
General Info table
--------------------------------------
gid(PK)
catid
title
date_posted
date_reported
picURL
picALT
picTitle
article_body
How To Table
--------------------------------------
hid(PK)
catid
title
date_posted
date_reported
picURL
picALT
picTitle
article_body
Missions Table
--------------------------------------
mid(PK)
catid
title
date_posted
date_reported
picURL
picALT
picTitle
article_body
Projects Table
---------------------------------------
pid(PK)
catid
title
date_posted
date_reported
picURL
picALT
picTitle
article_body
I have set up my sql query as follows:
Code: Select all
$sqlCommand = mysql_query("(SELECT * FROM generalinfo) UNION (SELECT * FROM howto) UNION (SELECT * FROM missions) UNION (SELECT * FROM projects) ORDER BY reported_date DESC LIMIT $records, $per_page")or die(mysql_error());
I have hit a snag where I'm not quite sure how to build the array to display the data from each table. Any advise on what methodology I should follow would be appreciated or if the UNION method used in the SQL above would be the best way of grabbing the data.
Best regards
Gerry
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 2:49 pm
by Eran
It seems that 4 of the tables have exactly the same structure. That would indicate that there should be only one table instead, with a column (named 'type' or something similar) differentiating between them.
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 5:46 pm
by Mechaworx
It seems that 4 of the tables have exactly the same structure. That would indicate that there should be only one table instead, with a column (named 'type' or something similar) differentiating between them.
That is true that the field names are the same in 4 of the tables. I differentiate them with the field catid in each table and references the category id from the categories table. So that all the records in the tables generalinfo, howto, missions and projects each reference, a catid of 1, 2, 3 or 4 respectively from the categories table.
Below is the schema of how the tables are set up with 4 records in each table:
categories table
--------------------------------------
catid(PK) | category | urlPath
1 General info
2 How To
3 Missions
4 Projects
General Info table
--------------------------------------
gid(PK) | catid | title | date_posted | date_reported | picURL | picALT | picTitle | article_body
1 1
2 1
3 1
4 1
How To Table
--------------------------------------
hid(PK) | catid | title | date_posted | date_reported | picURL | picALT | picTitle | article_body
1 2
2 2
3 2
4 2
Missions Table
--------------------------------------
mid(PK) | catid | title | date_posted | date_reported | picURL | picALT | picTitle | article_body
1 3
2 3
3 3
4 3
Projects Table
---------------------------------------
pid(PK) | catid | title | date_posted | date_reported | picURL | picALT | picTitle | article_body
1 4
2 4
3 4
4 4
Thanks for any help
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 5:55 pm
by Eran
If you already have a field differentiating them, there's really no need for 4 tables. Use the same table for all articled, with the field catid relating them to the appropriate category. It will make your job much simpler
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 6:18 pm
by Mechaworx
Unfortunately,
There are hundreds of records in each table, trying to rewrite the database at this stage really isn't an option. Are you saying that the way the database is set up now, it can't be done?
Thanks
Gerry
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 6:29 pm
by mikosiko
Mechaworx wrote:Unfortunately,
There are hundreds of records in each table,
that is nothing.... merge all that data should be fairly easy.
Mechaworx wrote:
trying to rewrite the database at this stage really isn't an option.
but seems to be you best and smartest option
Mechaworx wrote:
Are you saying that the way the database is set up now, it can't be done?
Thanks
Gerry
can be done... but you are going to regret it sooner or later.... and probably sooner
you best option is do a good database design now as pytrin suggested.... all your future code is going to be easy... better invest a little effort now that do big effort in the future.... IMHO
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 6:46 pm
by Mechaworx
Thanks guys,
Each of the four tables also have additional comments tables (for user comments). How would I need to handle that? Create only three tables?
Table 1
Categories
Table 2
Articles
Table 3
Comments
Link table 1 and table 2 by catid then link table 2 and table 3 by comment ID?
Thanks
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 6:56 pm
by Eran
I would say link the comments table to the articles table by article_id and not the other way around. It should be a one-to-many relationship (as in each article could have many comments)
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 7:45 pm
by Mechaworx
Ok thanks
Now how would I merge these four tables into one "articles" table? Some of these tables when merged would have duplicate primary keys.
Thanks
Gerry
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 7:51 pm
by Eran
You should use the INSERT ... SELECT syntax, without specifying the ID as one of the columns your are inserting/selecting. New auto-incremented keys will be generated for all the articles
http://dev.mysql.com/doc/refman/5.0/en/ ... elect.html
Re: Selecting data from multiple tables
Posted: Sat May 22, 2010 10:37 pm
by Mechaworx
Thanks guys for the help.
Tables are merged and things seem to be working perfectly. I just had to add some additional columns to my categories table because of the way my site is structured (sub-directories for each category). But once I did that and a few minor code changes things are working excellently.
Regards
Gerry