SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql query results to array

    I have a photo gallery that works fine. I am thinking I want to add tags to photos i.e. people in photo, place ect. the way i have my db set up now

    Gallery
    id
    main_cat
    gallery_thumb
    title
    gallery_views

    gallery_tags
    gallery_id
    England
    Ireland
    land
    sea
    ect.

    How do I query gallery_tags to select all where england = 1 and get the gallery_id from those to then pull from table gallery?

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    70
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You will need to use a Join statement to link the two tables in a query. This will pull all gallery id's from the db where gallery_tags.England = 1.

    Code MySQL:
    SELECT id FROM Gallery
    INNER JOIN gallery_tags on Gallery.id = gallery_tags.gallery_id
    WHERE gallery_tags.England = '1'

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks JeremyC that has gotten me a little further however I'm not getting any results from the query it always has 0 results and it should pull up 4. Here is my script.
    PHP Code:
    <?php $like $_GET['like'];
                
                
        
        include(
    'include/dbconfig.php');    
        
    $tableName="gallery";        
        
    $targetpage "similar.php";     
        
    $limit 16;    
        
    $query "SELECT COUNT(*) as num FROM $tableName 
                    INNER JOIN gallery_tags on gallery_id = gallery_tags.gallery_id
                    WHERE '
    $like' = '1'";
        
    $total_pages mysql_fetch_array(mysql_query($query));
        
    $total_pages $total_pages['num'];
        echo 
    $total_pages;
        
    $stages 3;
        
    $page mysql_escape_string($_GET['page']);
        if(
    $page){
            
    $start = ($page 1) * $limit
        }else{
            
    $start 0;    
            }    
        
        
    // Get page data
        
    $query1 "SELECT COUNT(*) as num FROM $tableName 
                    INNER JOIN gallery_tags on gallery.id = gallery_tags.gallery_id
                    WHERE '
    $like' = '1'";
        
    $result mysql_query($query1);
        
        
    // Initial page num setup
        
    if ($page == 0){$page 1;}
        
    $prev $page 1;    
        
    $next $page 1;                            
        
    $lastpage ceil($total_pages/$limit);        
        
    $LastPagem1 $lastpage 1;                    
        
        
        
    $paginate '';
        if(
    $lastpage 1)
        {    
        

        
        
            
    $paginate .= "<div class='paginate'>";
            
    // Previous
            
    if ($page 1){
                
    $paginate.= "<a href='$targetpage?page=$prev&gallery=$gallery'>previous</a>";
            }else{
                
    $paginate.= "<span class='disabled'>previous</span>";    }
                

            
            
    // Pages    
            
    if ($lastpage + ($stages 2))    // Not enough pages to breaking it up
            
    {    
                for (
    $counter 1$counter <= $lastpage$counter++)
                {
                    if (
    $counter == $page){
                        
    $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        
    $paginate.= "<a href='$targetpage?page=$counter&gallery=$gallery'>$counter</a>";}                    
                }
            }
            elseif(
    $lastpage + ($stages 2))    // Enough pages to hide a few?
            
    {
                
    // Beginning only hide later pages
                
    if($page + ($stages 2))        
                {
                    for (
    $counter 1$counter + ($stages 2); $counter++)
                    {
                        if (
    $counter == $page){
                            
    $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            
    $paginate.= "<a href='$targetpage?page=$counter&gallery=$gallery'>$counter</a>";}                    
                    }
                    
    $paginate.= "...";
                    
    $paginate.= "<a href='$targetpage?page=$LastPagem1&gallery=$gallery'>$LastPagem1</a>";
                    
    $paginate.= "<a href='$targetpage?page=$lastpage&gallery=$gallery'>$lastpage</a>";        
                }
                
    // Middle hide some front and some back
                
    elseif($lastpage - ($stages 2) > $page && $page > ($stages 2))
                {
                    
    $paginate.= "<a href='$targetpage?page=1&gallery=$gallery'>1</a>";
                    
    $paginate.= "<a href='$targetpage?page=2&gallery=$gallery'>2</a>";
                    
    $paginate.= "...";
                    for (
    $counter $page $stages$counter <= $page $stages$counter++)
                    {
                        if (
    $counter == $page){
                            
    $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            
    $paginate.= "<a href='$targetpage?page=$counter&gallery=$gallery'>$counter</a>";}                    
                    }
                    
    $paginate.= "...";
                    
    $paginate.= "<a href='$targetpage?page=$LastPagem1&gallery=$gallery'>$LastPagem1</a>";
                    
    $paginate.= "<a href='$targetpage?page=$lastpage&gallery=$gallery'>$lastpage</a>";        
                }
                
    // End only hide early pages
                
    else
                {
                    
    $paginate.= "<a href='$targetpage?page=1&gallery=$gallery'>1</a>";
                    
    $paginate.= "<a href='$targetpage?page=2&gallery=$gallery'>2</a>";
                    
    $paginate.= "...";
                    for (
    $counter $lastpage - (+ ($stages 2)); $counter <= $lastpage$counter++)
                    {
                        if (
    $counter == $page){
                            
    $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            
    $paginate.= "<a href='$targetpage?page=$counter&gallery=$gallery'>$counter</a>";}                    
                    }
                }
            }
                        
                    
    // Next
            
    if ($page $counter 1){ 
                
    $paginate.= "<a href='$targetpage?page=$next&gallery=$gallery'>next</a>";
            }else{
                
    $paginate.= "<span class='disabled'>next</span>";
                }
                
            
    $paginate.= "</div>";        
        
        
    }
     echo 
    $total_pages.' Similar Gallerier';
     
    // pagination

    echo $paginate;
    // display gallery thumb

            
    while($row mysql_fetch_array($result))
            {
            
            echo 
    "<div class='img'>
      <a href='"
    .$row['path']."'>
      <img src="
    .$row['path']." width='150' height='200' />
      </a>
      <div class='desc'></div>
            </div>"
    ;
            
            
            }
        echo 
    $paginate;

    ?>

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Get rid of the '' around the column names.
    'columnname' is a string, and will never be equal to '1'

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Incidentally, dont use that structure.

    gallery_tags should be a table of two fields, gallery_id and tag. They are a natural key duple.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all the help its working great. However the script I have been using for paginate is not working on this page. I dont think I am doing it the easy way anyway if one of you could take a look and point me in the right direction id appreciate it.
    PHP Code:
    <?php $like $_GET['like'];
                
                echo 
    $like;
        
        include(
    'include/dbconfig.php');    
        
    $tableName="gallery";        
        
    $targetpage "similar.php";     
        
    $limit 16;    
        
    $query "SELECT COUNT(*) as num gallery.id, gallery.gallery_photo, gallery.title FROM gallery   //Line 150
                    INNER JOIN gallery_tags
                    ON gallery_tags.gallery_id=gallery.id
                    WHERE gallery_tags.
    $like='1'";
        
    $total_pages mysql_fetch_array(mysql_query($query));
        
    $total_pages $total_pages['num'];
        echo 
    $total_pages;
        
    $stages 3;
        
    $page mysql_escape_string($_GET['page']);
        if(
    $page){
            
    $start = ($page 1) * $limit
        }else{
            
    $start 0;    
            }    
        
        
    // Get page data
        
    $query1 "SELECT gallery.id, gallery.gallery_photo, gallery.title FROM gallery
                    INNER JOIN gallery_tags
                    ON gallery_tags.gallery_id=gallery.id
                    WHERE gallery_tags.
    $like='1'";
        
    $result mysql_query($query1);
        
        
    // Initial page num setup
        
    if ($page == 0){$page 1;}
        
    $prev $page 1;    
        
    $next $page 1;                            
        
    $lastpage ceil($total_pages/$limit);        
        
    $LastPagem1 $lastpage 1;                    
        
        
        
    $paginate '';
        if(
    $lastpage 1)
        {    
        

        
        
            
    $paginate .= "<div class='paginate'>";
            
    // Previous
            
    if ($page 1){
                
    $paginate.= "<a href='$targetpage?page=$prev'>previous</a>";
            }else{
                
    $paginate.= "<span class='disabled'>previous</span>";    }
                

            
            
    // Pages    
            
    if ($lastpage + ($stages 2))    // Not enough pages to breaking it up
            
    {    
                for (
    $counter 1$counter <= $lastpage$counter++)
                {
                    if (
    $counter == $page){
                        
    $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        
    $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                }
            }
            elseif(
    $lastpage + ($stages 2))    // Enough pages to hide a few?
            
    {
                
    // Beginning only hide later pages
                
    if($page + ($stages 2))        
                {
                    for (
    $counter 1$counter + ($stages 2); $counter++)
                    {
                        if (
    $counter == $page){
                            
    $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            
    $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                    }
                    
    $paginate.= "...";
                    
    $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
                    
    $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";        
                }
                
    // Middle hide some front and some back
                
    elseif($lastpage - ($stages 2) > $page && $page > ($stages 2))
                {
                    
    $paginate.= "<a href='$targetpage?page=1'>1</a>";
                    
    $paginate.= "<a href='$targetpage?page=2'>2</a>";
                    
    $paginate.= "...";
                    for (
    $counter $page $stages$counter <= $page $stages$counter++)
                    {
                        if (
    $counter == $page){
                            
    $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            
    $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                    }
                    
    $paginate.= "...";
                    
    $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
                    
    $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";        
                }
                
    // End only hide early pages
                
    else
                {
                    
    $paginate.= "<a href='$targetpage?page=1'>1</a>";
                    
    $paginate.= "<a href='$targetpage?page=2'>2</a>";
                    
    $paginate.= "...";
                    for (
    $counter $lastpage - (+ ($stages 2)); $counter <= $lastpage$counter++)
                    {
                        if (
    $counter == $page){
                            
    $paginate.= "<span class='current'>$counter</span>";
                        }else{
                            
    $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                    }
                }
            }
                        
                    
    // Next
            
    if ($page $counter 1){ 
                
    $paginate.= "<a href='$targetpage?page=$next'>next</a>";
            }else{
                
    $paginate.= "<span class='disabled'>next</span>";
                }
                
            
    $paginate.= "</div>";        
        
        
    }
     echo 
    $total_pages.' Results';
     
    // pagination
     
    echo $paginate;
    ?>


    <?php 
      
    echo $paginate;

            while(
    $row mysql_fetch_array($result))
            {
            
            echo 
    "<div class='img'>
      <a href='gallery.php?gallery="
    .$row['id']."'>
      <img src="
    .$row['gallery_photo']." width='150' height='200' />
      </a>
      <div class='desc'>"
    .$row['title']."</div>
            </div>"
    ;
            
            
            }
        echo 
    $paginate;
        
    ?>
    The error i am getting is Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in F:\wamp\www\gallery\similar.php on line 150
    I have added a note on line 150. And i come to realize the way i am tagging the photos is very limited I have never done anything like this before. Can some one try to explain or post an example of how the tags work on youtube or other sites where you enter tags like- 4th july, 4, fireworks and so on.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    That error indicates the query has thrown an error. To see it, rewrite the code like this:
    Code:
        
      $query = "SELECT COUNT(*) as num gallery.id, gallery.gallery_photo, gallery.title FROM gallery   //Line 150
                    INNER JOIN gallery_tags
                    ON gallery_tags.gallery_id=gallery.id
                    WHERE gallery_tags.$like='1'";
      $result = mysql_query($query) or die('mysql error ' . mysql_error() . ' in query: ' . $query);
      $total_pages = mysql_fetch_array($result);
    By the way, since the mysql_ database extension is becoming deprecated, you might want to take a look at mysqli or pdo

  8. #8
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    You'll also want to look into separating design from content.

    That code is going to be really hard to maintain and to change in the future. You'll struggle to ever change the design there too. If I inherited that code, I'd refactor it from scratch.


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
  •