MySQL error

[bjeffries]

I am getting this error, and dont know what I should do to fix this.
Column 'uptime' in order clause is ambiguous
uptime = date uploaded set to NOW

My Query:
$select = ("SELECT * FROM photo_album, video ORDER BY uptime DESC LIMIT 10");

Maybe I cannot call a query like that. I am not sure, but I want to call everything from both of those tables. I want them ordered by date uploaded.

Any suggestion?

[pritaeas]

uptime is probably a column in both tables, so you have to specify which one you mean.

[bjeffries]

It is a column in both tables. I want them to be ordered based on the date, which is uptime in both tables. How would I write it to do this?

[pritaeas]

SELECT * FROM photo_album, video ORDER BY photo_album.uptime DESC LIMIT 10

--or

SELECT * FROM photo_album, video ORDER BY video.uptime DESC LIMIT 10

[bjeffries]

ok so my only issue is, I have a different template for videos and for photos. I think I have that issue covered by adding a field for the template which calls on the proper url for the proper template.

I am getting 4 results: the first 2 are test video 1 and the second 2 are test video 2.
It should be: test video2, test video, test album 2, test album.

My photo albums "Test album" are not even showing up. Do you think this has something to do with the sql? I used:

SELECT * FROM photo_album, video ORDER BY photo_album.uptime DESC LIMIT 10

My php is:

while($row = mysql_fetch_assoc($result)) {
	echo "<li>"
    ."<p align='left'><a href='view.php?p=".$row['type']."&id=".$row['id']."'>"
	."<img src='".$row['thumb']."' width='90px' align='left' border='1'/></a>"
	."<span class='galTitle'><a href='view.php?p=".$row['type']."&id=".$row['id']."'>".$row['title']."</a></span>"
	." - ".date('m-d-y', strtotime($row['uptime']))."<br/>"
	."<a href='view.php?p=".$row['type']."&id=".$row['id']."'>".$row['description']."</a></p>"
	."</li>";
}

[pritaeas]

Yes. Try:

SELECT * FROM photo_album
UNION
SELECT * FROM video 
ORDER BY uptime DESC LIMIT 10

But this will only work if the columns are the same. I need the table definitions if this gives wrong results.

[Shanti C]

or create alias names for common fields like :

SELECT p.thumb as photo_thumb,....... FROM photo_album as p, video as v ORDER BY photo_album.p DESC LIMIT 10

[bjeffries]

If I understand what you mean by the table definitions, then I just took a screenshot of my phpmyadmin.

The two photos are attached, one of the video table and one of the photo_album table.

I did get this error message, just as you said: "The used SELECT statements have a different number of columns"

As for the create alias names for the tables, that just totally confused me. I am not the best with the MySQL part of things, or PHP for that matter. But I am trying!

Thanks for all your help.

[pritaeas]

SELECT id,title,description,thumb,uptime,hits,rating,type,'' AS image,'' AS file FROM photo_album
UNION
SELECT id,title,description,thumb,uptime,hits,rating,type,image,file FROM video 
ORDER BY uptime DESC LIMIT 10

Since the first table does not have 'image' and 'file' as columns, we add empty strings with the correct name, to match those columns in the second table. That way both match, and the error should be gone.

[bjeffries]

Nice! works like a charm! thanks for all the help!