Selecting data from multiple tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Mechaworx
Forum Newbie
Posts: 8
Joined: Sat May 22, 2010 1:26 pm

Selecting data from multiple tables

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting data from multiple tables

Post 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.
Mechaworx
Forum Newbie
Posts: 8
Joined: Sat May 22, 2010 1:26 pm

Re: Selecting data from multiple tables

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting data from multiple tables

Post 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
Mechaworx
Forum Newbie
Posts: 8
Joined: Sat May 22, 2010 1:26 pm

Re: Selecting data from multiple tables

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Selecting data from multiple tables

Post 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
Mechaworx
Forum Newbie
Posts: 8
Joined: Sat May 22, 2010 1:26 pm

Re: Selecting data from multiple tables

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting data from multiple tables

Post 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)
Mechaworx
Forum Newbie
Posts: 8
Joined: Sat May 22, 2010 1:26 pm

Re: Selecting data from multiple tables

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting data from multiple tables

Post 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
Mechaworx
Forum Newbie
Posts: 8
Joined: Sat May 22, 2010 1:26 pm

Re: Selecting data from multiple tables

Post 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
Post Reply