Photo website PHP/MYSQL help

[lbaxterl]

Hi, im currently building a photography portfolio website for ym dissertation and i have a quick question.

 

The site has several categories of photos, would it be easier storing all in photos in one table and adding a album and field or storing each separate category,of photos,  into its own table? I have a script which gets the photos from the database which is below, if its easier storing all the photos in one table how would i go about modifying this script to select the photos with a specific category. I hope that makes sense. thanks

 

<?php

require "common.php";

$q = "SELECT id, title, source FROM sports";

$result = $mysqli->query($q) or die(mysql_error($mysqli));  
   
if ($result) {  

      
while ($row = $result->fetch_object()) {  

	$title = $row->title;
	$source = $row->source;
	$id = $row->id;

	echo "<a class='gallery' rel='usa' id='$id' alt='$title' href='img/sports/$source'>  <img src='img/sports_tn/$source'  />   </a> \n";
	}		
}
?>

[kickstart]

Hi

 

It would be far better to just have a single table. For a start makes things FAR easier when you want to add a new category of photos.

 

Personally I would set up a table of categories:-

 

CategoryId = Int

CategoryDescription = VarChar

 

Then have your table of pics and add a column for the CategoryId.

 

You can use the table of catergories to provide a drop down listing for a user to select, and return the category, then use that in your script.

 

Something like this:-

 

<?php

require "common.php";

if (is_numeric($_POST['CategoryId']))
{

$CategoryId = $_POST['CategoryId'];

$q = "SELECT id, title, source FROM pics WHERE CategoryId = $CategoryId ";

$result = $mysqli->query($q) or die(mysql_error($mysqli));  
   
if ($result) 
{  
	while ($row = $result->fetch_object()) 
	{  
		$title = $row->title;
		$source = $row->source;
		$id = $row->id;

		echo "<a class='gallery' rel='usa' id='$id' alt='$title' href='img/sports/$source'>  <img src='img/sports_tn/$source'  />   </a> \n";
	}		
}
}
else
{

$CategoryId = $_POST['CategoryId'];

$q = "SELECT CategoryId, CategoryDescription FROM Categories ";

$result = $mysqli->query($q) or die(mysql_error($mysqli));  
   
if ($result) 
{  
	echo "<form method='post' >";
	echo "<select name='CategoryId' >";
	while ($row = $result->fetch_object()) 
	{  
		$CategoryId = $row->CategoryId;
		$CategoryDescription = $row->CategoryDescription;
		echo "<option value='$CategoryId'>$CategoryDescription</option>";
	}		
	echo "</select>";
	echo "<input type='submit' name='Submit' value='Submit' />";
	echo "</form>";
}
}
?>

 

All the best

 

Keith