1

I'm having some trouble to make a query that get all the information from multiple tables with INNER JOIN. The table p_cards is the "main table" that have a relationship 1-M. This means that every other table (p_images, p_infotext,p_rules can have multiple rows associated with the same ID)

My function is the following:

function GetpById($mysqli, $id) {
    if($stmt = $mysqli->prepare("
        SELECT * 
        FROM p_cards 
        INNER JOIN p_images ON p_images.card_id = p_cards.id
        INNER JOIN p_infotext ON p_infotext.card_id = p_cards.id
        INNER JOIN p_rules ON p_rules.card_id = p_cards.id
        WHERE p_cards.id = ?
    ")) {
        $stmt->bind_param("i", $id);
        $stmt->execute() or trigger_error($stmt->error, E_USER_ERROR);
        ($stmt_result = $stmt->get_result()) or trigger_error($stmt->error, E_USER_ERROR);
        if ($stmt_result->num_rows>0) {
            while($row_data = $stmt_result->fetch_assoc()){
                $array[] = $row_data;
            }
            return $array   
        } else {
            return false;
        }
    } else {
        return false;
    }
}

Using the above function it will create a complete row for each different row inside the other tables, making the information in p_cards duplicated for each. For example, the p_images table will have id, p_id , image, language. For each image that exists associated with that ID, it will associate the information of p_cards.

What I would like is not to have duplicated information. How can I achieve this?

Edit:

Table p_cards Table p_cards

Table p_images t p_images

There are other tables but it's enough to show it (since there are many tables, every of them working as 1-M)

When I do the query, I get the information of p_card for each information in p_images (and other tables): enter image description here

This way I expect to get something like this so I can use the information to make a form to edit the data:

id
slug
card_number
...
--[images]
---[en]
----thumb_img
----full_size
----name
---[pt]
----thumb_img
...

Instead of having one row with all information. What would be the best approach?

Tiago
  • 625
  • 5
  • 16
  • 4
    Please show sample data which demonstrates the duplicates. Using `DISTINCT` or maybe `GROUP BY` could be appropriate here. – Tim Biegeleisen Oct 18 '18 at 09:50
  • Show what you got and what you expect, thanks! – Salim Ibrohimi Oct 18 '18 at 09:54
  • @TimBiegeleisen . I've edited the thread to see if it's better to understand. I really don't know how to explain better. – Tiago Oct 18 '18 at 10:11
  • @SalimIbrogimov done – Tiago Oct 18 '18 at 10:11
  • The screen shots are hard to read. Please include data as _text_. And the goal here is not to include massive amounts of data, just enough to get the point across. Most likely, there is a simple fix for you problem, but the answer is not visible right now. – Tim Biegeleisen Oct 18 '18 at 10:12
  • use group by on the index you don't want duplicate – abr Oct 18 '18 at 10:12
  • If you are having 1 - M relationship then the as per the query there will be duplicate, one option i can see here is if you cant change the relationship then you can write php code to generate an array as per your requirement – Mangesh Sathe Oct 18 '18 at 10:22
  • Or, use a unique index to prevent duplicate row creation in the first place... – Mason Stedman Oct 18 '18 at 10:29

1 Answers1

0

You get multiple rows for the same card id because you have multiple rows for it in the tables you join with.

If you want to have only one row per card id, you can use GROUP BY p_cards.id, and then you can get a comma separated list of the different values in the other tables using GROUP_CONCAT() function.

However, in your situation I think it is better to do 3 seprate queries instead of doing this in a join.

The Surrican
  • 29,118
  • 24
  • 122
  • 168
  • For the sake of data treatment I made the queries in separate way and then created an array as I needed. Thanks for your suggestion. – Tiago Oct 18 '18 at 13:28