Assigning Articles to Multiple Categories

[topianno]

I have a small CMS for Patient Testimonials.  I am trying to modify the system so as to assign multiple patient testimonials to multiple categories in the database. 

 

For example: A patient may fall under /women

 

But they may fall under other categories as well. 

 

For Example:

/century member

/platinum member

/gold Member

 

I have a PHP forum I am using to add and edit listings.  However, whenever I select more than one category in the list, the database only saves one.  I want to modify the system so that multiple categories may be selected.

 

I have listed a copy of my PHP form below:

 

Any help would be greatly appreciated!

 

Best,

-Gabe

 

<FORM METHOD="post" ACTION="<?=$_SERVER['PHP_SELF']?>" enctype="multipart/form-data">
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" width="100%">
   <tr>
      <td valign="top" width="470">
<strong>Testimonials Details</strong>
<input type="Hidden" name="Details[cCategoryID]" value="<?=$NewsParrentCategoryID?>">
<input type="Hidden" name="Details[cReleaseDate]" value="01/01/2007">
<TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" width="480">
   <TR>
      <TD CLASS="tableheadder" colspan="4"></TD>
   </TR>
   <TR>
       <TD COLSPAN="2" <? if (in_array("cName", $error)) echo "class=\"error\"";?>>Name<BR><INPUT TYPE="TEXT" NAME="Details[cName]" SIZE="50" style="width:510px;" MAXLENGTH="255" VALUE="<?=$Details['cName']?>"></TD>
   </TR>
   <TR>
   <td>Weight/Sizes/Inches Lost <BR><INPUT TYPE="TEXT" NAME="Details[cSubTitle]" SIZE="50" style="width:250px;" MAXLENGTH="255" VALUE="<?=$Details[cSubTitle]?>"></TD>
<td valign="top">Image <? if ( file_exists($_SERVER['DOCUMENT_ROOT'].$imageFile)) echo "<a href=\"$imageFile\" target=\"_blank\" CLASS=\"std\">preview current</a>";?><BR><input NAME="image" TYPE="file" style="width:250px;"></td>
   </TR>
   <TR>
     <td>Category<BR>
         
            <div class="form">

         <select name="Details[cCategoryID]" size="9" multiple="multiple" style="width:250px;">


   <? foreach ($Categories[$NewsParrentCategoryID ] as $CategoryID => $CatDetails){
            echo "<option value=\"$CategoryID\"";
             if ($CategoryID == $Details[cCategoryID]) echo " SELECTED";
            echo "> ".$CatDetails["cName"]."</option>\n" ;         
            
      } ?>
       
      </select>

        </TD>
     <td valign="top"> </td>
     </TR>
   <tr valign="top">
   <td colspan="2">Quote<BR><INPUT TYPE="TEXT" NAME="Details[cImageCaption]" SIZE="50" style="width:510px;" MAXLENGTH="255" VALUE="<?=$Details[cImageCaption]?>"></TD>
   </tr>
   <TR>
    <TD COLSPAN="2" <? if (in_array("cBody", $error)) echo "class=\"error\"";?>>Testimonial<BR>
    <? $FCKeditor = new FCKeditor('Details[cBody]', 510, 300, false) ;
    $FCKeditor->Value      = $Details['cBody'] ; $FCKeditor->Create() ;?>
    </td>
    <INPUT TYPE="Hidden" NAME="Details[ContentID]" VALUE="<?=$Details['ContentID']?>">
    </tr>
   <TR>
    <TD align="right" colspan="2"><INPUT TYPE="submit" NAME="action" VALUE="Submit">  <INPUT TYPE="submit" NAME="action" VALUE="Delete" onclick="javascript: return confirm('Are you sure you want to DELETE this Testimonial ?');"></td>
   </TR>
</TABLE>
</FORM>

[joel24]

create a relationship table in your database to create the one to many relationship,

i.e.

persons_categories

which would have person_id (foreign key) and then another column with category_id (foreign key) and you'd have a unique key (combination of category_id and person_id) so that the row was never duplicated

and then the person could be listed multiple times, once for each category they are in.

 

and then to update those relationships, or create a new relationship, you'd use something like

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

to avoid creating duplicate rows etc

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

[topianno]

Hey Joel,

I don't understand. I am new to one to many relationships in PHP.

 

This is my current database structure:

CategoryID

cParentID

cName

cOrder

cDesc

cImageCaption

cUserLastMod

cDateLastMod

 

There are a total of 10 categories.  Currently only one testimonials is able to be posted to one category.  I want the system to be able to post to multiple categories.

 

I've gotten everything to work, but I believe my problem lies with my form code.  The form will allow me to select multiple categories.  However, the system will not save the information once its been posted.  It reverts back to assigning the testimonials to only one category.

 

Here is the code I am referring to in my form below:

<? foreach ($Categories[$NewsParrentCategoryID ] as $CategoryID => $CatDetails){
			echo "<option value=\"$CategoryID\"";
			 if ($CategoryID == $Details[cCategoryID]) echo " SELECTED";
			echo "> ".$CatDetails["cName"]."</option>\n" ;



	} ?>

[joel24]

well in short, you have to have a third table.

table 1: categories

table 2: patients

 

table 3: patient_categories

then you can have a list or checkboxes etc in the form, and send it as an ARRAY, i.e. make the checkboxes all with the same name with [] after it. i.e.

<input type='checkbox' name="patient_categories[]">category name</input>

then have a php loop to get all the categories from the database and echo them.

 

then when the checkboxes are posted in the form, you can get the array like

$_POST['patient_categories'] will be an array

so you can use

 foreach ($_POST['patient_categories'] AS $pc)

 

 

you might be best googling one to many relationships mysql etc, this explanation is a bit brief and it will help a lot if you get a comprehensive understanding of it

 

[ignace]

@joel note that patients_categories is a many-to-many relationship (not one-to-many) meaning that:

 

a category has many patients AND

a patient is in many categories

 

PS <input type='checkbox' name="patient_categories[]">category name</input> is invalid and should be <input type='checkbox' name="patient_categories[]" value="category name">

 

This is of course also incorrect because: for which patient? And should therefor be:

 

<label for="patient_<?php print $patient_id; ?>"><?php print $category_name; ?></label>

<input type='checkbox' id="patient_<?php print $patient_id; ?>" name="patients_categories[<?php print $patient_id; ?>]" value="<?php print $category_id; ?>">

 

Note: input.id and label are optional I have only added them to comply with usability-guidelines

[topianno]

Hey Guys,

I am still having trouble getting this to work.  I believe I have my categories setup properly for the array - I think?  Below is a screen shot of my PHP MY ADMIN Panel with my category structure.

 

Also, I have included the code for my entire form.  This form is used not only to submit new testimonials from patients but also to update them when need be.

 

<? require($_SERVER['DOCUMENT_ROOT'] . "/inc/adminFunctions.php");
require($_SERVER['DOCUMENT_ROOT'] . "/inc/categories.class.php");
require($_SERVER['DOCUMENT_ROOT'] . "/inc/content.class.php");
require($_SERVER['DOCUMENT_ROOT'] . "/inc/FCKeditor/fckeditor.php");
checkAccess("Testimonials");
$NewsParrentCategoryID = 2;

$Categories = new Categories; $Categories = $Categories -> getChildern($NewsParrentCategoryID);
$Media = new Content; 

$error[]="";
if ($_POST['action'] == "Delete") $Media -> deleteArchive($_POST['Details']['ContentID'], "/sa/testimonials/");
if ($_POST['action'] == "Submit")	$error = $Media -> addUpdate($_POST['Details'], "/sa/testimonials/");
$Details = $_POST['Details'];

if ($_GET['action'] == "DeleteFile")	unlink ($_SERVER['DOCUMENT_ROOT'] .$_GET['FileID']);


if ($_GET['ContentID'] != "" ) $Details = $Media -> getDetails($_GET['ContentID']);
if ($Details['cReleaseDate'] == "" ) $Details['cReleaseDate'] = date('m/d/Y');


$imageFile = "/images/media/".$Details['ContentID'].".jpg";

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<titleTestimonials Editor</title>

<link rel="STYLESHEET" type="text/css" href="/style.css">
<link rel="STYLESHEET" type="text/css" href="/sa/admin.css">
</head>
<body>

<? include($_SERVER['DOCUMENT_ROOT'] . "/sa/header.php")?>
<? $links = array( "Browse" =>"index.php");
$title = ($Details['ContentID'] == "")? "Add Testimonial": "Update '". $Details['cName']."'"; 
printHeader($title, $links);	?>


<br>
<FORM METHOD="post" ACTION="<?=$_SERVER['PHP_SELF']?>" enctype="multipart/form-data">
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" width="100%">
<tr>
	<td valign="top" width="470">
<strong>Testimonial Details</strong>
<input type="Hidden" name="Details[cCategoryID]" value="<?=$NewsParrentCategoryID?>">
<input type="Hidden" name="Details[cReleaseDate]" value="01/01/2007">
<TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" width="480">
<TR> 
	<TD CLASS="tableheadder" colspan="4"></TD> 
</TR> 
<TR>
	 <TD COLSPAN="2" <? if (in_array("cName", $error)) echo "class=\"error\"";?>>Name<BR><INPUT TYPE="TEXT" NAME="Details[cName]" SIZE="50" style="width:510px;" MAXLENGTH="255" VALUE="<?=$Details['cName']?>"></TD>
</TR> 
<TR>
<td>Weight/Sizes/Inches Lost <BR><INPUT TYPE="TEXT" NAME="Details[cSubTitle]" SIZE="50" style="width:250px;" MAXLENGTH="255" VALUE="<?=$Details[cSubTitle]?>"></TD>
<td valign="top">Image <? if ( file_exists($_SERVER['DOCUMENT_ROOT'].$imageFile)) echo "<a href=\"$imageFile\" target=\"_blank\" CLASS=\"std\">preview current</a>";?><BR><input NAME="image" TYPE="file" style="width:250px;"></td>
</TR>
<TR>
  <td>Category<BR>

            <div class="form">

		<select name="Details[cCategoryID]" size="9" multiple="multiple" style="width:250px;">

<? foreach ($Categories[$NewsParrentCategoryID ] as $CategoryID => $CatDetails){
			echo "<option value=\"$CategoryID\"";
			 if ($CategoryID == $Details[cCategoryID]) echo " SELECTED";
			echo "> ".$CatDetails["cName"]."</option>\n" ;

	} ?>
        
        
	</select>
        
</TD>
  <td valign="top"> </td>
  </TR> 
<tr valign="top">
<td colspan="2">Quote<BR><INPUT TYPE="TEXT" NAME="Details[cImageCaption]" SIZE="50" style="width:510px;" MAXLENGTH="255" VALUE="<?=$Details[cImageCaption]?>"></TD>
</tr>
<TR>
 <TD COLSPAN="2" <? if (in_array("cBody", $error)) echo "class=\"error\"";?>>Testimonial<BR>
 <? $FCKeditor = new FCKeditor('Details[cBody]', 510, 300, false) ;
 $FCKeditor->Value		= $Details['cBody'] ; $FCKeditor->Create() ;?>
 </td>
 <INPUT TYPE="Hidden" NAME="Details[ContentID]" VALUE="<?=$Details['ContentID']?>">
    </tr>
<TR>
 <TD align="right" colspan="2"><INPUT TYPE="submit" NAME="action" VALUE="Submit">  <INPUT TYPE="submit" NAME="action" VALUE="Delete" onclick="javascript: return confirm('Are you sure you want to DELETE this Testimonial ?');"></td>
</TR>
</TABLE>
</FORM>
<? include($_SERVER['DOCUMENT_ROOT'] . "/sa/footer.php")?>

 

 

Any help would be greatly appreciated!  Thanks!

 

 

[attachment deleted by admin]