...

View Full Version : Display random image



jim_bo
09-10-2006, 12:10 AM
Hi,

I have 3 tables, maincat, subcat, images.

How can I show a random image for each maincat from any corrosponding subcat:

table structures:


CREATE TABLE gallery_maincat (
maincat_id int(20) NOT NULL auto_increment,
maincat_name varchar(50) NOT NULL default '',
PRIMARY KEY (maincat_id)
) TYPE=MyISAM;

CREATE TABLE gallery_subcat (
subcat_id int(20) NOT NULL auto_increment,
maincat_id int(20) NOT NULL,
subcat_name varchar(50) NOT NULL default '',
PRIMARY KEY (subcat_id)
) TYPE=MyISAM;

CREATE TABLE gallery_images (
photo_id int(20) NOT NULL auto_increment,
subcat_id int(20) NOT NULL,
photo_filename varchar(25) default '',
photo_caption text,
PRIMARY KEY (photo_id)
) TYPE=MyISAM;

Thanks

guelphdad
09-10-2006, 12:40 AM
can you show us the three table join to list an image for each main cat? From there we can show you how to grab the image for a related sub cat and make it a random one.

jim_bo
09-10-2006, 12:58 AM
Hi,

Not really following your reply.

I tried something like:


$preview = mysql_query("SELECT i.photo_filename
FROM gallery_images i
INNER JOIN gallery_subcat s
ON i.subcat_id = s.subcat_id
INNER JOIN gallery_maincat m
ON s.maincat_id = m.maincat_id
WHERE s.subcat_id = i.subcat_id GROUP BY i.subcat_id ORDER BY RAND()") or die(mysql_error());

But it shows random images for maincats that arnt related to corosponding subcats.

Thanks

Fumigator
09-10-2006, 07:39 AM
That's pretty close I'd say, but you can't really select a column that isn't in the "group by" clause-- out of which row would that come? "Group by" says "get me one row that contains grouped data". Then you try to select a column that is going to have all kinds of different information in all the rows you grouped up.

I don't feel like I'm being very clear-- here's an example of 4 rows of data:


subcat_id photo_filename
12345 kitten.jpg
12345 cat.jpg
12345 puppy.jpg
12345 dog.jpg

Doing a "group by subcat_id" is going to group those 4 rows together and give you a summary of them. So in your query you could:

Select subcat_id
Select count(*)
Select min(photo_filename)
Select sum(counter)

or any other operation allowed on a group of rows.

All that being said (sorry, you got the longwinded version), if you are running a version of MySQL that allows subqueries, you can do


SELECT photo_filename
FROM gallery_images
WHERE subcat_id = (SELECT i.subcat_id
FROM gallery_images i
INNER JOIN gallery_subcat s
ON i.subcat_id = s.subcat_id
INNER JOIN gallery_maincat m
ON s.maincat_id = m.maincat_id
WHERE s.subcat_id = i.subcat_id GROUP BY i.subcat_id ORDER BY RAND())


If you are not able to do subqueries, then you'll probably have to split the query into two: One to grab the subcat_id you randomly picked, the other to select the filename based on that id.

jim_bo
09-10-2006, 10:03 AM
Hi,

Thanks for that .. I get the following error:


Warning: mysql_query() [function.mysql-query]: Unable to save result set in #:\####\####\htdocs\gallery\sh_maincat.php on line 33
Subquery returns more than 1 row

?

Thanks

Fumigator
09-10-2006, 10:19 AM
Whoops, you'll need to stick LIMIT 1 in there.

jim_bo
09-10-2006, 01:12 PM
Hi,

That produces no errors, but is still showing unrelated random pictures for maincats.


Cheers

Fumigator
09-10-2006, 06:00 PM
Well hmmm that seems like it should work. I guess I'd need to see the full code and data to debug it.

Good luck.

jim_bo
09-10-2006, 11:09 PM
Hi,

Here is the bulk of the code basicaly less the pagination:


$preview = mysql_query("SELECT photo_filename
FROM gallery_images
WHERE subcat_id = (SELECT i.subcat_id
FROM gallery_images i
INNER JOIN gallery_subcat s
ON i.subcat_id = s.subcat_id
INNER JOIN gallery_maincat m
ON s.maincat_id = m.maincat_id
WHERE s.subcat_id = i.subcat_id GROUP BY i.subcat_id ORDER BY RAND() limit 1)") or die(mysql_error());

$num_rows = mysql_num_rows($preview);
while($row1 = mysql_fetch_array($preview)) {

if ($num_rows == 0) {

$image = 'No images<br /><br />';
}else{
$image = '<a href="index.php?action=sh_subcat&maincat_id='.$row['maincat_id'].'"><img src="'.$images_dir.'/tb_'.$row1['photo_filename'].'" border="1"></a><br />';
}
}
$subs = mysql_query("SELECT maincat_id FROM gallery_subcat WHERE maincat_id = ".$row['maincat_id']."");
$total = mysql_num_rows($subs);

if($numcolsprinted == $numcols) {
echo "<tr></tr>";
$numcolsprinted = 0;
}
echo '<td align="center" valign="top">';
echo '<a href="index.php?action=gadmin&process=edit_maincat&maincat_id='.$row['maincat_id'].'">Edit</a> - <a href="index.php?action=process&process=delete_maincat&maincat_id='.$row['maincat_id'].'">Delete</a><br />';
echo "$image";
echo '<b><a href="index.php?action=sh_subcat&maincat_id='.$row['maincat_id'].'">'.$row['maincat_name'].'</a></b><br />';
echo ''.$total.' sub cats<br /><br />';
echo '</td>';

$numcolsprinted++;
}
$colstobalance = $numcols - $numcolsprinted;
for($i=1; $i<=$colstobalance; $i++) {
}
echo "</table>";

And the table layouts have been posted.

It must be close .. but its the same as all my efforts .. Displays thumbs during the random cycle for maincats that arnt related to them. I guess you under stand what I mean.

Thanks

Fumigator
09-11-2006, 01:06 AM
There's nothing in your query that specifies the maincat_id. I think that must be what is tripping you up-- you are using $row['maincat_id'] in your link's URL, but you are not using that value in the query, but you are expecting the query to know about that value. If my guess is right, you simply need to add "AND m.maincat_id = {$row['maincat_id']}" to the subquery's where clause.

BTW, are you aware the second query is a bit redundant? :p

jim_bo
09-11-2006, 01:30 AM
Hi,

There is a query in the begining part of the code that creates $row['maincat_id']


$sql = mysql_query("SELECT * FROM gallery_maincat ORDER BY maincat_id DESC LIMIT $from, $max_results");

if(mysql_num_rows($sql) == 0) {
echo '<br /><br /><b>No Categories available.</b>';
return;
}

Maybe I should post all the code:


echo '<center><b>Categories</b><br /><br />';

if($_GET['page'] == NULL) {
$page = 1;
}else{
$page = $_GET['page'];
}
$max_results = 10;
$numcols = 5;
$numcolsprinted = 0;
$from = (($page * $max_results) - $max_results);

$sql = mysql_query("SELECT * FROM gallery_maincat ORDER BY maincat_id DESC LIMIT $from, $max_results");

if(mysql_num_rows($sql) == 0) {
echo '<br /><br /><b>No Categories available.</b>';
return;
}
echo '<table border="0" bordercolor="#000000" cellspacing="5" cellpadding="5" align="center">';

while($row = mysql_fetch_array($sql)) {

$preview = mysql_query("SELECT photo_filename
FROM gallery_images
WHERE subcat_id = (SELECT i.subcat_id
FROM gallery_images i
INNER JOIN gallery_subcat s
ON i.subcat_id = s.subcat_id
INNER JOIN gallery_maincat m
ON s.maincat_id = m.maincat_id
WHERE s.subcat_id = i.subcat_id GROUP BY i.subcat_id ORDER BY RAND() limit 1)") or die(mysql_error());

$num_rows = mysql_num_rows($preview);
while($row1 = mysql_fetch_array($preview)) {

if ($num_rows == 0) {

$image = 'No images<br /><br />';
}else{
$image = '<a href="index.php?action=sh_subcat&maincat_id='.$row['maincat_id'].'"><img src="'.$images_dir.'/tb_'.$row1['photo_filename'].'" border="1"></a><br />';
}
}
$subs = mysql_query("SELECT maincat_id FROM gallery_subcat WHERE maincat_id = ".$row['maincat_id']."");
$total = mysql_num_rows($subs);

if($numcolsprinted == $numcols) {
echo "<tr></tr>";
$numcolsprinted = 0;
}
echo '<td align="center" valign="top">';
echo '<a href="index.php?action=gadmin&process=edit_maincat&maincat_id='.$row['maincat_id'].'">Edit</a> - <a href="index.php?action=process&process=delete_maincat&maincat_id='.$row['maincat_id'].'">Delete</a><br />';
echo "$image";
echo '<b><a href="index.php?action=sh_subcat&maincat_id='.$row['maincat_id'].'">'.$row['maincat_name'].'</a></b><br />';
echo ''.$total.' sub cats<br /><br />';
echo '</td>';

$numcolsprinted++;
}
$colstobalance = $numcols - $numcolsprinted;
for($i=1; $i<=$colstobalance; $i++) {
}
echo "</table>";

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM gallery_maincat"),0);
$total_pages = ceil($total_results / $max_results);

echo '<br /><br />';

if($page > 1){
$prev = ($page - 1);
echo "<<&nbsp<a href=\"index.php?action=sh_maincat&page=$prev\">Previous</a>&nbsp|&nbsp";
}
for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "<font color=\"red\">$i&nbsp;";
}else{
echo "<a href=\"index.php?action=sh_maincat&page=$i\">$i</a>&nbsp";
}
}
if($page < $total_pages){
$next = ($page + 1);
echo "&nbsp|&nbsp<a href=\"index.php?action=sh_maincat&page=$next\">Next</a>&nbsp>>";
}
echo '</center>';


Feel free to pass any tidyup code.


Thanks

jim_bo
09-11-2006, 02:16 AM
Hi,

I beleave I have it working:


$preview = mysql_query("SELECT i.photo_filename
FROM gallery_images i
LEFT JOIN gallery_subcat s
ON i.subcat_id = s.subcat_id
LEFT JOIN gallery_maincat m
ON m.maincat_id = s.maincat_id
WHERE m.maincat_id = {$row['maincat_id']}
ORDER BY RAND()") or die(mysql_error());

Does that look like plausable code?

Should it be inner join?

Thanks

guelphdad
09-11-2006, 04:36 AM
it should be an inner join when you want your rows to match against those in the other tables. if you don't care about matched rows then use outer or left joins.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum