Random Image

[SkyRanger]

Is there a way to display a random image on page reload for each:

 

$query = "SELECT * 
FROM gallery_category AS g1
LEFT JOIN gallery_photos AS g2 ON g1.category_id = g2.photo_category
WHERE g1.photo_owner =  '$client'";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
?>


<?php
//Define number of columns to use
$maxColumns  = 5;

$count = 0;              //Var to count records
$categoryList = ''; //Var to hold HTML output
while($row = $result->fetch_assoc())
{
        $count++; //Increment count

        //Open new row if first record in row
        if($count % $maxColumns == 1)
        {
                $categoryList .= "<tr>\n";
        }
$cid = $row['category_id'];
        //Output current record
        $categoryList .= "<td width=20%><br><center>";
		 
          if (empty($row['photo_filename'])) {
        $categoryList .= "<img src=images/noimage.jpg height=100 width=100>";
        } else { 
		$categoryList .= "<img src=clients/{$client}/photos/{$row['photo_filename']} height=100 width=100>";
		}
		$categoryList .= "<br><a class=black-text href=showphoto.php?cid=$cid>{$row['category_name']}</a></center><br></td>\n";

        //Close row if last record in row
        if($count % $maxColumns == 0)
        {
                $categoryList .= "</tr>\n";
        }
}

//Close last row if needed
if($maxColumns % $count != 0)
{
        $categoryList .= "</tr>\n";
}

?>
<table width="95%" border="0">
<?php echo $categoryList; ?>
</table>

 

 

So when 

$categoryList .= "<img src=clients/{$client}/photos/{$row['photo_filename']} height=100 width=100>";

 

gets refreshed it will display a different image.

[Psycho]

Well, yes. But, in the code you have above it is querying all the associated records displaying the specific image to display for each record in the result set. So, if you want the image to be random you must have a list of possible values to select the random value from. But,since you are specifying a specific image, not sure what you are looking to do.

[SkyRanger]

Hey Psycho

 

The problem I am having is that when the page is loaded it shows all the categories and also shows and image above the category name.  What I need it to do is show the categories and show a random image for each category.  Right now it is showing all the images for each category.  I hope that explains it some.

 

example:

 

table layout:

 

table: gallery_category

 

category_id    category_name  photo_owner

1                          family               ownername

2                          christmas         ownername

3                          holiday             ownername

4                          party                ownername

 

 

table:  gallery_photos

 

photo_filename   photo_category photo_owner

image1.jpg                   2                 ownername

image2.jpg                   2                 ownername

image3.png                  3                ownername

etc.....

 

Not sure how to fix this problem and do as I need.  

 

[Love2c0de]

I think that is because your query is set up to 'SELECT ALL' so it will select all the photo_category images where the id matches so any which are the same will be selected.

 

I'm not sure how to fix it though and I'm not sure if that is entirely correct but that was my initial thoughts.

 

Hope it helps.

 

Regard,

 

L2c.

Edited February 26, 2013 by Love2c0de

[SkyRanger]

ok, got the show only 1 record per category, forgot to use group by

 

SELECT * 
FROM gallery_category AS g1
LEFT JOIN gallery_photos AS g2 ON g1.category_id = g2.photo_category
WHERE g1.photo_owner =  '$client'
GROUP BY category_id

 

Anybody have an idea on how to show a random photo_filename for each category?

[Psycho]

http://forums.phpfreaks.com/topic/247904-select-a-random-row-with-group-by/

 

EDIT: I had actually written out the query (from the above post) to match your specific table structures along with some other comments.

 

So, you can use the query format in that post above, but it uses ORDER BY RAND() in order to get the random image on each run. That can be a performance issue with large data sets. This may not be an issue with your current setup. I would probably try it out and see what the performance is like since that is the easiest solution.. And, I'd probably load up the database with a bunch of test data to simulate what you think you may have. If the performance is acceptable, then use it.

 

The other option would be to query the database so you get one record back for each category that includes a concatenated list of all the photos for the category. Then use PHP array functions to get a unique value.

Edited February 26, 2013 by Psycho

[Psycho]

Here is the solution that relies on the query selecting the random image

 

<?php

$query = "SELECT * FROM
          (   SELECT category.category_id, category.category_name, photos.photo_filename
              FROM gallery_category AS category
              LEFT JOIN gallery_photos AS photos
                  ON category.category_id = photos.photo_category
              WHERE category.photo_owner =  '1'
              ORDER BY RAND()
          ) AS T
          GROUP BY category_id
          ORDER BY category_name";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

//Define number of columns to use
$maxColumns  = 5;

$count = 0; //Var to count records
$categoryList = ''; //Var to hold HTML output
while($row = $result->fetch_assoc())
{
    $count++; //Increment count
    $imgSrc = (!empty($row['photo_filename'])) ? "clients/{$client}/photos/{$row['photo_filename']}" : 'images/noimage.jpg';

    //Open new row if first record in row
    if($count % $maxColumns == 1) { $categoryList .= "<tr>\n"; }

    //Output current record
    $categoryList .= "<td width='20%' style='text-align:center;padding-top:10px;padding-bottom:10px;'>";
    $categoryList .= "<img src='{$imgSrc}' height='100' width='100'><br>";
    $categoryList .= "<a class=black-text href='showphoto.php?cid={$row['category_id']}'>{$row['category_name']}</a>";
    $categoryList .= "</td>\n";

    //Close row if last record in row
    if($count % $maxColumns == 0) { $categoryList .= "</tr>\n"; }
}

//Close last row if needed
if($maxColumns % $count != 0)
{
    $categoryList .= "</tr>\n";
}

?>
<table width="95%" border="0">
<?php echo $categoryList; ?>
</table>

 

Here is the solution that relies on the PHP code

 

<?php

$query = "SELECT category.category_id, category.category_name,
                 GROUP_CONCAT(photos.photo_filename) as photos
          FROM gallery_category AS category
          LEFT JOIN gallery_photos AS photos
              ON category.category_id = photos.photo_category
          WHERE category.photo_owner =  '$client'
          GROUP BY category.category_id
          ORDER BY category.category_name";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

//Define number of columns to use
$maxColumns  = 5;

$count = 0; //Var to count records
$categoryList = ''; //Var to hold HTML output
while($row = $result->fetch_assoc())
{
    $count++; //Increment count
    if(empty($row['photo_filename']))
    {
        //Use default image
        $imgSrc = 'images/noimage.jpg';
    }
    else
    {
        //Set Random image
        $imagesAry = explode($row['photos']);
        $imgName = $imagesAry[array_rand($imagesAry)];
        $imgSrc = "clients/{$client}/photos/{$imgName}";
    }

    //Open new row if first record in row
    if($count % $maxColumns == 1) { $categoryList .= "<tr>\n"; }

    //Output current record
    $categoryList .= "<td width='20%' style='text-align:center;padding-top:10px;padding-bottom:10px;'>";
    $categoryList .= "<img src='{$imgSrc}' height='100' width='100'><br>";
    $categoryList .= "<a class=black-text href='showphoto.php?cid={$row['category_id']}'>{$row['category_name']}</a>";
    $categoryList .= "</td>\n";

    //Close row if last record in row
    if($count % $maxColumns == 0) { $categoryList .= "</tr>\n"; }
}

//Close last row if needed
if($maxColumns % $count != 0)
{
    $categoryList .= "</tr>\n";
}

?>
<table width="95%" border="0">
<?php echo $categoryList; ?>
</table>

 

Note: I removed the center tag and the beginning and ending line breaks inside the TD's and instead added style properties to the TD. But, really, you should assign a class to the TDs and define the class. Better yet, you can assign a class to the table and then just define the class to apply to the TD elements of the table. Makes for much cleaner code.

[SkyRanger]

Perfect, thanks Psycho, going to fill my database and run some tests to ensure everything will work smoothly.