SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 38
  1. #1
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Creating Categories

    Hi all.. I completed a script a few months ago and is still working perfectly to this day, however I would like to take it one step further by being able to create categories and post the news specifying its prefered category.

    I've tried several methods to try and add the news to certain categories but to no avail, would it be possible to take a look at the script and help me out a little.

    Currently I have 2 tables.. news and categories

    PHP Code:
    <?php

    //-------------------connect to and select the database and tables------------------------>
      // includes
      
    include("conf.php");
      
      
    $connection mysql_connect($host$user$pass) or die ("Unable to connect!");
      
      
    mysql_select_db($db) or die ("Unable to select database!");
      
    //-------------------connect to and select the database and tables------------------------>        

      
    if (isset($_POST['submit'])):
      
    $headline $_POST['headline'];
      
    $content $_POST['content'];
      
    $author $_POST['author'];
      
    $categories $_POST['categories'];
      
      
    $sql "INSERT INTO news, categories SET
              headline='
    $headline',
              content='
    $content',
              categories='
    $categories',
              author='
    $author',
              date=CURDATE()"
    ;    
              
      if (@
    mysql_query($sql)) {
        echo(
    "<p><blockquote>News added successfully</blockquote></p>");
      } else {
        echo(
    "<p>Error adding News");
      }

    ?>



    <!-------------------select the caterogies table------------------------->

    <?php
    else: 

      
      
    $cats    mysql_query('SELECT ID, Name FROM Categories');
    ?>
    <!-------------------select the caterogies table------------------------->


     Add News Item
     <form action="<?=$_SERVER['PHP_SELF']?>" method="post">
     
      <BLOCKQUOTE>
        Headline (<em> title of news</em> )<br>
       <input type="text" name="headline" size="53" maxlength="40"/>
          
      <p><br />
        Content (<em> news story</em> )<br>
        <textarea name="content" cols="40" rows="5" wrap="VIRTUAL"></textarea>
      </p>
      <p>Author's Name<br>
        <input type="text" name="author" size="53" maxlength="255"/>
      <br>
            
       Select Category <br>
     <!----------------------diplays the full list of cats--------------------------------------->  
    <?php

      
    while ($cat mysql_fetch_array($cats)) {
        
    $cid $cat['ID'];
        
    $cname htmlspecialchars($cat['Name']);
        echo(
    "<input type='checkbox' name='cats[]' value='$cid' />$cname<br />\n"); 
      }
    ?>
    <!----------------------diplays the full list of cats--------------------------------------->        
            <br>
          <img src="images/spacer.gif" width="100" height="8"> 
            <input type="submit" name="submit" value="Add News Item"/>
          </p>
      </BLOCKQUOTE>
      </form>

    <?php endif; ?>
    Thx for reading and hopefully someone can help me out.

    Best Regards
    Deano

  2. #2
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    Middle / East TN
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would help to know your database structure, but here's a handy version (minus actual SQL statements):

    Table categories
    -------------------
    ID title


    Table news
    ------------------
    ID categoryID title body

    The ID in the news table matches an ID in the categories table. Here's an example insert:

    categories
    ID = 1
    title = 'IMPORTANT UPDATES'

    news
    ID = 1
    categoryID = 1 // Shows it's in important updates cat
    title = 'Server Down!'
    body = 'Massive black hole ... need help!'

    ID = 2
    categoryID = 1
    title = 'Failure!'
    body = 'sys...tem d...own'

    For each news story, just add the same categoryID, create new categories only when you want another one.

    If you want to find new only for one category:
    SELECT * FROM news WHERE categoryID = 1;

    This setup is considered many to one (many news to one category) where one news story can have only one category. I would advise not using checkboxes in the form, maybe pulldown or radio buttons. If you want many to many, that requires another table which I'll explain later.
    There are 10 kinds of people in this world,
    those who can read binary and those who can't.

  3. #3
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Birth Place of Aviation
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I believe I have a solution for you, but I need more info from you: Can you post your database schema for this code. Some of your code looks a little weird to me--i just want to make heads or tails of it.

    After I read your reply--I will send you great solution.

    Port

  4. #4
    SitePoint Wizard edshuck's Avatar
    Join Date
    Jul 2000
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Am a bit lost about the problem you are having.

    My site looks like hell but I am working on that. So no more there. My site is shops and categories. The shop table is extensive but the main search element is the shop_id column.
    The category table is simply a cat_id and cat_name.
    Between the two is a many to many that is just a lookup with three cols lookup_id, shop_id and cat_id. So I can search either way (to or from shop and to or from cat).
    Is the code failing? I am missing the main question. The basis for all the code I have is the original Kevin Yank tutorial.

    Let me know.

  5. #5
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    here's the database structure

    Well the probelm is it fails to add the news story to the newstable and the categories table.

    # Code to create a simple news table with the following columns

    CREATE TABLE news (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    headline TEXT,
    content TEXT,
    author TEXT,
    count TEXT,
    date TIMESTAMP NOT NULL

    );

    # Code to create a simple Categories table

    CREATE TABLE Categories (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255)
    );


    thx for helping me out with this problem all.

    Cheers Deano
    Last edited by Deano; Jul 26, 2003 at 03:27.

  6. #6
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) In your table structure, I don't see any categories column.

    2) I don't believe that's the correct syntax for INSERT

    3) I believe the third portion of your problem lies in the use of a single SQL query to insert into two tables simultaneously. AFAIK, this is not supported -- try splitting into two (and correcting error 1 & 2):

    PHP Code:
    $sql "INSERT INTO news 
              (headline, content, author, date) 
              VALUES(
                 '
    $headline',
                 '
    $content',
                 '
    $author',
                 CURDATE()
              );"

    mysql_query($sql);

    $sql "INSERT INTO categories (name) 
              VALUES('
    $categories');
    mysql_query(
    $sql); 
    Cheers!
    My name is Steve, and I'm a super-villian.

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    Middle / East TN
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You don't need to insert a new category everytime you insert a news story. Add a categoryID column to the news table which would refer to a number in categories. You should already have the categories table filled with several rows. Then just insert information into the news table.
    There are 10 kinds of people in this world,
    those who can read binary and those who can't.

  8. #8
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think your right with the about the sql query, regarding splitting it into 2

    I will give it a try and get back to you with the results.

    Thx for your replies
    Deano :-)

  9. #9
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bonk
    You don't need to insert a new category everytime you insert a news story. Add a categoryID column to the news table which would refer to a number in categories. You should already have the categories table filled with several rows. Then just insert information into the news table.
    Good point, I wasn't really thinking about the actual use of that code -- thanks for catching that!
    My name is Steve, and I'm a super-villian.

  10. #10
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thx that works perfectly.. but how does one display all the news items regarding a particular Category ID

    eample :
    News Category 'updates'

    news item 1
    news item 2
    news item 3

    and so on

    Cheers Deano

  11. #11
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Deano
    thx that works perfectly.. but how does one display all the news items regarding a particular Category ID

    eample :
    News Category 'updates'

    news item 1
    news item 2
    news item 3

    and so on

    Cheers Deano
    First, you will need to translate 'updates' to it's corresponding category id:

    PHP Code:
    $sql "SELECT id FROM categories WHERE name='updates'"
    After performing the usual mysql extraction functions, you now have, say $id = 5; Then just WHERE your news articles:

    PHP Code:
    $sql "SELECT * FROM news WHERE category = $id";
    mysql_query($sql); 
    Cheers!
    My name is Steve, and I'm a super-villian.

  12. #12
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've reverted back to the old way of creating categories. Ive changed a few things around in the tables allowing for an easier way of using categories in the news table.

    However.. Adding a new category to the categories table and echoing the column works fine, but... when i select them to be placed into the news table IE the CID column I created this is where things start to go drastically wrong for me. Ive tried various methods with no joy, here's the script for the news submission and the database layouts.

    I dont understand the reason if the news gets added successfully why doesnt the cid ?

    PHP Code:
    <?php
      
      
    include("conf.php");

      
    $connection mysql_connect($host$user$pass) or die ("Unable to connect!");
     
      
    mysql_select_db($db) or die ("Unable to select database!");
            
      if (isset(
    $_POST['submit'])):
      
    $headline $_POST['headline'];
      
    $content $_POST['content'];
      
    $author $_POST['author'];
      
    $cid $_POST['cid'];
      
    $sql "INSERT INTO kmsnews SET
              headline='
    $headline',
              content='
    $content',
              author='
    $author',
              cid='
    $cid',
              date=CURDATE()"
    ;
      if (@
    mysql_query($sql)) {
        echo(
    '<p>News added successfully</p>');
      } else {
        echo(
    '<p>Error adding News' .
             
    mysql_error() . '</p>');
      }

      else: 
      
    $cats  mysql_query('SELECT ID, name FROM categories'); 
      
    ?>

    <form action="<?=$_SERVER['PHP_SELF']?>" method="post">
        
      <BLOCKQUOTE>
        Headline <br>
          <input type="text" name="headline" size="53" maxlength="40"/>
          <br>
          Content<br>
          <textarea name="content" cols="40" rows="5" wrap="VIRTUAL"></textarea>
          <br>
        Authors name <br>
          <input type="text" name="author" size="53" maxlength="255"/>
        <br>
        
        Select category<br />
      
      <?php 
      
    while ($cat mysql_fetch_array($cats)) { 
      
    $cid $cat['ID']; 
      
    $cname htmlspecialchars($cat['name']); 
      echo(
    "<input type='checkbox' name='cats[]' value='$cid' />$cname<br />\n" ); 
       } 
      
    ?>
          
          <br />
          <input type="submit" name="submit" value="Add News Item"/>
      </BLOCKQUOTE>
      </form>
      
      <?php endif; ?>
    Database tables :
    PHP Code:
    CREATE TABLE kmsnews (
      
    ID int(11NOT NULL auto_increment,
      
    cid varchar(255) default NULL,
      
    headline text,
      
    content text,
      
    author text,
      
    count text,
      
    date timestamp(14NOT NULL,
      
    PRIMARY KEY  (ID)
    TYPE=MyISAM;

    CREATE TABLE categories (
      
    ID int(11NOT NULL auto_increment,
      
    name text,
      
    PRIMARY KEY  (ID)
    TYPE=MyISAM
    I hope someone may be able to help me out with this..

    Best Regards
    Deano
    Last edited by Deano; Jul 27, 2003 at 13:48.

  13. #13
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anyone ?
    Last edited by Deano; Aug 1, 2003 at 14:06.

  14. #14
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im Still no being able to add a news item that displays the cid in the news from the categories I've created.

    Can anyone help me out at all ?

    Thx for your time

    Deano

  15. #15
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Birth Place of Aviation
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your code is good: One simple mistake. Remeber that for the category you are returning an array--not a variable. So in the insert you must determine which index of the array you're going to put in.
    Thus, your insert should be:

    $sql = "INSERT INTO kmsnews SET
    headline='$headline',
    content='$content',
    author='$author',
    cid='".$cid['0']."',
    date=CURDATE()";

    Also, unless you want to put the article under multiple categories, change your checkboxs to radio buttons and remove the the [] (brackets) from cid in the name attribute in the form input. And use your original code, not what I have above and it should work.

    If you want to be able to list one article under multiple categories reply and I'll help you out.

    Port

  16. #16
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thx S001DXP for helping me out :-)

    If I have anymore questions I'll be back

    Thx again for your support

    Best Regards
    Deano

  17. #17
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Birth Place of Aviation
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anytime!

    Also, if you want to be able to add articles to multiple categories, drop a note.

    Port

  18. #18
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still no luck I'm afraid..

    The news gets added nicely.. accept for the cid :-(

    Cheers Deano

  19. #19
    SitePoint Wizard edshuck's Avatar
    Join Date
    Jul 2000
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi Deano,

    I will be back up on the forum for a few days.

    I have a simple page that uses the "category". Take a look at the page at www.noevalley.com .. I am using just a few pages.

    There are some problems with the page but that is mostly some of my cat work being odd.

    I do not have a problem with sharing. Most of it is from the original Kevin Yank - with a few changes.

  20. #20
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thx edshuck..

    I taken out a little of the script also from Mr Yank's book and tryed to implement it into my news script, but for some unkown reason the cid in the news table wont update onece i select a category.

    I'll take a look, thx for your response

    Best Regards
    Deano

  21. #21
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no problem adding news but adding a category ID 'cid' ! well, this is where things start to go wrong for me.

    This is what I'm using now to INSERT the news item :

    PHP Code:
    $sql "INSERT INTO kmsnews SET 
    headline='
    $headline', 
    content='
    $content', 
    author='
    $author', 
    cid='"
    .$cid['0']."', 
    date=CURDATE()"

    And this peice of code I use to pull the categories list form the database:

    PHP Code:

    <?php 
      
    while ($cat mysql_fetch_array($cats)) { 
      
    $cid $cat['ID']; 
      
    $cname htmlspecialchars($cat['name']); 
      echo(
    "<input type='checkbox' name='cats[]' value='$cid' />$cname<br />\n" ); 
       } 
      
    ?>
    Any ideas regarding any of the snippets above ?

    Best Regards Deano

  22. #22
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Birth Place of Aviation
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh I see the problem--the name of the array is cats[], not cid[].

    You code should look like this:

    $sql = "INSERT INTO kmsnews SET
    headline='$headline',
    content='$content',
    author='$author',
    cid='"
    .$cats['0'].
    "',
    date=CURDATE()"
    ;


    Actually, because you are not assigning the news to multiple categories, your code should look like this:

    PHP Code:
    $sql = "INSERT INTO kmsnews SET 
    headline='$headline', 
    content='$content', 
    author='$author', 
    cid='$cats', 
    date=CURDATE()"; 

    //And this peice of code you use to pull the categories list form the database:
    <?php 
      
    while ($cat mysql_fetch_array($cats)) { 
           
    $cid $cat['ID']; 
           
    $cname htmlspecialchars($cat['name']); 
           echo 
    "<input type='radio' name='cats' value='$cid' />$cname<br />\n"

    ?>
    Port
    Last edited by s001dxp; Aug 2, 2003 at 09:16.

  23. #23
    SitePoint Evangelist Deano's Avatar
    Join Date
    Mar 2003
    Location
    Derbyshire, UK
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still the same.. i think im going to cry, ok i am

    Thx
    Sobbing Deano

  24. #24
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Birth Place of Aviation
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Send me a zip file with the files along with a schema of the database--I will get to the bottom of this.

    Port

  25. #25
    SitePoint Wizard edshuck's Avatar
    Join Date
    Jul 2000
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Deano,

    I do not specify a cat_id, rather, looking at the alter table part of the mysql by duBois.

    I use the string

    Add member_id int unsigned not null auto_increment primary key.

    As above, ship the problem with code and I also will work on it.

    I am very sorry about the time elements you have suffered.

    peace


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •