Jump to content


Photo

Tabbed image gallery problem

tabbed gallery images

  • Please log in to reply
25 replies to this topic

#1 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 14 January 2013 - 12:31 PM

Hi,

I am trying to create a tabbed image gallery using php and mysql .

The following are my table structure.

CREATE TABLE IF NOT EXISTS `gallery_category` (
`category_id` int(11) NOT NULL auto_increment,
`category_name` text NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `photos` (
`photo_id` int(20) NOT NULL auto_increment,
`photo_filename` varchar(25) NOT NULL,
`photo_caption` text NOT NULL,
`photo_category` bigint(20) NOT NULL,
PRIMARY KEY (`photo_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=56 ;

I am tring to create a tabbed gallery like this.

	 <ul class="tabs">
		 <li><a href="#" rel="view1">gallery1</a></li>
		 <li><a href="#" rel="view2">gallery2</a></li>
	 </ul>
	 <div class="tabcontents">
		 <div id="view1" class="tabcontent">
			 Gallery1 images goes here.
		 </div>
		 <div id="view2" class="tabcontent">
			 Gallery2 images goes here.
		 </div>
	 </div>
Here is my code which i am trying to make this work.
<ul class="tabs">
<?php $sql = "SELECT * FROM category ORDER BY category_id ASC";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query))
{
$cid = $row['category_id'];
$category_name = $row['category_name'];
echo "<li><a href='?catid=".$cid."' rel='view".$cid."'>$category_name</a></li> ";
}
echo "</ul>";
?>
			
<div class="tabcontents">
<?php
$sql = "SELECT * FROM category ORDER BY category_id ASC";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query))
{
$catid = $row['category_id'];
$category_name = $row['category_name'];
$count = $row['COUNT(photo_id)'];
echo"<div id='view".$catid."' class='tabcontent'>";
		
$catid = (int)($_GET['catid']);
$sql = "SELECT * FROM gallery_photos WHERE photo_category = $catid ORDER BY photo_id DESC";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query))
{
$pid = $row['photo_id'];
$photo_caption = $row['photo_caption'];
$photo_filename = $row['photo_filename'];
echo "$photo_caption</br>";
echo "<img src='".$images_dir."/tb_".$row[photo_filename]."' border='0'";
}
		
}
?>
</div>
</div>


The category is displaying correctly (first <ul> </ul> part) but it content display part is not working.

Can any one please help me with this..

Thanks,
Sam.

#2 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 15 January 2013 - 04:48 AM

Can anyone please point out me the problem in the above code. ?

Thanks in advance,
Sam.

#3 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 16 January 2013 - 04:54 AM

Is there anybody know how to get the value of an active tab (id value) to make the content part working..?

#4 PFMaBiSmAd

PFMaBiSmAd
  • Staff Alumni
  • Advanced Member
  • 16,767 posts
  • LocationColorado, U.S.A.

Posted 16 January 2013 - 12:52 PM

Your post doesn't contain enough relevant or actual information to help you. In fact your database table names don't match what the code is using, so the posted code cannot be producing any output (yes it matters if you post actual information or altered/paraphrased/off-the-top-of-your-head information.) If you find yourself bumping a thread, but you are not adding any information or clarifying the problem in those bumps, you might as well not bump the thread.

It's likely that the tabbed gallery javascript code expects the content <div>'s to all be output when the page is requested and they are just reveled when the corresponding tab is clicked.

If on the other hand, you expect to click on the link in a tab and request the correct gallery, you would need to write your code so that when $_GET['catid'] is set that you output the correct <div></div> contents for just the requested gallery.

Clarification is needed on what you are actually trying to produce as output on the page.

Edited by PFMaBiSmAd, 16 January 2013 - 12:53 PM.

Signature: (not a comment about anything you posted unless specifically indicated)
Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

Programming is just problem solving, but it is done in another language. You must learn enough of the programming language you are using to be able to read and write code.

#5 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 17 January 2013 - 05:54 AM

Hi PFMaBiSmAd,

Thanks for your reply. Sorry for the errors in my posting. I was trying to make this work from last one week and many versions i tried. But still it is not working.

I am trying to make a tabbed gallery. I got this tabbed page script from the net. It actually works like this.
	 <ul class="tabs">				
		 <li><a href="#" rel="view1">tab1</a></li>				
		 <li><a href="#" rel="view2">tab2</a></li>		
	 </ul>		
	 <div class="tabcontents">				
		 <div id="view1" class="tabcontent">						
			 tab1 content goes here.				
		 </div>				
		 <div id="view2" class="tabcontent">						
		 tab2 content goes here.				
	 </div>		
</div>

There is no javacript to get the value of tabs.

I have modified the above code to make my gallery works.

	 <ul class="tabs">
	 <?php $sql = "SELECT * FROM gallery_category ORDER BY category_id ASC";
	 $query = mysql_query($sql);
	 while($row = mysql_fetch_array($query))
	 {
	 $cid = $row['category_id'];
	 $category_name = $row['category_name'];
	 echo "<li><a href='?catid=".$cid."' rel='view".$cid."'>$category_name</a></li> ";
	 }
	 echo "</ul>";
	 ?>

The above part is working fine.

May be i am doing wrong here.


<div class="tabcontents">
	 <?php
	 //$catid = (int)($_GET['catid']);
	 $sql = "SELECT * FROM gallery_category ORDER BY category_id ASC";
	 $query = mysql_query($sql);
	 $catarray = array();
			 while($row = mysql_fetch_array($query))
		 {
	 $catarray[] = $row['category_id'];
		 // $catid = $row['category_id'];

		 foreach($catarray as $catid){
		 echo"<div id='view".$catid."' class='tabcontent'>";
		
			 $catid = (int)($_GET['catid']);
		
		 $sql = "SELECT * FROM photos WHERE photo_category = $catid ORDER BY photo_id DESC";
		
		 $query = mysql_query($sql);
		 while($row = mysql_fetch_array($query))
		 {
		 $pid = $row['photo_id'];
		 $photo_caption = $row['photo_caption'];
		 $photo_filename = $row['photo_filename'];
		 echo "$photo_caption</br>";
		 echo "<img src='".$images_dir."/tb_".$row[photo_filename]."' border='0'";
		 }
		 echo "</div>";
		 }	
	 }
	
	 ?>
</div>


Can you please help me to solve my problem here. I am really confused how to do that..and i am wasting lot of time also without any success. Any help will be greatly appreciated.

Thanks,
Sam.

#6 Christian F.

Christian F.
  • Staff Alumni
  • Advanced Member
  • 3,106 posts
  • LocationNorway

Posted 17 January 2013 - 07:29 AM

The first thing I'd do, is to remove the query from the loop and use JOIN instead. Then use one loop to loop through all of the content, adding the view headers whenever a new category is detected. It'll save you a whole lot of processing time, and make things a bit easier for you in the long run.

Though, that said: I still don't actually know what's wrong with the code, or rather its result, as you haven't told us. After all, if you don't tell us what the result is, and how it differs from the expected result, we have no (reliable) means of knowing where the error occurs. All we know is that there is an error, or most likely so, because you're telling us that it's not working (correctly).

In short: Help us help you, by giving us as much information as you can. As long as it's relevant information, mind you. :P
Keeping it simple.

#7 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 17 January 2013 - 12:13 PM

The first thing I'd do, is to remove the query from the loop and use JOIN instead. Then use one loop to loop through all of the content, adding the view headers whenever a new category is detected. It'll save you a whole lot of processing time, and make things a bit easier for you in the long run.


I didnot clear this part to make a join. Sorry i am not that much good in writting sql queries.

I have added a javascript part also with the code which i got it from the same script site.

var tabs=function(){var c=function(c,a){var b=new RegExp("(^| )"+a+"( |$)");return b.test(c.className)?true:false},j=function(a,<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />{if(!c(a,<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />)if(a.className=="")a.className=b;else a.className+=" "+b},h=function(a,<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />{var c=new RegExp("(^| )"+b+"( |$)");a.className=a.className.replace(c,"$1");a.className=a.className.replace(/ $/,"")},g=function(c,<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />{var a=document.getElementsByTagName("html");if(a)a[0].scrollTop+=b},e=function(){var a=window.location.pathname;if(a.indexOf("/")!=-1)a=a.split("/");var b=a[a.length-1]||"root";if(b>20)b=b.substring(b.length-19);return b},a=e(),d=function(a){this.a=0;this.b=[];this.c=[];this.d=[];this.e=0;this.f(a)}<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/wink-01.gif' class='bbc_emoticon' alt=';D' />.prototype={g:function(c){var d=new RegExp(a+c+"=[^;&]+"),b=document.cookie.match(d);return b?b[0].split("=")[1]:this.h()},h:function(){for(var a=0,b=this.d.length;a<b;a++)if(c(this.d[a],"selected"))return a;return 0},j:function(d,c){for(var b=d.getAttribute("rel"),a=0;a<this.b.length;a++)if(this.b[a].getAttribute("rel")==<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />{j(this.b[a].parentNode,"selected");c&&this.e&&this.k(this.a,a)}else h(this.b[a].parentNode,"selected");this.l(<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />},k:function(b,c){if(document.cookie.indexOf("tabContent=")==-1)document.cookie="tabContent="+a+b+"="+c+";path=/";else if(document.cookie.indexOf(a+<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />==-1){var e=new RegExp("tabContent=[^;]+");document.cookie=document.cookie.match(e)[0]+"&"+a+b+"="+c+";path=/"}else{var e=new RegExp(a+b+"=\\d+","g"),d=document.cookie.replace(e,a+b+"="+c);document.cookie=d.substring(d.indexOf("tabContent="))}},l:function(<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />{for(var a=0;a<this.c.length;a++)this.c[a].style.display=this.c[a].id==b?"block":"none"},m:function(a){if(a.id)for(var b=0;b<this.b.length;b++)if(this.b[b].getAttribute("rel")==a.id)return this.b[b];return a.parentNode.nodeName!="BODY"?this.m(a.parentNode):null},n:function(d,c){var a=document.getElementById(d);if(a){var b=this.m(a);if(<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />{this.j(b,0);if(!c)setTimeout(function(){a.scrollIntoView();g(a,-120)},0);else setTimeout(function(){window.scrollTo(0,0)},0);return 1}else return 0}},f:function(a){this.a=a.i;this.b=a.getElementsByTagName("a");this.d=a.getElementsByTagName("li");for(var b=0;b<this.b.length;b++)if(this.b[b].getAttribute("rel")){this.c.push(document.getElementById(this.b[b].getAttribute("rel")));var f=this;this.b[b].onclick=function(){f.j(this,1);return false}}var e=a.getAttribute("persist")||"";this.e=e.toLowerCase()=="true"?1:0;var d=window.location.hash;if(d&&d.length>1)if(this.n(d.substring(1),window.location.search.indexOf("noscroll=true")>-1))return;var c=this.e?parseInt(this.g(a.i)):this.h();if(c>=this.b.length)c=0;this.j(this.b[c],0)}};var b=[],i=function(d){var b=false;function a(){if(b)return;b=true;setTimeout(d,4)}if(document.addEventListener)document.addEventListener("DOMContentLoaded",a,false);else if(document.attachEvent){try{var e=window.frameElement!=null}catch(f){}if(document.documentElement.doScroll&&!e){function c(){if(b)return;try{document.documentElement.doScroll("left");a()}catch(d){setTimeout(c,10)}}c()}document.attachEvent("onreadystatechange",function(){document.readyState==="complete"&&a()})}if(window.addEventListener)window.addEventListener("load",a,false);else window.attachEvent&&window.attachEvent("onload",a)},f=function(){for(var e=document.getElementsByTagName("ul"),a=0,f=e.length;a<f;a++)if(c(e[a],"tabs")){e[a].i=b.length;b.push(new d(e[a]))}};i(f);return{open:function(c,d){for(var a=0;a<b.length;a++)b[a].n(c,d)}}}()

What i am trying to acheive is that if i click a tab its content should display there. But in my case its not displaying. If i click the tab1, its content is showing. But if i click on the second tab the content is not showing. Also if I click a link under a tab, page refreshes and all ok, then move to another tab and click a link under that tab(tab2) and on the page refresh the tab goes back to the first tab(tab1). Thats the problem i am facing now. Its not showing any errors, but i am getting tabs with out any content.I am thinking something wrong in getting the value of catid or the value is not moving to the second tab. Please let me know if you need any more clarification of the problem.

Is there any simple way i can display the image gallery pictures in a tabbed view?

Thanks,
Sam.

#8 Christian F.

Christian F.
  • Staff Alumni
  • Advanced Member
  • 3,106 posts
  • LocationNorway

Posted 17 January 2013 - 12:26 PM

Here's the query that will extract all of the data you want, in the order you want it.
SELECT gc.category_id, p.photo_id, p.photo_caption, p.photo_filename
FROM gallery_category AS gc
INNER JOIN photos AS p ON p.photo_category = gc.category_id
ORDER BY gc.category_id ASC, p.photo_id DESC

If you compare it to the two queries you have, you should be able to spot what I've done and how simple it actually is. ;)

As for your problem: Do you have a link to a site which displays it?
Keeping it simple.

#9 PFMaBiSmAd

PFMaBiSmAd
  • Staff Alumni
  • Advanced Member
  • 16,767 posts
  • LocationColorado, U.S.A.

Posted 17 January 2013 - 12:45 PM

And once you have a query that does what you want, here's example code (untested) that will produce the output you are trying to make -

edit - modified the 'tab' code to detect when the gallery changes -
$tab_content = '';
$gallery_content = '';

$last_gallery = null;

$query = "single query that gets the joined information you want in the order that you want it";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){
// category/tab values
$catid = $row['category_id'];
$category_name = $row['category_name'];

// gallery values
$photo_caption = $row['photo_caption'];
$photo_filename = $row['photo_filename'];

// build tab content
if($last_gallery != $catid){
$tab_content .= "<li><a href='#' rel='view{$catid}'>$category_name</a></li>";
$last_gallery = $catid;
}

// build gallery content
$gallery_content .= "<div id='view{$catid}' class='tabcontent'>";
$gallery_content .= "$photo_caption</br>";
$gallery_content .= "<img src='{$images_dir}/tb_{$photo_filename}' border='0'>";
$gallery_content .= "</div>";
}


// output the HTML document -
?>
<ul class="tabs">
<?php echo $tab_content; ?>
</ul>
					
<div class="tabcontents">
<?php echo $gallery_content; ?>
</div>

Edited by PFMaBiSmAd, 17 January 2013 - 12:55 PM.

Signature: (not a comment about anything you posted unless specifically indicated)
Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

Programming is just problem solving, but it is done in another language. You must learn enough of the programming language you are using to be able to read and write code.

#10 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 17 January 2013 - 01:45 PM

Thank you both of you for helping me in this problem. But still its not working as expected.

$query = "single query that gets the joined information you want in the order that you want it";

Here i used the query "

SELECT gc.category_id, gc.category_name, p.photo_id, p.photo_caption, p.photo_filename
FROM gallery_category AS gc
INNER JOIN photos AS p ON p.photo_category = gc.category_id
ORDER BY gc.category_id ASC , p.photo_id DESC

" as Christian suggested and i got the following result.

category_id ---/--- category_name ---/--- photo_id ---/--- photo_caption ---/--- photo_filename
---1----------/---- Test1------------/---- 1---------/---- test1-----------/--- 1.jpg-----

---1----------/---- Test2------------/---- 2---------/---- test2-----------/--- 2.jpg-----

---1----------/---- Test3------------/---- 3---------/---- test3-----------/--- 3.jpg-----

---1----------/---- Test4------------/---- 4---------/---- test4-----------/--- 4.jpg-----

---2----------/---- Test5------------/---- 5---------/---- test5-----------/--- 5.jpg-----


But the tabs are not working as expected.I have attached the results which i got it after running the query.
Attached File  tabs_display.gif   32.3KB   3 downloads

Thanks for your time.
Sam.

#11 PFMaBiSmAd

PFMaBiSmAd
  • Staff Alumni
  • Advanced Member
  • 16,767 posts
  • LocationColorado, U.S.A.

Posted 17 January 2013 - 01:51 PM

I edited my untested, example, code about 10 minutes after it was first posted. Did you use the original code or the 10 minute later code?
Signature: (not a comment about anything you posted unless specifically indicated)
Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

Programming is just problem solving, but it is done in another language. You must learn enough of the programming language you are using to be able to read and write code.

#12 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 17 January 2013 - 02:04 PM

I tried the edited code and i am getting the result like this.Please see the attachment.
Tab is showing correct category, but corresponding category content is not displaying.

Attached Files



#13 PFMaBiSmAd

PFMaBiSmAd
  • Staff Alumni
  • Advanced Member
  • 16,767 posts
  • LocationColorado, U.S.A.

Posted 17 January 2013 - 02:36 PM

Is the HTML that is being output correct? Have you set the $images_dir variable so that it points to where your images are?
Signature: (not a comment about anything you posted unless specifically indicated)
Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

Programming is just problem solving, but it is done in another language. You must learn enough of the programming language you are using to be able to read and write code.

#14 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 18 January 2013 - 04:56 AM

In the HTML output the tabs is displaying correctly as tabs1->Test1(category1) and tab2->Test2(category2). But the problem is with the content of each category.(please check the screenshot which i have added in my last reply)

Test1(category1) should display the content photo_caption test1,test2,test3,test4 with images 1.jpg,2.jpg,3.jpg,4.jpg and Test2(category2) should display the content photo_caption test5 with images 5.jpg (please check the database result after running the query which i have added earlier.)

But now what i am getting is that, Test1(category1) displays the result as the content photo_caption test1,test2,test3,test4 with images 1.jpg,2.jpg,3.jpg,4.jpg and Test2(category2) displays the result as the content photo_caption 1.jpg,2.jpg,3.jpg,test5 with images test1,test2,test3,5.jpg. Here in Test2(category2) it should display the the content photo_caption test5 with images 5.jpg only.

I have not yet set the correct $images_dir variable since i am still working on the code to get it working.

Thanks for your help and time.

Sam.

#15 PFMaBiSmAd

PFMaBiSmAd
  • Staff Alumni
  • Advanced Member
  • 16,767 posts
  • LocationColorado, U.S.A.

Posted 18 January 2013 - 08:17 AM

I didn't ask what was being displayed, I asked if the HTML being output was correct.

I used your implied data in post #10, fixing the incorrect category_name values, and get the expected HTML (\n added after the end of tags to format the output) -
<ul class="tabs">
<li><a href='#' rel='view1'>Test1</a></li>
<li><a href='#' rel='view2'>Test2</a></li>
</ul>

<div class="tabcontents">
<div id='view1' class='tabcontent'>
test1</br><img src='/tb_1.jpg' border='0'>
</div>
<div id='view1' class='tabcontent'>
test2</br><img src='/tb_2.jpg' border='0'>
</div>
<div id='view1' class='tabcontent'>
test3</br><img src='/tb_3.jpg' border='0'>
</div>
<div id='view1' class='tabcontent'>
test4</br><img src='/tb_4.jpg' border='0'>
</div>
<div id='view2' class='tabcontent'>
test5</br><img src='/tb_5.jpg' border='0'>
</div>
</div>

Therefore, either your data is incorrect, you altered the code and it no longer works, or something else being output on the page is messing up the display of the data.

Edit: I did just notice that the <br /> tag you have in your original code is incorrect, though I don't think it is the cause of the wrong output. </br> should be <br> or <br />

Edited by PFMaBiSmAd, 18 January 2013 - 08:19 AM.

Signature: (not a comment about anything you posted unless specifically indicated)
Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

Programming is just problem solving, but it is done in another language. You must learn enough of the programming language you are using to be able to read and write code.

#16 Christian F.

Christian F.
  • Staff Alumni
  • Advanced Member
  • 3,106 posts
  • LocationNorway

Posted 18 January 2013 - 11:39 AM

... i got the following result.

category_id ---/--- category_name ---/--- photo_id ---/--- photo_caption ---/--- photo_filename
---1----------/---- Test1------------/---- 1---------/---- test1-----------/--- 1.jpg-----

---1----------/---- Test2------------/---- 2---------/---- test2-----------/--- 2.jpg-----

---1----------/---- Test3------------/---- 3---------/---- test3-----------/--- 3.jpg-----

---1----------/---- Test4------------/---- 4---------/---- test4-----------/--- 4.jpg-----

---2----------/---- Test5------------/---- 5---------/---- test5-----------/--- 5.jpg-----


This result actually demonstrates that you have a problem with your data, which I reckon stems from the code that inserts new pictures into the database.
A part of the problem is that you have one category per picture, even though they share the same ID. This is just a symptom of the core issue, which is that your database logic is flawed in how you've set up the relation. What you have here is a one-to-many relation, between images and categories. Meaning, one image can have many category, but a category can only have one image. Quite the opposite of what you seem to be wanting, based upon your example pictures.
Though, coming back to the first part of the problem, or rather the sharing of IDs. This tells me that you're not using a PRIMARY KEY or UNIQUE index for the category indices, allowing you to build something that acts like a many-to-many relationship instead. Only, it repeats data and thus violates first normal form (and gives rise to the problem with incorrect category names).

So, what you need to do to fix this issue, is to redesign your database a bit. If you want a one-to-many relationship between the categories and pictures, then you need to make the category ID an AUTO_INCREMENT and PRIMARY KEY. Then add a FOREIGN KEY relationship to this in the photos table. You also need to rewrite the query, to SELECT from the photo table, and JOIN the category table. A rather minor change to the query.
The other option, is if you want a proper many-to-many relationship. You still need to change the category table as above, to remove all data directly related to the pictures themselves and set up a proper PRIMARY KEY. Once that is done, however, you need to create a new table, to hold the associations between the photo and the category table. An internet search will give you lots of detailed tutorials on how to do this, if you need more information.
Keeping it simple.

#17 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 18 January 2013 - 12:26 PM

Hi Christian,

I wanted to make one-to-many relationship between the categories and pictures, so i altered my tables as follows.

ALTER TABLE photos
ADD FOREIGN KEY (category_id)
REFERENCES gallery_category(category_id)

Now the table structure is like this.

CREATE TABLE IF NOT EXISTS `gallery_category` (
`category_id` int(11) NOT NULL auto_increment,
`category_name` text NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `photos` (
`photo_id` int(20) NOT NULL auto_increment,
`photo_filename` varchar(25) NOT NULL,
`photo_caption` text NOT NULL,
`category_id` int(20) NOT NULL,
PRIMARY KEY (`photo_id`),
KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=56 ;

so now category_ID is AUTO_INCREMENT and PRIMARY KEY in gallery_category table and category ID is the FOREIGN KEY in photo table.

Please check it and let me know this is correct..

Thanks,
Sam.

#18 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 18 January 2013 - 12:50 PM

Hi PFMaBiSmAd,

I didnot change anything on your code. Just copied and posted on my php page.The html output i checked and i also got the way as yours. But not getting the expected result. Is it due to my tabbed content script error..?

Thanks,
Sara.

#19 Christian F.

Christian F.
  • Staff Alumni
  • Advanced Member
  • 3,106 posts
  • LocationNorway

Posted 18 January 2013 - 01:18 PM

Besides the (11) and (20) behind the int types, it looks good.

Adding anything more than a display width of 11 for an int field doesn't do anything, as it can only contain 11 digits as the most. In addition to this, setting the display width is only useful for ZEROFILL columns. So I recommend removing them, though not that important; Won't affect the code or data, just wasteful.
Keeping it simple.

#20 sam123

sam123
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 18 January 2013 - 01:35 PM

Thanks Christian. I changed int(20) to int(11).

Also i changed the query as follows

$query = "SELECT gc.category_id, gc.category_name, p.photo_id, p.photo_caption, p.photo_filename
	 FROM gallery_category AS gc
	 INNER JOIN photos AS p ON p.category_id = gc.category_id
	 ORDER BY gc.category_id ASC , p.photo_id DESC";



But still getting the wrong display result. Is there any simple way i can do it..?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users