0

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>

Edited by Bacchus_1

5
Contributors
33
Replies
172
Views
2 Months
Discussion Span
Last Post by Web-Designer
Featured Replies
  • select e.`name`, ( select group_concat( (select s.`shop` from `shops` s where s.`shop_id` = t.`shop_id`) separator ' ') from `shop_employees` t where t.`employee_id` = e.`id` ) `shop list` from `employees` e; Read More

  • You can create stored procedure for convenient use add or delete relations in the table `shop_employees` eg delimiter $$ create procedure `edit_relation`(in p_name varchar(9), in p_shop varchar(9), in p_add boolean) begin declare e_id int; declare s_id int; select t.`id` into e_id from `employees` t where t.`name` like p_name; select t.`shop_id` … Read More

  • 3
    diafol 3,669   2 Months Ago

    Confused. Who's thread is this? bacchus_1 or seularts? Are you the same person? Read More

  • I think that so many subqueries will make slow SQL execution. Use left join and build PHP object from query results. Read More

  • 1
    diafol 3,669   2 Months Ago

    No my mistake - use `DISTINCT` in your `GROUP_CONCAT` for shops GROUP_CONCAT(DISTINCT(sh.shop) SEPARATOR ', ') shop, Read More

2
select e.`name`, (
    select group_concat(
        (select s.`shop` from `shops` s
            where s.`shop_id` = t.`shop_id`)
    separator ' ') from `shop_employees` t
        where t.`employee_id` = e.`id`
) `shop list` from `employees` e;

Edited by AndrisP

0

Wow this are some advanced steps for me. I almost have it working except one tiny thing:

select e.`name`, e.`id`, (
    select group_concat(
        (select s.`shop` from `shops` s
            where s.`shop_id` = t.`shop_id`)
    separator ' ') from `shops_employees` t
            where t.`employee_id` = e.`id`
    ) `shop list` from `employees` e;

It seems I can't get the shop values to show up in my query. Right now I can only output the name and the id from employees, while s.shop doesn't seem to be able to be recognized through my $row['shop'];

Side note: My name is also Andris so I was surprised to see your name replying my post. Hope the P. doesn't stand for Paul cause coincidentaly that is also my second name :D

Edited by seularts

0

Edit\\ I figured it out, shop list was actually shop. I will need to precess this code in my mind a bit cause it's new to me, but thank you super much cause this is amazing work!

Edited by seularts

0

Silly me, I forgot one other aspect. Is there also a way to INSERT these multiple shop values/ids in the table (shops_employees) for each employee id, in many-to-many relations, using the SQL commands?

Edited by seularts

2

You can create stored procedure for convenient use add or delete relations in the table shop_employees eg

delimiter $$
create procedure `edit_relation`(in p_name varchar(9), in p_shop varchar(9), in p_add boolean)
begin
    declare e_id int;
    declare s_id int;
    select t.`id` into e_id from `employees` t where t.`name` like p_name;
    select t.`shop_id` into s_id from `shops` t where t.`shop` like p_shop;
    case when p_add then
        insert ignore into `shop_employees`(`employee_id`,`shop_id`)
        values (e_id, s_id);
    else
        delete from `shop_employees`
        where `employee_id` = e_id and `shop_id` = s_id;
    end case;
end; $$
delimiter ;

and then call edit_relation('user1','shop1',true) for add relation
or call edit_relation('user1','shop1',false) for delete relation.
I recommend set on the "shop_employees" table

unique key (`employee_id`, `shop_id`)

if it is not yet

Edited by AndrisP

0

Weird, this code seems to not react at all with my tables. I created the procedure in phpmyadmin. I don't really understand why should I make the unique key (employee_id, shop_id). Aren't they going to repeat in that table?

Here is my updated code:

<style>
td,th {border: 1px solid #000; padding: 5px;}
</style>

<?php

$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "test";

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 edit($user,$shop)
    {
        try
        {
            $stmt = $this->db->prepare("CALL edit_relation('".$user."','".$shop."',true)");
            $stmt->execute();
            return true;
        }
        catch(PDOException $e)
        {
            return false;
        }

    }

    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);

if(isset($_POST['send'])){

        $user = $_POST['user'];
        $shop = $_POST['shop'];

        $crud->edit($user,$shop);

    }

?>

<table>

<?php

    $query22 = "

    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;";

    $query = "

    select e.`name`, e.`id`, (
    select group_concat(
        (select s.`shop` from `shops` s
            where s.`shop_id` = t.`shop_id`)
    separator ', ') from `shops_employees` t
            where t.`employee_id` = e.`id`
    ) `shop` from `employees` e;

    ";

    $crud->dataview($query);

?>

</table>

<form method="post">

<input type="text" name="user" value="user4">
<input type="text" name="shop" value="shop7">
<input type="submit" name="send" value="submit">

</form>

Also added a screenshop of the DB SP.

Edited by seularts

Attachments dba.png 98.72 KB
1

Yes unique key constraint will not allow duplicate entries when you call procedure edit_relation many times with the same input parameters (input ignore into .....). Table employees field name and table shops field shop also should be unique otherwise selects inside procedure can return many results and its raise error.
Replace in your PHP lines 43, 44

$stmt = $this->db->prepare("CALL edit_relation(?,?,true)");
$stmt->execute(array($user, $shop));

Edited by AndrisP

0

Having unique employees would work against me, cuase I could have 2 employees with the same name. But for this test I have made everything unique. Sadly I still didn't manage to get any results from the code modifications you specified. I'm not getting any errors either and my variables do pass on to their destination.

1

If you can not set unique names for employees edit procedure - change input parameter p_name varchar to p_id int and pass "id" directly

1

You need set foreign key (on delete cascade) constraints also. It will prevent to zombie entries in the table “employees_shops”.

0

Awesome. Managed to make it work somehow. I have one last question and I think I am done :)

How can I expand the query to extra tables from the original statement, to get aditional values from those tables as well (ex. names, departments, devices, shops):

select e.`name`, e.`id`, (
    select group_concat(
        (select s.`shop` from `shops` s
            where s.`shop_id` = t.`shop_id`)
    separator ', ') from `shops_employees` t
            where t.`employee_id` = e.`id`
    ) `shop` from `employees` e;

for example if I would also have departments_employees and departaments + devices_employees and devices tables aside from shops_employees and shops?

Thank you for your support and time ^_^ I really apreciate it!

Edited by seularts

0

Hmm, this seems to do the trick:

select e.`name`, e.`id`, (
    select group_concat(
        (select s.`shop` from `shops` s
            where s.`shop_id` = t.`shop_id`)
    separator ', ') from `shops_employees` t
            where t.`employee_id` = e.`id`
    ) `shop`, (select group_concat(
        (select d.`device` from `devices` d
            where d.`device_id` = n.`device_id`)
    separator ', ') from `devices_employees` n
            where n.`employee_id` = e.`id`
    ) `device` from `employees` e;

Could you please tell me if I did it right? Thanks!

Edited by seularts

1

It seems ok. Save it in the DB as view. I'm notice in this select you can not order shops and can not order devices. Can order by employee name only.

Edited by AndrisP

0

I did test with ORDER BY shop ASC/DESC and it works. So does with devices. I even tested all 3 of them combined with ASC and DESC and they seem to function :)

Edited by seularts

0

Oh, you meant in the Select statement I can't select more than one column from the other tables! I see..

Edited by seularts

0

Well, i could call on that table twice like this:

select e.`name`, e.`id`, (
    select group_concat(
        (select s.`shop` from `shops` s
            where s.`shop_id` = t.`shop_id`)
    separator ', ') from `shops_employees` t
            where t.`employee_id` = e.`id`
    ) `shop`, (
    select group_concat(
        (select s.`gender` from `shops` s
            where s.`shop_id` = t.`shop_id`)
    separator ', ') from `shops_employees` t
            where t.`employee_id` = e.`id`
    ) `gender`, (
    select group_concat(
        (select d.`device` from `devices` d
            where d.`device_id` = n.`device_id`)
    separator ', ') from `devices_employees` n
            where n.`employee_id` = e.`id`
    ) `device` from `employees` e ORDER BY shop ASC, name DESC;
2

I think that so many subqueries will make slow SQL execution. Use left join and build PHP object from query results.

0

Hmmm.. I did try with Left Join and it just fails the code. Maybe I am inserting it wrong. Can you give me an example how should I join them corectly? Thanks!

0

WOW, I made it work like this:

SELECT emp.id,
           emp.name,
           group_concat((sh.shop) separator ', ') shop,
           group_concat((dv.device) separator ', ') device 
    FROM employees AS emp 
         LEFT JOIN shops_employees      AS se ON (emp.id        = se.employee_id)
         LEFT JOIN shops                AS sh ON (sh.shop_id    = se.shop_id)
         LEFT JOIN devices_employees    AS de ON (emp.id        = de.employee_id)
         LEFT JOIN devices              AS dv ON (dv.device_id  = de.device_id)
    GROUP BY emp.id 
    ORDER BY emp.name,
             sh.shop
0

Oh I spoke too soon, I see devices are not showing right. They are repeating as many times as the shop elements. Oh, they are multiplying the results with group_concat :( Can you give me a little guidence where I misswrote the code please!?

Edited by seularts

1

No my mistake - use DISTINCT in your GROUP_CONCAT for shops

GROUP_CONCAT(DISTINCT(sh.shop) SEPARATOR ', ') shop,

Edited by diafol

0

OMG, that saved me such a head ake right now! Freaking missed one word to make it work. Plus this saved me from trying to do subqueries in the left joins. Thanks a billion <3

Edited by seularts

1

Also, if you do this to your SQL (without the DISTINCT), you'll have an idea of the data MySQL was retrieving:

GROUP BY emp.id, sh.shop_id, dv.device_id   

and why you were getting multiple values for shops.

0

Well I ran into a funny issue now. If I add other table values to my query my group_concat changes display direction.

Example: I have 3 languages out of 10 selected. It should display: English, French, Italian.. but when I view the data it displays: Itlian, French, English.

$query = "

        SELECT can.id,
            can.nume,
            can.prenume,
            can.telefon,

            group_concat(distinct(se.sex) separator ', ') AS sex,
            group_concat(distinct(lb.limba) separator ', ') AS limbi_straine, 

            can.cv,
            can.observatii,
            can.interview,
            can.acceptat,
            can.plecat,
            can.ignorat,
            can.stare,
            can.data

        FROM candidati AS can 
            LEFT JOIN sex_candidati         AS sec ON (can.id      = sec.candidat_id)
            LEFT JOIN sex                   AS se ON (se.sex_id    = sec.sex_id)
            LEFT JOIN limbi_candidati       AS lbc ON (can.id      = lbc.candidat_id)
            LEFT JOIN limbi                 AS lb ON (lb.limba_id  = lbc.limba_id)

        GROUP BY can.id

        ORDER BY ".$p1." ".$p2

        ;

public function dataview_new($query)
{
    $stmt = $this->db->prepare($query);
    $stmt->execute();

    if($stmt->rowCount()>0)
    {
        while($row=$stmt->fetch(PDO::FETCH_ASSOC))
        {

            $date_in = $row['data'];
            $date_out = explode(' ', $date_in);

            ?>
            <tr>
            <td><?php echo $date_out[0]; ?></td>
            <td><?php print($row['telefon']); ?></td>
            <td><?php print($row['limbi_straine']); ?></td>

            ....

           }
    else
    {
        ?>
        <tr>
        <td colspan="12" class="text-center danger"><i class="glyphicon glyphicon-eye-close"></i> Nu a fost gasit nici un rezultat...</td>
        </tr>
        <?php
    }

} 

It only shows the correct listing if I remove all the other elements such as can.name, can.email... and just leave: group_concat(distinct(lb.limba) separator ', ') AS limbi_straine

Now in the same context if I try to output as an array, it works perfectly fine.

$stmt = $this->db->prepare("
        SELECT can.id,
            can.*,

            group_concat(distinct(se.sex) separator ', ') AS sex,
            group_concat(distinct(lb.limba) separator ', ') AS limbi_straine

        FROM candidati AS can 
            LEFT JOIN sex_candidati         AS sec ON (can.id           = sec.candidat_id)
            LEFT JOIN sex                   AS se ON (se.sex_id    = sec.sex_id)
            LEFT JOIN limbi_candidati       AS lbc ON (can.id       = lbc.candidat_id)
            LEFT JOIN limbi                 AS lb ON (lb.limba_id  = lbc.limba_id)

        WHERE can.id=:id

        GROUP BY can.id  

        ");
        $stmt->execute(array(":id"=>$id));
        $editRow=$stmt->fetch(PDO::FETCH_ASSOC);
        return $editRow;

If I echo row['limbi_straine'] it shows the correct listing of the words. I mean it is not a big issue, but I don't actually know what is causing it!?

Edited by seularts

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.