3 Replies - 2623 Views - Last Post: 18 July 2011 - 11:57 PM

#1 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

MYsql + creating Blog + problem in primay key. foriegn key , auto in

Posted 12 July 2011 - 10:48 AM

Hi all,

i have post this problem in database forum and php forum i stil didn't get single reply.
as this problem is about a blog i put the problem here.

please some one help me. i am waiting .
.....................................................
Currently i am developing a blog.

i have some problem in database layer . I use mysql.

here is the description.
My Blog facilitates administrator to put new threads and users to put comment to the threads.
I have planned to put administrators post in thread table where id is the primay key.

I tried to get thread table id as foriegn key in usercomment table where users can put their replies to administrartor thread.
problem is when i use primay key as usercomments id since i am not able to duplicate it.

it is obvious we want to give same number for usercomments in seperate threads but we can not if usercomments id is primary.

then what should be the primay key in the usercomments table . thread id also can not be use as primay since we may have may replies for one thread of administrator.


here is the code for administrator's thread table
CREATE TABLE IF NOT EXISTS `thread` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `post` text NOT NULL,
  `datePosted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;




here is the code for user's usercomment table. actually this should be corrected as solve my problem but i put it here.

CREATE TABLE IF NOT EXISTS `usercomments` (
  `postId` int(5) NOT NULL,
  `commentId` int(5) NOT NULL,
  `name_cm` varchar(20) NOT NULL,
  `email_cm` varchar(100) NOT NULL,
  `website_cm` varchar(100) NOT NULL,
  `comment_cm` text NOT NULL,
  `dateComment_cm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `allow_cm` tinyint(1) DEFAULT NULL,
  FOREIGN KEY (`postId`) REFERENCES thread(id),
  PRIMARY KEY (`postId`,`commentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;






again another thing i want to do in usercomment's table is starting id from 1 in usercomment table.
because it is clear seperate threads can have same usercommentid.

please help me solve this problem .
At least tell me to handle this via programming side .
then i would ...........
thx in advance,
menuka.

Is This A Good Question/Topic? 0
  • +

Replies To: MYsql + creating Blog + problem in primay key. foriegn key , auto in

#2 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

Re: MYsql + creating Blog + problem in primay key. foriegn key , auto in

Posted 18 July 2011 - 11:51 PM

hi this problem was solved

here is the coding

<?php

/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 * 
 * set minimum post to view
 * vie posts and comments
 */
  include '/Classes/DBhandling.php';
 
//connect database.  
  $dob = new Dbhandling('localhost','root','classdb','needsome1');
  $dob->connectDB();


        
        
 $qryMaxId="select MAX(id) from classdb.thread";
 $maxid=mysql_query($qryMaxId);
 if($maxid){
 
    $postID;

    while($row2=mysql_fetch_array($maxid)){
    $val=$row2['0'];

    $postID=$val;
   //echo "the max postid".$postID.$val;
    }
//set the maximump post id
function setMinimumPostIDtoView(){
  $qryMaxId="select MAX(id) from classdb.thread";
 $maxid=mysql_query($qryMaxId);
 if($maxid){
 
    $postID;

    while($row2=mysql_fetch_array($maxid)){
    $val=$row2['0'];

    $minId=$val-10;
   //echo "the max postid".$postID.$val;
    }
    }else{
        
   
        return FALSE;   
    }
    return $minId;   
}
 
 //this funtion is used to print posts
function printPostAndComments($postID){
//get post details  
    $qryPost="SELECT thread.title,thread.id, thread.post, thread.datePosted from thread where id='$postID'"; 

    $result=mysql_query($qryPost);

    while($row=mysql_fetch_assoc($result)){
                //increment glbol id , after increment if id is equal to post id print nothing otherwise print the content

                 echo "<pre>";
                 echo '<h2>'.$row['title'].'</h2>';
                 echo '<h3>'.$row['post'].'<br>'.'<br>'.'<br>';
                 echo "date posted ". $row['datePosted'].'</h3>'.'<br>'.'<br>';
                //check whether post has a comment and administrator's permistion.
                 //select the maximum commentID inorder to print all the comments under given post id
              $val1=$row['id'];
              echo "<a href='Blog/createComments.php?var1=$val1'>".'leave comment'.'</a>'.'<br>';
             
              echo "</pre>";
    }


     
     printComments($postID);
      
}
 
 //this funtion is used to print the commets
 function printComments($postID){
     
$qry="select userComments.postId,userComments.commentId, userComments.name_cm, userComments.comment_cm, userComments.commentId, userComments.allow_cm, userComments.dateComment_cm
FROM userComments  where postId='$postID'  order by userComments.commentId DESC";

$result=mysql_query($qry) or die(mysql_error());

    while($row=mysql_fetch_array($result)){
              if(!empty($row['commentId']) && $row['allow_cm']==1){
                   
                            echo "<pre>"."<br>";
                             echo "&nbsp"."reply by :\t\t\t".$row['name_cm'].'<br>';
                             echo "&nbsp"."the message is:\t\t".$row['comment_cm'].'<br>';
                             echo "&nbsp"."date posted:\t\t\t".$row['dateComment_cm'];
                             //echo 'commentID'.$row['commentId'].'<br>';
                            // echo 'allow commetn'.$row['allow_cm'];
                             
                             echo "<br>"."<br>"."<br>";
                         //  echo "ID".$row['id']."<$> ".$row['title']." ".$row['popst'];
           
             // echo "varl is".$val1;
             
               }
      
    }
    
 }
        
        //this funciton set the minimum post id to view
        $minPostId=setMinimumPostIDtoView();
        
        
             while($minPostId<$postID){
                 printPostAndComments($postID);
            global $postID;
            $postID=$postID-1;
             
        }
 
 }else{
     
     echo "the qery getting max postid is not working";
 }
       
        
      $dob->closeDB();
        // put your code here
?>


Was This Post Helpful? 3
  • +
  • -

#3 no2pencil  Icon User is online

  • Head MFIC
  • member icon

Reputation: 5066
  • View blog
  • Posts: 26,449
  • Joined: 10-May 07

Re: MYsql + creating Blog + problem in primay key. foriegn key , auto in

Posted 18 July 2011 - 11:53 PM

Thank you for sharing the solution!

I'm glad to see that you were able to solve your problem by yourself.
Was This Post Helpful? 0
  • +
  • -

#4 menukadevinda  Icon User is offline

  • D.I.C Regular

Reputation: -7
  • View blog
  • Posts: 470
  • Joined: 14-April 11

Re: MYsql + creating Blog + problem in primay key. foriegn key , auto in

Posted 18 July 2011 - 11:57 PM

you well come!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1