Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Feb 2006
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display random image

    Hi,

    I have 3 tables, maincat, subcat, images.

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

    table structures:

    Code:
    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    New Coder
    Join Date
    Feb 2006
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Not really following your reply.

    I tried something like:

    PHP Code:
    $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

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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:
    Code:
    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
    Code:
    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.

  • #5
    New Coder
    Join Date
    Feb 2006
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for that .. I get the following error:

    Code:
    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

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Whoops, you'll need to stick LIMIT 1 in there.

  • #7
    New Coder
    Join Date
    Feb 2006
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

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


    Cheers

  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #9
    New Coder
    Join Date
    Feb 2006
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

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

    PHP Code:
            $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

  • #10
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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?

  • #11
    New Coder
    Join Date
    Feb 2006
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

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

    PHP Code:
        $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:

    PHP 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
    Last edited by jim_bo; 09-11-2006 at 01:39 AM.

  • #12
    New Coder
    Join Date
    Feb 2006
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I beleave I have it working:

    PHP Code:
    $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

  • #13
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •