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?
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'
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;
?>
Get rid of the ‘’ around the column names.
‘columnname’ is a string, and will never be equal to ‘1’
Incidentally, dont use that structure.
gallery_tags should be a table of two fields, gallery_id and tag. They are a natural key duple.
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.
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 [URL=“http://www.php.net/manual/en/book.mysqli.php”]mysqli or [URL=“http://www.php.net/manual/en/book.pdo.php”]pdo
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.