how to add/remove three level keywords using mysql and php?

Question

i want to build a three level keywords system for every entry. As suggested i've created one table (ctypes) for categories' information and one (categories) for the relation table with entry.

CREATE TABLE ctypes (
cat_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(20) NOT NULL,
cat_level INT UNSIGNED NOT NULL,
parent_id INT UNSIGNED,
PRIMARY KEY (cat_id),
UNIQUE (cat_name)
);

CREATE TABLE categories (
entry_id INT UNSIGNED NOT NULL,
cat_ids VARCHAR(100) NOT NULL,
UNIQUE (entry_id)
);

then i've already build a form to collect the keywords information by checkboxs.

<form action="add_category.php" method="post">
<table>
<tr>
    <td><b>Level 1(A,B,C)</b></td>
    <td><b>Level 2(Aa,Ab,Ac)</b></td>
    <td><b>Level 3(Aa1,Aa2,Aa3)</b></td>
</tr>
<tr>
    <td>A</td>
    <td>Aa</td>
    <td><input type="checkbox" name="cat[]" value="5" />Aa1<td>
</tr>
<tr>
    <td></td>
    <td></td>
    <td><input type="checkbox" name="cat[]" value="6" />Aa2</td>
</tr>
<tr>
    <td></td>
    <td>Ab</td>
    <td><input type="checkbox" name="cat[]" value="7" />Ab1</td>
</tr>
...
</table>
<input type="hidden" name="eid" value="1" />
<input type="submit" name="submit" value="submit" />
</form>

And i've create a relation table for the entry_id and cat_id, how to save the $cat[] data to the table?

Then, how to handle this edit(add/remove) the keywords for every entry, and how to search the entry by (1st,2nd,3rd keywords)?

Answer 1

I'm not entirely sure what you're trying to achieve. As has been pointed out, your table structure doesn't look ideal, but without knowing your exact end goal it's really hard to assist you and advise.

I think the main mystery is why you have three levels in each category, but only one checkbox for one level.

ie you have this:

<tr>
    <td>A</td>
    <td>Aa</td>
    <td><input type="checkbox" name="cat[]" value="5" />Aa1<td>
</tr>

However, why can't I select "A" or "Aa"?

Maybe this is what you intend, or maybe (as per many of your comments) you're just trying to show a basic example of what you are trying to achieve and will change you code as necessary once you have the answer.

Also, your solution wont work as you need to create an array to insert into mysql, you're just appending to a variable, which will insert one row with the entire array data as one value.

To help you with the inputs you could do this:

//Initialise some things so can work with them (and no errors)
$arySqlQry = array();
$strError = FALSE;

//make sure you have posted data first
if (isset($_POST['cat']) && is_array($_POST['cat']))
  {

    foreach ($_POST['cat'] as $strCatKey => $strCatValue)
      {

        //ensure data is what you want, no SQL injection etc
        if ($strCatValue != 'Aa1'
            && $strCatValue != 'Aa2'
            && $strCatValue != 'Ab1')
          {
            $strError = TRUE;
          }
        else
          {
            //clean data and construct sql values ready for insert
            $strCatValue = mysql_real_escape_string($strCatValue);             
            $arySqlQry[] = "('".$eid."', '".$strCatValue."')";
          }

        //if we have an error in the array
        //stop the loop as further looping is pointless
        if ($strError == TRUE)
          {
            break;
          }


      }//end foreach


    //if we have a new array with results, and no errors
    //insert into DB
    //if not, this wont run and the error catch below will work
    if (!empty($arySqlQry) && $strError != TRUE)
      {
        $qryQuery = '"INSERT INTO categories (entry_id, cat_ids) 
                      VALUES '.implode(',', $arySqlQry).'"';
        $qryExecute = mysqli_query($dbc, $qryQuery);
      }


  } //end if $_POST is set and is array
else //(POST not there so is error)
  {
    $strError = TRUE;
  }

//if there are any errors
if ($strError == TRUE)
  {
    //do some error reporting
    //ie tell user nothing entered in form, and therefore nothing was saved
  }

You can see this working by commenting out the line with the $qryExecute and echoing the var $qryQuery, which will show you the constructed mysql query string and what it will insert into the database.

As for getting the data back, this is a simple select, using a while or grab an array and foreach etc.

As has been mentioned, but I feel it necessary again, the mysql_ extensions are depreciated as of PHP 5.5.0, and you should consider using PDO or mysqli, with prepared statements for extra manageability and security.

Answer 2

how to save the form data to the database table

$cats=''; 

foreach($_POST["cat"] as $key)  {
    $cats.=$key.',';
}
$q="insert into categories (entry_id, cat_ids) values ('$eid','$cats')";
$r = @mysqli_query($dbc, $q); // Run the query.

how to read data from database

$q = "select cat_ids from categories WHERE entry_id=$eid";    
$r = @mysqli_query ($dbc, $q);

if (mysqli_num_rows($r) == 1) { 
    $row = mysqli_fetch_array ($r, MYSQLI_NUM);
    $cats = trim($row[0],',');
    $cat = explode(",",$cats);
    echo '<p>';
    foreach($cat as $key)  {
        $q = "select cat_name from ctypes where cat_id=$key";    
        $r = @mysqli_query ($dbc, $q);
        $row = mysqli_fetch_array ($r, MYSQLI_NUM);
        echo "<b>".$row[0].";</b>";               
    }
    echo '</p>';
}