CODEWITHSUNDEEP
phpmysqlsqlcodeigniterjoin
user3898892
user3898892

Reputation: 27

Altering a query to not duplicate data

In my database, I have 3 tables.

Jokes:

CREATE TABLE IF NOT EXISTS `jokes` (
  `joke_id` int(11) NOT NULL AUTO_INCREMENT,
  `joke` varchar(1024) NOT NULL,
  `category_id` int(11) NOT NULL,
  `vote` int(255) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`joke_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Category:

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(51) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

And finally, Comments:

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `comment` text NOT NULL,
  `joke_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

This function is stored in my read controller, with the name of Joke and it grabs the data from the db then sends it to the view.

public function joke($joke_id = FALSE)
{
    if ($joke_id === FALSE) redirect('main'); //go to default controller

    $data['results'] = $this->comments_m->getComments($joke_id, $this->uri->segment(2));

    $this->load->view('template/header');
    $this->load->view('template/sidebar');
    $this->load->view('content/read', $data);
    $this->load->view('template/footer');

}

The model (getjokes_m) has a function called readJokes which gets the joke, joke_id, category name, votes, comments. This function is used to read specific jokes in the database.

function readJokes($joke_id)
{

    $query = $this->db->query("SELECT c.name, j.*, co.* FROM jokes j LEFT JOIN category c ON c.category_id = j.category_id LEFT JOIN comments co ON co.joke_id = j.joke_id WHERE j.joke_id =  '$joke_id'") or die("No results found" );

    //displays the results
    return $query->result();

}

The query above is where the problem lies. For the moment it returns the joke as many times as the joke has been commented. I.e if the joke has 6 comments, the joke will show 6 times (no idea why). Any help altering this query, to just show the joke once, with all the comments the joke has, will be much appreciated.

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You want a group by with group_concat() for the comments:

SELECT c.name, j.*, group_concat(co.comment separator '|') as comments
FROM jokes j LEFT JOIN
     category c
     ON c.category_id = j.category_id LEFT JOIN
     comments co
     ON co.joke_id = j.joke_id
WHERE j.joke_id =  '$joke_id'
GROUP BY j.joke_id;

Actually the group by is not strictly needed here, unless you want to get the results for more than one joke.

Upvotes: 1

Related Questions