SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Show items only from selected category

    First I want to point that I'm newbie in php&mysql and still learning.
    I have 2 tables.
    1 table:
    Cats - where I have:
    Code PHP:
     - cat_id
     - cat_name
    2 table is for images and I have:
    Code PHP:
     - id
     - caption
     - name
     - size
     - type
     - file_path
     - img_category
    I'm trying to make category for my images on site. So far I can show categories list but how to make when I click on some category to show me images only from this category?
    Here is what I have so far but when I choose category it show me first image in that category and when I click 'Next' I get next image from next category.
    Code PHP:
    <?php
                // show categories list
                $q = mysqli_query($con,"select * from cats");
                while ($res = mysqli_fetch_assoc($q))
                {
                    echo '<a href="pic.php?cat_id='. $res['cat_id'] .'">'.$res['cat_name'].'<br/>';
                }
     
                ?>
                <hr>
                <?php
                 //show image from that category
                $cat_id = $_GET['cat_id'];
                $query = "SELECT * FROM images JOIN cats ON images.img_category = cats.cat_id WHERE cats.cat_id = '$cat_id'";
                $result = mysqli_query($con, $query) or die("Query failed: " . mysqli_errno($con));
     
                $line = mysqli_fetch_array($result, MYSQL_BOTH);
                if (!$line) echo '';
                $previd = -1;
                $currid = $line[0];
                if (isset($_GET['id'])) {
                    do {
                        $currid = $line[0];
                        if ($currid == $_GET['id']) break;
                        $previd = $currid;
                        $line = mysqli_fetch_array($result, MYSQL_BOTH);
                    } while ($line);
                }
     
                if ($line) {
                    echo "<div id=\"picture\">";
     
                    echo "<img style=\"width:100%;margin:0 auto;\" src=\"upload/".$line['name']."\" /></a><br />";
                    echo "<div id=\"caption\">".$line['caption']."</div><br />";
                }
                else echo "There is no images!\n";
     
                if ($previd > -1) echo '<a href="pic.php?cat_id='.$previd.'" class="prev_pic"><span>Prev</span></a>';
                echo str_repeat('&nbsp;', 5);
     
                $line = mysqli_fetch_array($result, MYSQL_BOTH);
     
                $query = "select * from images order by RAND() LIMIT 1";
                $result = mysqli_query($con, $query) or die("Query failed: " . mysqli_errno($con));
                while ($row = mysqli_fetch_array($result, MYSQL_BOTH)){
                    echo '<a href="pic.php?cat_id='.$row['id'].'"class="random">Random</a>';
                }
                echo str_repeat('&nbsp;', 5);
                if ($line) echo '<a href="pic.php?cat_id='.$line[0].'&id='.$line[0].'" class="next_pic"><span>Next</span> </a><br /><br />';
     
                echo "</div>";
     
                ?>
    I gues is a little mess in the code and I'm sorry about that.

    Any help is appreciate

    edit://
    One image can be in one category at the time.. one-to-one

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    41
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I had a fun time reading this because I thought you had a table literally for cats. You know the furry things that go meow and s**t.

    Your second query
    Code:
    SELECT * FROM images JOIN cats ON images.img_category = cats.cat_id WHERE cats.cat_id = '$cat_id'
    could be simplified to
    Code:
    SELECT * FROM images WHERE img_category = '$cat_id'
    Try changing this block
    PHP Code:
               $line mysqli_fetch_array($resultMYSQL_BOTH);
                if (!
    $line) echo '';
                
    $previd = -1;
                
    $currid $line[0];
                if (isset(
    $_GET['id'])) {
                    do {
                        
    $currid $line[0];
                        if (
    $currid == $_GET['id']) break;
                        
    $previd $currid;
                        
    $line mysqli_fetch_array($resultMYSQL_BOTH);
                    } while (
    $line);
                }
                if (
    $line) {
                    echo 
    "<div id=\"picture\">";
     
                    echo 
    "<img style=\"width:100%;margin:0 auto;\" src=\"upload/".$line['name']."\" /></a><br />";
                    echo 
    "<div id=\"caption\">".$line['caption']."</div><br />";
                }
                else echo 
    "There is no images!\n"
    to this
    PHP Code:
    function getAllRows($dbResult) {
       
    $rows = array();
       while (
    $row mysqli_fetch_assoc($dbResult)) { $rows[] = $row; }
       return 
    $rows; }

    foreach (
    $rows getAllRows($result) as $row) {
       echo 
    "<div id=\"picture\">";
       echo 
    "<img style=\"width:100%;margin:0 auto;\" src=\"upload/".$row['name']."\" /></a><br />";
       echo 
    "<div id=\"caption\">".$row['caption']."</div></div><br />"; }
    if (
    count($rows) == 0) { echo "There is no images!\n"; } 

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    41
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Oh and BTW this is a one-to-many relationship (many images in one category) not a one-to-one. Captions to images is a one-to-one (one caption per image).

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I get all the images from the category and I loose 'Prev' and 'Next' buttons.
    Code PHP:
     Notice: Undefined variable: previd in C:\wamp\www\pic.php on line 54
    It must be 1 image at the time whit pagination buttons for next and prev. So I can put LIMIT in sql query but how to make buttons to work?

    Quote Originally Posted by parallelist View Post
    Oh and BTW this is a one-to-many relationship (many images in one category) not a one-to-one. Captions to images is a one-to-one (one caption per image).
    So is my bed.. I thought is one-to-one because one image is in one category only not in many..

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    41
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Those links for prev and next are for the previous and next category, no? If so then just generate them from $cat_id.

    It must be 1 image at the time whit pagination buttons for next and prev. So I can put LIMIT in sql query but how to make buttons to work?
    Oh I see what you are trying to do now. Look up a tutorial on PHP pagination.

    I thought is one-to-one because one image is in one category only not in many..
    No because a category stores many images. A one-to-one between images and categories would be where you have exactly one category for each image.

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by parallelist View Post
    Those links for prev and next are for the previous and next category, no? If so then just generate them from $cat_id.
    No, for the previous and next image in that category. Here is where I get confused - how to get next image in that category.

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Took this from one tutorial. Problem is whenever I click 'Next' button the page reload but the image is the same.

    Code PHP:
    $q="select count(*) \"total\"  from images";
                $ros=mysqli_query($con, $q) or die(mysqli_error($con));
                $row=mysqli_fetch_array($ros);
                $total=$row['total'];
                $dis=1;
                $total_page=ceil($total/$dis);
                $page_cur=(isset($_GET['page']))?$_GET['page']:1;
                $k=($page_cur-1)*$dis;
     
                $q="select * from images limit $k,$dis";
                $ros=mysqli_query($con, $q);
     
                if($page_cur>1)
                {
                    echo '<a href="pic.php?cat_id='.$cat_id.'&id='.($page_cur-1).'" style="cursor:pointer;color:green;" ><input style="cursor:pointer;background-color:green;border:1px black solid;border-radius:5px;width:120px;height:30px;color:white;font-size:15px;font-weight:bold;" type="button" value=" Previous "></a>';
                }
                else
                {
                    echo '';
                }
     
                if($page_cur<$total_page)
                {
                    echo '<a href="pic.php?cat_id='.$cat_id.'&id='.($page_cur+1).'"><input style="cursor:pointer;background-color:green;border:1px black solid;border-radius:5px;width:90px;height:30px;color:white;font-size:15px;font-weight:bold;" type="button" value=" Next "></a>';
                }
                else
                {
                    echo '';
                }
     
                echo "</div>";
     
                ?>

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    41
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Ah OK, what you need is to use the LIMIT clause in MySQL.
    Quote Originally Posted by MySQL Cookbook
    MySQL supports a LIMIT clause that tells the server to return only part of a result set. LIMIT is a MySQL-specific extension to SQL that is extremely valuable when your result set contains more rows than you want to see at a time. It allows you to retrieve just the first part of a result set or an arbitrary section of the set.
    Quote Originally Posted by MySQL Cookbook
    LIMIT n tells the server to return the first n rows of a result set. LIMIT also has a two- argument form that allows you to pick out any arbitrary section of rows from a result. The arguments indicate how many rows to skip and how many to return. This means that you can use LIMIT to do such things as skip two rows and return the next
    So you’re probably want to query for the images within the category (as you are now) with the addition of a LIMIT $offset, 1 at the end where $offset is increased and decreased by your Next and Previous buttons

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I still don't get how to create link for next and prev image while I hold both category= and id= at the same time.
    I have one page also where I query all the images in the table and there I have buttons and no problem with them.. But in the category page I don't understand how this work.

  10. #10
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    148
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    First, I think you should check your tables. I think you need a category table, it'll make this easier IMO.

    Code:
    ===============
    CAT
    ===============
     - cat_id
     - cat_name
    
    ===============
    CAT_IMAGE
    ===============
     - cat_image_id
     - caption
     - name
     - size
     - type
     - file_path
     - cat_image_category_id
    
    ===============
    CAT_IMAGE_CATEGORY
    ===============
    - cat_image_category_id
    - name
    To SELECT all categories and display them (in alphabetical order):
    Code:
    SELECT cat_image_category_id, name FROM CAT_IMAGE_CATEGORY ORDER BY name
    With the result, you'll be able to display all the categories with their unique ID.
    When somebody clicks on a specific category, you'll grab the ID and select the corresponding images based on the selected category.

    To SELECT all images in one particular category, this would look like this:
    Code:
    SELECT file_path, caption FROM CAT_IMAGE WHERE cat_image_category_id = 18
    Whit this query you should be able to use pagination more easily.

    I know I didn't answer your "main" question, but starting with the right database design will help you a lot. And if you're able to display all images from a category, adding pagination after will be much more easier.

    By the way, you should check how to use "prepared statements" with mysqli as this will prevent you against SQL injections. While your code is not on a live website, it's not a problem but as soon as you want to put it live, I would look into it.

    Good luck!

  11. #11
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by xMog View Post
    First, I think you should check your tables. I think you need a category table, it'll make this easier IMO.

    Code:
    ===============
    CAT
    ===============
     - cat_id
     - cat_name
    
    ===============
    CAT_IMAGE
    ===============
     - cat_image_id
     - caption
     - name
     - size
     - type
     - file_path
     - cat_image_category_id
    
    ===============
    CAT_IMAGE_CATEGORY
    ===============
    - cat_image_category_id
    - name
    To SELECT all categories and display them (in alphabetical order):
    Code:
    SELECT cat_image_category_id, name FROM CAT_IMAGE_CATEGORY ORDER BY name
    With the result, you'll be able to display all the categories with their unique ID.
    When somebody clicks on a specific category, you'll grab the ID and select the corresponding images based on the selected category.

    To SELECT all images in one particular category, this would look like this:
    Code:
    SELECT file_path, caption FROM CAT_IMAGE WHERE cat_image_category_id = 18
    )
    I have some questions:
    1. Then what for will be first table:
    Code:
    ===============
    CAT
    ===============
     - cat_id
     - cat_name
    
    ===============
    2. When I upload image it will be in CAT_IMAGE.

    May be my biggest problem here is as you said the right database design. I don't undesrtand why 3 tables.

    And question 3 -
    Why in
    Code:
    SELECT file_path, caption FROM CAT_IMAGE WHERE cat_image_category_id = 18
    there is =18?

  12. #12
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    148
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    Ooops.. I tought CAT was a... CAT.. Haha I need some sleep :P I saw I did the same mistake as parallelist...

    So maybe just renaming some of your field like this:

    Code:
    ===============
    IMAGE_CATEGORY
    ===============
     - image_category_id
     - name
    
    ===============
    IMAGE
    ===============
    - image_id
    - image_category_id
    - caption
    - name
    - size
    - type
    - file_path
    With this structure, what you have is a list of images. Those images are "included" inside one and only one category.

    For example:
    Category XYZ includes image 1, 3 and 5
    Category ABC includes image 7, 8 and 9
    and so forth...

    Is this what you want?

    So if I understand correctly now (I hope), you want to paginate the images within a selected category?

  13. #13
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, this is what I try to achieve. I have this two tables:
    Code:
    ===============
    IMAGE_CATEGORY
    ===============
     - image_category_id
     - name
    
    ===============
    IMAGE
    ===============
    - image_id
    - image_category_id
    - caption
    - name
    - size
    - type
    - file_path
    For example category like:

    Cars
    Animals
    Buildings

    If I have 3 images in category 'Car' and I choose on index page category 'Car' I want to load first image from this category then whit buton 'Next' change to second image from this category and so on... That's it.

  14. #14
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    742
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Without redoing the whole thing.... Give this version a go.
    PHP Code:
    <?php
    // show categories list
    $q mysqli_query($con,"select * from cats ORDER BY cat_name ASC");
    while (
    $res mysqli_fetch_assoc($q))
    {
    echo 
    '<a href="pic.php?cat_id='$res['cat_id'] .'">'.$res['cat_name'].'</a><br/>';
    }

    ?>
    <hr />
    <?php
    //show image from that category
    if(isset($_GET['cat_id'])){
    $cat_id $_GET['cat_id'];
    $query "SELECT * FROM images WHERE img_category = '$cat_id'";
    $result mysqli_query($con$query) or die("Query failed: " mysqli_errno($con));

    $line mysqli_fetch_array($resultMYSQL_BOTH);
    if (!
    $line) echo '';
    $previd = -1;
    $currid $line[0];
    if (isset(
    $_GET['id'])) {
            
    $previous_ids = array();
        do { 
            
    $previous_ids[] = $line[0];
            
    $currid $line[0];
            if (
    $currid == $_GET['id']) break;
            
    $previd end($previous_ids);
            
    $line mysqli_fetch_array($resultMYSQL_BOTH);
        } while (
    $line);
    }

    if (
    $line) {
        echo 
    "<div id=\"picture\">";
        
        echo 
    "<img style=\"width:100%;margin:0 auto;\" src=\"upload/".$line['name']."\" /><br />\r";
        echo 
    "<div id=\"caption\">".$line['caption']."</div><br />";
    }
    else echo 
    "There is no images!\n";

    if (
    $previd > -1
        echo 
    '<a href="pic.php?cat_id='.$cat_id.'&amp;id='.$previd.'" class="prev_pic"><span>Prev</span></a>';
        echo 
    str_repeat('&nbsp;'5);
        
        
    $line mysqli_fetch_array($resultMYSQL_BOTH);
        
        
    $query "select * from images order by RAND() LIMIT 1";
        
    $result mysqli_query($con$query) or die("Query failed: " mysqli_errno($con));
        while (
    $row mysqli_fetch_array($resultMYSQL_BOTH)){
            echo 
    '<a href="pic.php?cat_id='.$cat_id.'&amp;id='.$row['id'].'"class="random">Random</a>';
        }
        echo 
    str_repeat('&nbsp;'5);
        if (
    $line) echo '<a href="pic.php?cat_id='.$cat_id.'&amp;id='.$line[0].'" class="next_pic"><span>Next</span> </a><br /><br />';
        
            echo 
    "</div>\r";
        }
    ?>

  15. #15
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Drummin View Post
    Without redoing the whole thing.... Give this version a go.
    PHP Code:
    <?php
    // show categories list
    $q mysqli_query($con,"select * from cats ORDER BY cat_name ASC");
    while (
    $res mysqli_fetch_assoc($q))
    {
    echo 
    '<a href="pic.php?cat_id='$res['cat_id'] .'">'.$res['cat_name'].'</a><br/>';
    }

    ?>
    <hr />
    <?php
    //show image from that category
    if(isset($_GET['cat_id'])){
    $cat_id $_GET['cat_id'];
    $query "SELECT * FROM images WHERE img_category = '$cat_id'";
    $result mysqli_query($con$query) or die("Query failed: " mysqli_errno($con));

    $line mysqli_fetch_array($resultMYSQL_BOTH);
    if (!
    $line) echo '';
    $previd = -1;
    $currid $line[0];
    if (isset(
    $_GET['id'])) {
            
    $previous_ids = array();
        do { 
            
    $previous_ids[] = $line[0];
            
    $currid $line[0];
            if (
    $currid == $_GET['id']) break;
            
    $previd end($previous_ids);
            
    $line mysqli_fetch_array($resultMYSQL_BOTH);
        } while (
    $line);
    }

    if (
    $line) {
        echo 
    "<div id=\"picture\">";
        
        echo 
    "<img style=\"width:100%;margin:0 auto;\" src=\"upload/".$line['name']."\" /><br />\r";
        echo 
    "<div id=\"caption\">".$line['caption']."</div><br />";
    }
    else echo 
    "There is no images!\n";

    if (
    $previd > -1
        echo 
    '<a href="pic.php?cat_id='.$cat_id.'&id='.$previd.'" class="prev_pic"><span>Prev</span></a>';
        echo 
    str_repeat('*'5);
        
        
    $line mysqli_fetch_array($resultMYSQL_BOTH);
        
        
    $query "select * from images order by RAND() LIMIT 1";
        
    $result mysqli_query($con$query) or die("Query failed: " mysqli_errno($con));
        while (
    $row mysqli_fetch_array($resultMYSQL_BOTH)){
            echo 
    '<a href="pic.php?cat_id='.$cat_id.'&id='.$row['id'].'"class="random">Random</a>';
        }
        echo 
    str_repeat('*'5);
        if (
    $line) echo '<a href="pic.php?cat_id='.$cat_id.'&id='.$line[0].'" class="next_pic"><span>Next</span> </a><br /><br />';
        
            echo 
    "</div>\r";
        }
    ?>
    IIt's just working as expected and seems so easy.. I don't know what to say. Thank you very much!
    Just one more thig:

    On 'Random' button when hit some 'id' from another category it shows blank page. Is there some way to search and show random image just from this category?

  16. #16
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    742
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Oh I missed that.. You need to add the category_id to the condition.
    PHP Code:
    $query "select * from images WHERE img_category = '$cat_id' order by RAND() LIMIT 1"

  17. #17
    SitePoint Enthusiast
    Join Date
    Feb 2014
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it's working perfectly now!
    Thank you very much!

  18. #18
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    742
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    You're welcome.


Bookmarks

Posting Permissions

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