April 8, 2012
Mysql query and foreach query with double items
Question by ciprian
I am just grabbing user data from a few tables but users have the option of adding more than one skill or exp.
$query_str = "SELECT a.*, b.*, c.*, d.*, e.* FROM edu a
JOIN exp b ON a.user_id=b.user_id
JOIN user_profiles c ON a.user_id=c.user_id
JOIN skills d ON a.user_id=d.user_id
JOIN comp e ON a.user_id=e.user_id
WHERE a.user_id = ?";
$query = $this->db->query($query_str, $end_user);
if($query->num_rows() > 0) {
foreach($query->result_array() as $stuff) {
$data[] = $stuff;
}
return $data;
} else {
return false;
}
Everything is fine until I try to display the data. If a user has two exp, everything else is showing up twice. I m not sure how to write this. Would it be easier to do separate them? One query for each item?
public function get_education()
{
$one_edu = $this->test_model->one_edu($end_user);
if ($one_edu != false)
{
foreach($one_edu as $edas) {
$one_edu_html .='<p>'.$edas['objective'].'</p>';
}
foreach($one_edu as $exp) {
$one_edu_html .= '<p>'.$exp['exp_title'].'</p>';
}
foreach($one_edu as $educ) {
$one_edu_html .= '<p>'.$educ['edu_title'].'</p>';
}
$result = array('status' => 'ok', 'content' => $one_edu_html);
echo json_encode($result);
exit();
}else{
$result = array('status' => 'ok', 'content' => '');
echo json_encode($result);
exit();
}
}
Now it s returning something like this:
Objective
Exp title1
Exp title2
Edu title
Edu title <- Extra
Using codeigniter
Answer by Starx
The main reason for this is because you haven’t grouped your rows. Add a grouper, like GROUP BY a.id at the end
Update
The rows are duplicating because they are different, you can group the field on a single rows, using GROUP_CONCAT
SELECT a.user_id, a.education, GROUP_CONCAT(a.edu_title SEPARATOR ",") "Edu_Title", GROUP_CONCAT(b.exp_title SEPARATOR ",") "Experience Title" ,b.experience, c.objective, d.skill, e.comp FROM edu a
JOIN exp b ON a.user_id=b.user_id
JOIN user_profiles c ON a.user_id=c.user_id
JOIN skills d ON a.user_id=d.user_id
JOIN comp e ON a.user_id=e.user_id
WHERE a.user_id = 243;
Demo
