Mysql query results to array

[dxm31]

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?

[JeremyC]

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.

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

[dxm31]

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 $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 < 7 + ($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 > 5 + ($stages * 2))    // Enough pages to hide a few?
        {
            // Beginning only hide later pages
            if($page < 1 + ($stages * 2))        
            {
                for ($counter = 1; $counter < 4 + ($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 - (2 + ($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;

?>

[guido2004]

Get rid of the '' around the column names.
'columnname' is a string, and will never be equal to '1'

[StarLion]

Incidentally, dont use that structure.

gallery_tags should be a table of two fields, gallery_id and tag. They are a natural key duple.

[dxm31]

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 $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 < 7 + ($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 > 5 + ($stages * 2))    // Enough pages to hide a few?
        {
            // Beginning only hide later pages
            if($page < 1 + ($stages * 2))        
            {
                for ($counter = 1; $counter < 4 + ($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 - (2 + ($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.

[guido2004]

That error indicates the query has thrown an error. To see it, rewrite the code like this:

    
  $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

[aaarrrggh]

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.