Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • how to avoid duplicate data in php mysql ?

    • 0
    • 0
    • 0
    • 1
    • 0
    • 1
    • 0
    • 698
    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  ?

 1 Answer(s)

  • 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
                           OR                           
                           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: