Categorical Articles

Beebs · May 19th, 2006, 01:04 AM

Honestly I am very confused the way this P2P Forum - cause I don't got repply from my post , maybe I am double post, please for give me.

I want to devekop chapter 13 further, and I need clue/help/advise from the authors of this great book.

Question : How to create categorical articles?

The sample code in the book just show how to create general article. In the real world website, the articles always belong to category/section.

How can I start doing the process?

Beebs · May 24th, 2006, 08:53 AM

Great book, but very appaling support - I notice a lot of post have 0 repplies (include my post). So watchout for newbie who bought the book looking for help.

harpua · May 24th, 2006, 11:12 AM

Well I think generally with this forum people will help more if there is actual code, I have been using this forum for years and have learned alot from everybody.

Anyway, this is really just a heirarchy and you can create it as deep as you want.
Category
    Sub Category
       Sub Category
            Article

What I usually do is basically like this. Create a table to hold my categories. Then when you edit the article itself create another field in the form and article table for the categories. Now you will have a link between the categories and the articles.

Then on your main page when the user selects the category they wish to read about. The articles pertaining to that category are selected through the category field on the article table.

peace
mike

Beebs · May 25th, 2006, 06:06 PM

harpua,

Thanks for your repply.
As much as the general idea that you give in your repply, yes I knew that, but thank you anyway - at least someone repply my post.

As much as the actual code that you said, I specifically said, the code from chapter13 - it's hell lot of code to attach/post here. And if anyone wants it's available here in this site.

I'm not being sour or cynical, but truth is, a repply of a post always given after someone make a fuss.

As a noobie, I'd like to emphasize again: it is really great book but I have to be carefull if I want to take further of what's been given through the book, because there's no help at all.

Peace!

Beebs · May 25th, 2006, 06:16 PM

There's no choice to edit the post so I have to add my post here.

If you are asking about the categorical/hierarchial code, well that's surely my question so I can't come up with the code.

But enough with rants. If you like to help or comments me here's the SQL that I did.

Code:

CREATE TABLE IF NOT EXISTS cms_section (
  section_id int(4) unsigned NOT NULL auto_increment COMMENT 'The unique ID of the section',  
  is_published tinyint(1) NOT NULL default '0',
  section_name varchar(20) NULL COMMENT 'The section name',
  section_parent_id int(4) NULL DEFAULT 0 COMMENT 'The ID of the parent section',  
  author_id int(11) NOT NULL default '0',
  PRIMARY KEY (section_id)
)TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS cms_articles (
  article_id int(11) NOT NULL auto_increment,  
  section_id int(4) unsigned NOT NULL default '0' COMMENT 'Relation to section',
  author_id int(11) NOT NULL default '0',
  is_published tinyint(1) NOT NULL default '0',
  date_submitted datetime NOT NULL default '0000-00-00 00:00:00',
  date_published datetime NOT NULL default '0000-00-00 00:00:00',
  title varchar(255) NOT NULL default '',
  body mediumtext NOT NULL,
  PRIMARY KEY (article_id),
  KEY IdxArticle (author_id,date_submitted),
  FULLTEXT KEY IdxText (title,body)
)
  TYPE = MyISAM

Please look at relational field : section_id int(4) unsigned NOT NULL default '0' COMMENT 'Relation to section', between those to table.

Question : Am I in the right track?

harpua · May 31st, 2006, 08:51 PM

Sorry I didn't get back sooner. Yes you are on the right track. Now when the user selects the category all you have to do is search for the related ID.

mike

Beebs · May 31st, 2006, 09:02 PM

mike, thanks for the repply

I have done the database update (creating section) and able to join them to display the result.

However I struggle them to make them appear in the compose.php ( which is going to be used for editing the article as well).

What I want is the section appear as a pull-down choice (a select form) that any new article going to be posted/edited have to chose the section.

Here's the code :

Code:

<?php

require_once 'conn.php';

$article = '';
$title = '';
$section_id = '';
$body = '';
$author_id = '';
if (isset($_GET['a'])
    and $_GET['a'] == 'edit'
    and isset($_GET['article'])
    and $_GET['article']) {
  $sql = "SELECT ar.* ".
          "FROM cms_articles ar " .

         "WHERE article_id=" . $_GET['article'];
  $result = mysql_query($sql, $conn)
    or die('Could not retrieve article data; ' . mysql_error());

  $row = mysql_fetch_array($result);
  $title = $row['title'];
  $section_id = $row['section_id'];
  $body = $row['body'];
  $article = $_GET['article'];
  $authorid = $row['author_id'];
}
require_once 'header.php';
?>

<form method="post" action="transact-article.php">

<h2>Compose Article</h2>

<p>
  Title:<br>
  <input type="text" class="title" name="title" maxlength="255"
    value="<?php echo htmlspecialchars($title); ?>">
</p>


<p>
Section:<br>
<select id="section_name" name="section_name" style="width:150px">
<?php
$section_name = "";
$sql = "SELECT section_id, section_name " .
         "FROM cms_section ORDER BY section_id";
  $result = mysql_query($sql)
    or die("Query Error" .
           mysql_error());
  while ($row = mysql_fetch_array($result)) {
    if ($row['section_id'] == $section_name) {
      $selected = " selected";
    } else {
      $selected = "";
    }
    echo '<option value="' . $row['section_id'] . '"' . 
         $selected.'>' . $row['section_name'] . '</option>' . 
         "\r\n";
  }

?>
</select>






</p>

<p>
  Body:<br>
  <textarea class="body" name="body" rows="10" cols="60"><?php
    echo htmlspecialchars($body); ?></textarea>
</p>
<p>
<?php
echo '<input type="hidden" name="article" value="' .
     $article . "\">\n"; 

if ($_SESSION['access_lvl'] < 2) {
  echo '<input type="hidden" name="authorid" value="' .
       $authorid . "\">\n";
}

if ($article) {
  echo '<input type="submit" class="submit" name="action" ' .
       "value=\"Save Changes\">\n";
} else {
  echo '<input type="submit" class="submit" name="action" ' .
       "value=\"Submit New Article\">\n";
}
?>
</p>
</form>

<?php require_once 'footer.php'; ?>

In that code I manage to run SQL query to pull section data in the pull down menu, but how can I make them having relational result that when any article is published/edited the section_id field in the cms_articles will also be filled/updated?

PS: I don't include the adjusted transac-article.php code here, but if you need please advise me.

Cheers

harpua · May 31st, 2006, 10:25 PM

I hope I am understanding how you are wanting to add the Select section into the form.

<p>
Section:<br>

<select id="section_name" name="section_name" style="width:150px">
//It's a good idea to give this a blank select option or better yet test for results from DB first.
<option value="">Make A Selection</option>

<?php
//You are creating a variable, but never assigning it a value, there will never be a match.
//I am assuming this is your category so you need to have some way to send over the article ID and then retrieve it on the page using most likely using something like a $_POST or $_GET
$section_name = $_GET['article_id'];

$sql = "SELECT section_id, section_name FROM cms_section ORDER BY section_id";

$result = mysql_query($sql)
    or die("Query Error" . mysql_error());

// an cleaner way to write the loop option section would be
while ($row = mysql_fetch_array($result)) {

    echo '<option value="' . $row['section_id'] . '"';
    if ($row['section_id'] == $section_name) {
        echo ' selected';
    }
    echo '>' . $row['section_name'] . '</option>\r\n';
  }
?>
</select>
</p>

Beebs · May 31st, 2006, 10:39 PM

mike,

This : $section_name = $_GET['article_id']; does not do anything . I've tried to use _POST as well...no luck

As I told you in the previous post the form is handled by transact-article.php

I don't know where to change - wether in the SQL query above, or in this transact-article.php

Can you please have a look?

Code:

<?php
session_start();
require_once 'conn.php';
require_once 'http.php';

if (isset($_REQUEST['action'])) {
  switch ($_REQUEST['action']) {
    case 'Submit New Article':
      if (isset($_POST['title'])
          and isset($_POST['section_id'])
          and isset($_POST['body'])
          and isset($_SESSION['user_id']))
      {
        $sql = "INSERT INTO cms_articles " .
               "(title,section_id,body,author_id,date_submitted) " .
               "VALUES ('" . $_POST['title'] . 
               "','" . $_POST['section_id'] . 
               "','" . $_POST['body'] .
               "'," . $_SESSION['user_id'] . ",'" .
               date("Y-m-d H:i:s", time()) . "')";



        mysql_query($sql, $conn)
          or die('Could not submit article; ' . mysql_error());
      }
      redirect('index.php');
      break;

    case 'Edit':
      redirect('compose.php?a=edit&article=' . $_POST['article']);
      break;

    case 'Save Changes':
      if (isset($_POST['title'])
          and isset($_POST['section_id'])
          and isset($_POST['body'])
          and isset($_POST['article']))
      {
        $sql = "UPDATE cms_articles " .
               "SET title='" . $_POST['title'] .
                "', section_id='" . $_POST['section_id'] .
               "', body='" . $_POST['body'] . "', date_submitted='" .
               date("Y-m-d H:i:s", time()) . "' " .
               "WHERE article_id=" . $_POST['article'];
               if (isset($_POST['authorid'])) {
                 $sql .= " AND author_id=" . $_POST['authorid'];
               }

        mysql_query($sql, $conn)
          or die('Could not update article; ' . mysql_error());
      }

      if (isset($_POST['authorid'])) {
        redirect('cpanel.php');
      } else {
        redirect('pending.php');
      }
      break;

    case 'Publish':
      if ($_POST['article']) {
        $sql = "UPDATE cms_articles " .
               "SET is_published=1, date_published='" .
               date("Y-m-d H:i:s",time()) . "' " . 
               "WHERE article_id=" . $_POST['article'];
        mysql_query($sql, $conn)
          or die('Could not publish article; ' . mysql_error());
      }
      redirect('pending.php');
      break;

    case 'Retract':
      if ($_POST['article']) {
        $sql = "UPDATE cms_articles " .
               "SET is_published=0, date_published='' " .
               "WHERE article_id=" . $_POST['article'];
        mysql_query($sql, $conn)
          or die('Could not retract article; ' . mysql_error());
      }
      redirect('pending.php');
      break;

    case 'Delete':
      if ($_POST['article']) {
        $sql = "DELETE FROM cms_articles " .
               "WHERE is_published=0 " . 
               "AND article_id=" . $_POST['article'];
        mysql_query($sql, $conn)
          or die('Could not delete article; ' . mysql_error());
      }
      redirect('pending.php');
      break;

    case 'Submit Comment':
      if (isset($_POST['article'])
          and $_POST['article']
          and isset($_POST['comment'])
          and $_POST['comment'])
      {
        $sql = "INSERT INTO cms_comments " .
               "(article_id,comment_date,comment_user,comment) " .
               "VALUES (" . $_POST['article'] . ",'" .
               date("Y-m-d H:i:s", time()) . 
               "'," . $_SESSION['user_id'] .
               ",'" . $_POST['comment'] . "')";
        mysql_query($sql, $conn)
          or die('Could add comment; ' . mysql_error());
      }
      redirect('viewarticle.php?article=' . $_POST['article']);
      break;

    case 'remove':
      if (isset($_GET['article'])
          and isset($_SESSION['user_id']))
      {
        $sql = "DELETE FROM cms_articles " .
               "WHERE article_id=" . $_GET['article'] . 
               " AND author_id=" . $_SESSION['user_id'];
        mysql_query($sql, $conn)
          or die('Could not remove article; ' . mysql_error());
      }
      redirect('cpanel.php');
      break;
  }
} else {
  redirect('index.php');
}
?>

Beebs · May 31st, 2006, 10:48 PM

mike just to clarify my post above, here's the problem:

I can submit/edit new article but the pull-down selection, although I have selected - during submission does not direct/put the article into category selected.

The article is published but does not belong to any category selected

Thanks a lot for help.