Problems joining two tables

[Michan]

Hi there,

I'm having problems joining two databases together; I can't seem to pluck data from the second one. I'm new to PHP and if you could highlight what I'm doing wrong and what I'd need to do to correct myself, I'd be very appreciative.

[code]$media = mysql_query('SELECT * FROM files LEFT JOIN albums ON files.album=albums.id WHERE ORDER BY timestamp DESC LIMIT 0, 1');

while($latestmedia = mysql_fetch_array($media))
{
$displaymedia=('
<div id="home-media-image">
<a href="/'.$latestmedia['directory'].'/'.$latestmedia['file'].'" target="image"><img src="/'.$latestmedia['directory'].'/'.$latestmedia['file'].'" border="0" width="114" height="84"></a>
</div>
<a href="?view=image_gallery&album='.$latestmedia['album'].'"><div id="home-media-select">
<div id="home-media-l"></div>
<div id="home-media-text">More for this game</div>
<div id="home-media-r"></div>
</div></a>
');

$bars = "\|";
$removebars = "";

echo ereg_replace($bars, $removebars, $displaymedia);

}[/code]

(The ereg_replace is to remove the "|" symbol from items in that row. The albums come up as "|XXXX|" for example.)

Many thanks!

[cmgmyr]

$media = mysql_query('SELECT * FROM files LEFT JOIN albums ON (files.album=albums.id) ORDER BY timestamp DESC LIMIT 0, 1');

Try that, I usually inclose the join with (). Also your WHERE didn't do anything...this is more then likely your problem.

Let me know how it works out.
-Chris

[Michan]

Thanks Chris,

I tried doing as you said by using the brackets (and removing the WHERE), but it still isn't returning the 'directory'. It displays the 'file' and 'album', however. I can provide you with the table structures, if you like?

Thanks in advance :)

[Destruction]

SELECT * FROM files LEFT JOIN albums ON

You're only selecting all fields from files, you need to do something like

SELECT * FROM files, albums LEFT JOIN albums ON

If your directory column is within the albums table perhaps?

Dest

[cmgmyr]

If you could include them it might help. Thanks

[Michan]

[quote author=Destruction link=topic=113361.msg460588#msg460588 date=1162310322]
You're only selecting all fields from files, you need to do something like

SELECT * FROM files, albums LEFT JOIN albums ON[/quote]

I have tried this, but I get shouted at by my server:

[quote]Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in index.php on line 241[/quote]

FYI, line 241 is:

[code]while($latestmedia = mysql_fetch_array($media))[/code]

My database structure is below:

[b]files[/b]
id (primary)
name
file
album
type
timestamp
size
description

[b]albums[/b]
id (primary)
name
directory

The ones I need are, from files, 'file', 'album', (and 'timestamp' to order) and from albums, 'directory' (as well as 'id' to define the row).


Many thanks!

[cmgmyr]

If you are matching up the album names wouldn't it be:

$media = mysql_query('SELECT * FROM files LEFT JOIN albums ON (files.album=albums.name) ORDER BY timestamp DESC LIMIT 0, 1');

...what you should do is make another key in files (a_id) for album id then it can be (files.a_id=albums.id) which would make it a lot easier

[Michan]

[quote author=cmgmyr link=topic=113361.msg460597#msg460597 date=1162311052]
If you are matching up the album names wouldn't it be:

$media = mysql_query('SELECT * FROM files LEFT JOIN albums ON (files.album=albums.name) ORDER BY timestamp DESC LIMIT 0, 1');
[/quote]

Basically I'm trying to get the file 'album' value to match the albums 'id' value, then return the albums 'directory'; the 'name' column is irrelevant as that's to do with what will be displayed on the actual page for the album (this data is just for the homepage itself).

[quote author=cmgmyr link=topic=113361.msg460597#msg460597 date=1162311052]
...what you should do is make another key in files (a_id) for album id then it can be (files.a_id=albums.id) which would make it a lot easier
[/quote]

I'm doing that for all of my tables, but what I have to work with is this, and I can't really change it (as much as I'd like to) as the site is already running off these databases and don't even want to begin touching any of the existing code, as it's a total mess (hence the new code XD).

There are over 12,000 rows in files, and over 1,500 in albums. :(

There surely must be a way to be able to do this with the existing tables though?

[cmgmyr]

Try just:
$media = mysql_query('SELECT * FROM files LEFT JOIN albums ON (files.album=albums.id) LIMIT 0, 1');

See what happens with that.

[Michan]

Chis,
That delivers the first row in the database, and still no 'directory', unfortunately! :(

[cmgmyr]

Ok...Instead of selecting everything, just select the ones that you want to output...

$media = mysql_query('SELECT albums.directory, files.file FROM files LEFT JOIN albums ON (files.album=albums.id) ORDER BY timestamp DESC LIMIT 0, 1');

[Michan]

Chris,
It's now displaying the same result as earlier; the latest row, but without the 'directory'! I've also just tried using my n00bish PHP skills to write things like '.$latestmedia['files.file'].' but that just destroyed everything, so I've reverted back to what I had initially...

[cmgmyr]

Can you post the 2 records from the database that you are reading from and also the html output you are getting?

[Michan]

Sure, here is the latest record:

[quote]id        name    file            album      type      timestamp                  size        description
15311    000      000.JPG      |1763|    image    2006-10-24 15:05:32    34775[/quote]

Which should match with:

[quote]id      name                          directory
1763  Guilty Gear Judgement    Guilty Gear Judgement[/quote]

The HTML I get is:

[code]<a href="downloads//000.JPG" target="image"><img src="downloads//000.JPG" border="0" width="114" height="84"></a>
</div>
<a href="?view=image_gallery&album=1763"><div id="home-media-select">
<div id="home-media-l"></div>
<div id="home-media-text">More for this game</div>

<div id="home-media-r"></div>
</div></a>[/code]

[cmgmyr]

Ok, I think part of your problem is "|1763|" does not equal "1763".

Try this:
First find the record that has the latest timestamp, get the "album", strip out the 2 "|" 's
Then, have a second query to get all of the stuff from the album table

This might be a little more bulky, but it will work for you.

[Michan]

Ah, thanks Chris, I understand what do do now, but how would I get about putting that into PHP?

(Sorry, I'm really just a beginner, and I'm still not entirely sure of what I can and can't do.)

[radar]

Here just try this...  this is how I do my joins..  it works great...  Might be a bit dramatic for you but guaranteed to work...


[code]
<?php
$data = $turbo->get_all("SELECT users.user_id AS user_id,
users.username AS username,
    users.class AS class,
users.fname AS fname,
users.lname AS lname,
users.email AS email,
users.reg_date AS reg_date,
users.tot_cnt AS tot_cnt,
users.var_stats AS var_stats,
count(ratings.status = 'complete') AS status,
ratings.user_id as r_user_id
FROM users LEFT JOIN ratings ON users.user_id=ratings.user_id
GROUP BY users.user_id ORDER BY users.user_id ASC"); ?>
[/code]

and I would agree with putting in like an a_id in your files table that has the album id that it corresponds to.. that way you dont need to do anything else except run your query..  it'll make your life easier trust me..

[cmgmyr]

Is there any reason why you have the "|" 's in the albums? It would be a lot easier to take those out. Then you could use my original query...

[Michan]

Chris, it's so that files can be linked to multiple albums, like |XXXX|XXXX| etc. However, we never had a use for it...

But now there are 15,000+ rows in the database, and the current site is relying on seeing the "|" symbol. So I've been forced into finding other ways of doing it >_>;;

I can't figure out how to strip the | symbols, however. I guess it would be something similar to this..?

[code]$bars = "\|";
$removebars = "";

$media = $turbo->get_all("SELECT files.file AS file,
files.album = ereg_replace($bars, $removebars, files.album) AS album,
files.timestamp AS timestamp,
albums.id AS id,
albums.directory AS directory,
FROM files LEFT JOIN albums ON files.album=albums.id
GROUP BY files.album ORDER BY files.timestamp DESC");

while($latestmedia = mysql_fetch_array($media))
{
echo ('...[/code]

I know I'm wrong however, as it brings me an error. I'm really new to PHP, so if someone could lend me a hand here, I'd appreciate it :-[

[cmgmyr]

What was the error?

Try and see what this does:
$media = $turbo->get_all("SELECT files.file AS file,
files.album =".ereg_replace($bars, $removebars, files.album)." AS album,
files.timestamp AS timestamp,
albums.id AS id,
albums.directory AS directory,
FROM files LEFT JOIN albums ON files.album=albums.id
GROUP BY files.album ORDER BY files.timestamp DESC");

Prob won't work, but worth a try

[Michan]

Sorry Chris, it doesn't work :(

Here is the error:

[quote]Fatal error: Call to a member function get_all() on a non-object in index.php on line 242[/quote]

Line 242:

[code]$media = $turbo->get_all("SELECT files.file AS file,[/code]