Repeating data from database?

Question

0.00/5 (No votes)

See more:

Hi, I would like to tell you about the problem I have to see if someone can help me.
I have created a table in a database with the following columns:

Table: union

exercise_id_fk	tag_id_fk
   1	             1
   1	             2
   2	             1
   2	             3

What I pretend with my program is that a user chooses the tags they want so that the exercise corresponding to the chosen tags is shown on the screen. For example, if the user chooses tag 1, exercises 1 and 2 will appear. If you choose tags 1 and 3, exercises 1 and 2 appear.

The problem with my program is that when the user chooses tags 1 and 3, for example, exercises 1 and 2 appear but 2 repeats. Or when you choosing tags 1 and 2, exercise 1 is repeated.

Does anybody know any way so that this does not happen? Only show the exercises once?

What I have tried:

Here I show part of my code:

PHP

$sql = "SELECT * FROM exercises, union, tags where exercise_id = exercise_id_fk and tag_id = tag_id_fk";

if (!empty($_SESSION['tags_array'])) {
    $sql .= " and (";
    foreach ($_SESSION['tags_array'] as $tagId)
        $sql .= 'tag_id = ' . $tagId . ' or ';

    $sql .= "tag_id = -1);";
}

$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
    echo $row["exercise_id"] . ". " .  $row["title"] . "<br>";
}

Posted 2-Aug-18 0:00am

Member 13933133

Updated 2-Aug-18 1:06am

CHill60

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Accepted Answer · 2018-08-02T00:38:00

What you are looking for is the SQL DISTINCT keyword which returns only distinct (different) values.

Also your SQL command is much too complicated (contains redundant information). As far as I understand, you want to query the single table 'union' for a single field. Then the general syntax might be

SQL

SELECT DISTINCT exercise_id_fk FROM union WHERE tag_id_fk IN (<list_of_tags>)

The above uses also the SQL IN keyword to match values from a list.

Untested example from scratch:

PHP

$tags_array = $_SESSION['tags_array'];
$tags = count($tags_array);
$sql = "SELECT DISTINCT exercise_id_fk FROM 'union' WHERE tag_id_fk"; 
if ($tags == 1) {
    $sql .= "=" . $tags_array[0]; 
}
else {
    $sql .= " IN (" . $tags_array[0];
    for ($i = 1; $i < $tags; $i++) {
        $sql .= ", " . $tags_array[$i];
    }
    $sql .= ")"
}

Note that I have quoted the table name 'union' because it is a reserved SQL word.