Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

  • how to avoid duplicate data in php mysql ?

    @nasyia
    Recommendations Offline Message

    • 0
    • 0
    • 0
    • 1
    • 0
    • 1
    • 0
    • 711
    Answer it

    i have this database which contain table for questions and other for answers
    how can i display each questions with the answers that belong to this question
    my problem is when i try to display the questions i get duplicated rows or all the answers for one question

     

     

    CREATE TABLE IF NOT EXISTS `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) NOT NULL,
  `question` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

    CREATE TABLE IF NOT EXISTS `answers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) NOT NULL,
  `answer` varchar(255) NOT NULL,
  `correct` enum('0','1') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

     

    how to avoid duplicate data in php mysql ?
    mysql php
    Answer it

 1 Answer(s)

  • @jayant.kumar
    Recommendations Offline Message

    over 8 years ago

    Use GROUP_CONCAT and CONCAT

    The query should be like this : 

    print("SELECT q.*,
      (SELECT GROUP_CONCAT(concat(a.choices, ':', a.is_correct) SEPARATOR ',')
         FROM answers a 
        WHERE q.question_number = a.question_number) AS all_choices
 FROM questions q");

    For output of this query, see screen shot

    Comment
Sign In
Forgot Password?
                           OR                           
Create an account !!
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: