Taking data from two tables

[KingOfHeart]

I have a site that contains a file list . I also have a wiki that contains a file list. I want to list both of these files into one group.

 

From Wiki

image - Table

img_name - Contains the name of the file

img_description - Contains the description

img_user - Returns the number of the user who uploaded it

 

user - Table

user_id - The id for the user

user_name - Contains the name of the user

 

From my File List

Downloads - Table

name - Name of the file

userid - The user's id

description - The description for the file

type - The file's Type

 

Accounts - Table

id - The user's ID

username - The user's name

 

So first we need to get it organized for the image table to be combined with the user table. Then Downloads needs to be combined with Accounts. After all that is done, the two lists need to come together.

 

So how tough would this be? I'd settle for keeping the Wiki data and the File List separated into two lists if necessary.

 

[fenway]

It's not tough at all.. a few joins ought to do the trick.  What do you want to display at the edn?

[KingOfHeart]

img_name, user_name, img_description, name, username, description would need to be echoed.

 

The other fields will be needed though for getting certain data. Haven't had much practice with the JOIN command.

[fenway]

Try this:

( 
select
i.img_name
, i.img_description
, u.user_name
from image as i
inner join user at t on ( t.user_id = i.img_user )
)
union all
( 
select d.name
, d.description
, a.username
from downloads as d
inner join accounts as a on ( a.id = d.userid )
)

[KingOfHeart]

I'll try it out later but I have a question first. When echoing the rows can I use $row['d.description'] at all? Or is this a wrong method to use?

[fenway]

The table prefix is not part of the field name that you will retrieve... in fact, it will probably have the name of the first half of the query.

[KingOfHeart]

How do I set this up?

 

$sql = mysql_query("(
select
i.img_name
, i.img_description
, u.user_name
from image as i
inner join user at t on ( t.user_id = i.img_user )
)
union all
(
select d.name
, d.description
, a.username
from downloads as d
inner join accounts as a on ( a.id = d.userid )
)
");

while($rows= mysql_fetch_array($sql))
{
$out .= $rows['name'];
}

 

$out is for just returning the data.

 

[fenway]

How do I set this up?

Easiest way is simply to use the same column aliases for both parts of the union.

[KingOfHeart]

Can you just paste the entire coded needed, showing the mysql line and $rows

This is the part that throws me off.

The last code I used just created an error.

[Kouruu]

fixed some errors in it, and got it decent

 

$sql = mysql_query("(
select
i.img_name
, i.img_description
, t.user_name
from image as i
inner join user as t on ( t.user_id = i.img_user )
)
union all
(
select d.name
, d.description
, a.username
from Downloads as d
inner join Accounts as a on ( a.id = d.userid )
)
");

 

mind you, due to the fact that the fields of the first group and second group don't match, you can't use a union here, the only way would be if they each had the same fields.

[fenway]

mind you, due to the fact that the fields of the first group and second group don't match, you can't use a union here, the only way would be if they each had the same fields.

Not true at all... like I said earlier, use column aliases to make them the same.