I am rather new to Normalization. I understand the concept of how it organizes the tables but I fail to see how I can use the data to show up correctly in my query using pdo.
I have 3 tables:
employees:
id | name
1 user1
2 user2
2 user3
shops:
shop_id | shop
1 shop1
2 shop2
3 shop3
4 shop4
5 shop5
shop_employees:
shops_employees_id | employee_id | shop_id
1 1 1
2 1 2
3 2 4
4 3 3
5 3 5
but I get:
1 user1 shop1
1 user1 shop2
2 user2 shop4
3 user3 shop3
3 user3 shop5
How can I get them in one row like:
1 user1 shop1 shop2
2 user2 shop4
3 user3 shop3 shop5
this is my code:
<?php
...
try
{
$DB_con = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
$DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo $e->getMessage();
}
class crud
{
private $db;
function __construct($DB_con)
{
$this->db = $DB_con;
}
public function getID($id)
{
$stmt = $this->db->prepare("SELECT * FROM employees WHERE id=:id");
$stmt->execute(array(":id"=>$id));
$editRow=$stmt->fetch(PDO::FETCH_ASSOC);
return $editRow;
}
public function dataview($query)
{
$stmt = $this->db->prepare($query);
$stmt->execute();
if($stmt->rowCount()>0)
{
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
?>
<tr>
<td><?php print($row['id']); ?></td>
<td><?php print($row['name']); ?></td>
<td><?php print($row['shop']); ?></td>
<?php
}
}
else
{
?>
<tr>
<td colspan="8" class="text-center danger"> Nothing founds...</td>
</tr>
<?php
}
}
}
$crud = new crud($DB_con);
?>
<table>
<?php
$query = "
SELECT emp.id,
emp.name,
sh.shop
from employees AS emp
INNER JOIN shops_employees AS se ON (emp.id = se.employee_id)
INNER JOIN shops AS sh ON (sh.shop_id = se.shop_id)
ORDER BY emp.name,
sh.shop;";
$crud->dataview($query);
?>
</table>
