Multiple options into a mysql table

[johnsimpson]

Hi I am struggling a little with the best way to do this and its quite hard to explain, Im sure there is a simple answer tho...

I am doing a portfolio website and making it all cms driven, the problem I am stuck on is that I have multiple categories which jobs could fall under. i.e. a project could include web design, graphic design, branding etc...

I want to add the categories into their own table and then link this table with the main table via a parent id or something like... but my problem is that I may want to choose several of the categories, so how could I add multiple options into my main table?

So for example I will have 2 tables

1. tbl_categories:
id | category
------------------
1 | Web
2 | Print

2. tbl_portfolio
id | Project | Image | Description | Category
----------------------------------------------------
1 | Test Project | img.gif | description | 1

So... I want to add more than one value into the category field of tbl_portfolio as the project may include web and print


How could I do this in a way which I could then pull the values back out to filter some results from (i.e. just show projects which include web)

I hope this makes sense, please let me know if you need more information

thanks in advance
J

 

[jpadie]

two approaches. one is 'best practice' the other is a cludge.

cludge approach

store each associated category id in a comma delimited string inside the category column. remember you would then need to search on the <<categoryID[red],[/red]>> rather than just the category ID (as otherwise a search on 1 would bring up 11, 21 etc.

best practice

create a join table

create table if not exists portfolio_category_join (
 joinID int(10) not null auto_increment primary key,
 portfolioID int(10) not null,
 categoryID int(10) not null )

then to bring up all portfolios in a category your query would look something like this

/**
 * function to return an array of portfolios.
 * if only certain categories are wanted submit an array of desired categories as the function argument
 * 
 * @param object $categoryID [optional]
 * @return 
 */
function getPortfolios($categoryID=array()){
	//construct the basic query
	$sql = "
			SELECT
				p.*, c.category 
			FROM tbl_portfolio p 
			JOIN portfolio_category_join pcj, tbl_categories c
				ON (pcj.portfolioID = p.id and p.id = c.id) ";
	
	//if a filter has been applied to the categories that are to be returned
	//construct a where clause for that filter
	if (count($categoryID) > 0){
		$v= array();
		foreach ($categoryID as $c){
			$v[] = (int) mysql_real_escape_string($c);
		}
		$sql .= "
			WHERE 
				c.id IN (" . implode (',', $v) .")";
	}
	//execute the query to obtain a resultset
	$results = mysql_query($sql) or die (mysql_error());
	
	//instantiate a holding variable
	$data = array();
	//iterate the recordset and return the data
	while ($row = mysql_fetch_assoc($results)){
		$data[] = $row;
	}
	return $data;
}

you would then drop the category column from your job table.

to save the portfolio data you would do this

/**
* function to update the Portfolio table
*
* @param object $project
* @param object $image
* @param object $description
* @param object $portFolioID
* @return
*/
function updatePortfolio($project, $image, $description, $portFolioID){
$sql = "
UPDATE tbl_portfolio
SET
Project = '%s',
Image = '%s',
Description = '%s'

WHERE
id = %d ";

//replace placeholders with escaped data
$query = vsprintf($sql, array_map('mysql_real_escape_string', func_get_args()));
//execute query
mysql_query($query) or die (mysql_error());

//update the join table
//assuming categories are received from a POSTED form in an array called categories
updateJoinTable($portfolioID, $_POST['categories']);
}

/**
* function to insert a new portfolio entry into the database
* @param object $project
* @param object $image
* @param object $description
* @return
*/
function insertPortfolio( $project, $image, $description){

//Construct insert query
$sql = "
INSERT into tbl_portfolio
(id, Project, Image, Description)
values
(null, '%s', '%s', '%s')";

//replace placeholders with properly escaped data
$query = vsprintf($sql, array_map('mysql_real_escape_string', func_get_args()));

//execute query
mysql_query($query) or die (mysql_error());

//get the portfolio ID
$portFolioID = mysql_insert_id();

//update the join table
//assuming categories are received from a POSTED form in an array called categories
updateJoinTable($portfolioID, $_POST['categories']);
}

/**
* function to update the portfolios join table
*
* this can be used for updates and inserts to the portfolio table
*
* @param object $portfolioID
* @param object $catArray
* @return void
*/
function updateJoinTable ($portfolioID, $catArray){
//cleanse the portfolioID
$portfolioID = (int) mysql_real_escape_string($portfolioID);

//delete all existing join rows
mysql_query("delete from portfolio_category_join where portfolioID=$portfolioID") or die (mysql_error());

//construct the values for the insert query
foreach ($catArray as $c){
$values[] ="(null, $portFolioID, ". (int) mysql_real_escape_string($c) .")";
}

//construct the whole query
$query = "Insert into portfolio_category_join (joinID, portfolioID, categoryID) values " . implode (',', $values);

mysql_query($query) or die (mysql_error());
}

post back if anything is not clear. there are bound to be parse errors in the

 

[johnsimpson]

hi jpadie,

many thanks for this really appreciate it!

I will try both but I think the cludge method prob easier for me... problem is I am using CodeIgniter with Rapyd and direct scripting is not easy, If I can do the propper method then I will but I assume its easier for the cludge way for now... there wont be many categories being added and so should cause many problems... how do I run a search on a table which has comma seperated values in it tho? for example if I had the values 1, 2, 3 stored in the db then how would I run a query to pull results with the value 2 & 3?

thanks for your help

John

 

[jpadie]

it would be easier for the cludge if I had not given you the code functions you need to make it work.

on the cludge you would have to do this (in answer to your final question)

select * from tbl_portfolios where category in ('2,','3,');

you will curse yourself for going with a cludge method in the long term.

 

[johnsimpson]

ok I will try the propper way and see how I get on... i understand it is a much better way to do this the propper way especially seeing as you have been so kind to give me the functions etc but CI/Rapyd is a funny system which isnt easy to implement direct code / functions as it uses its own methods as well, I will try my best to integrate the propper way as you have explained above though.

I really appreciate your help on this

many thanks
John