SELECT DISTINCT

[pablo1988]

How can I stop duplication in the below code? Where do I implement the DISTINCT function?

 

 

$sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $name .  "%' OR Last_Name LIKE '%" . $name ."%' OR Skill_Name LIKE '%" . $name ."%'";

   

//-run  the query against the mysql query function

    $result=mysql_query($sql);

   

//-create  while loop and loop through result set

    while($row=mysql_fetch_array($result)){

            $First_Name  =$row['First_Name'];

            $Last_Name=$row['Last_Name'];

            $Resource_ID=$row['Resource_ID'];

   

//-display the result of the array

    echo "<ul>\n";

    echo "<li>" . "<a  href=\"a.php?id=$Resource_ID\">"  .$First_Name . " " . $Last_Name .  "</a></li>\n";

    echo "</ul>";

    }

    }

[AyKay47]

have you tried SELECT DISTINCT *

[pablo1988]

Yes, that has no effect for some reason.

[Muddy_Funster]

It's probably making mutliple matches because of the excessive use of LIKE. have you tried this?

$sql = <<<SQL
SELECT fields, that, you, want, and, need
FROM resource
INNER JOIN resource_skill
ON
(resource.Resource_ID = resource_skill.Resource_ID)
INNER JOIN skill
ON
(resource_skill.Skill_ID = skill.Skill_ID)
WHERE
(
(First_Name LIKE '%$name%' OR Last_Name LIKE '%$name%' OR Skill_Name LIKE '%$name%')
)
GROUP BY Skill_Name
SQL;

//-run  the query against the mysql query function
     $result=mysql_query($sql);
   
//-create  while loop and loop through result set
     while($row=mysql_fetch_array($result)){
             $First_Name  =$row['First_Name'];
             $Last_Name=$row['Last_Name'];
             $Resource_ID=$row['Resource_ID'];
   
//-display the result of the array
     echo "<ul>\n";
     echo "<li>" . "<a  href=\"a.php?id=$Resource_ID\">"   .$First_Name . " " . $Last_Name .  "</a></li>\n";
     echo "</ul>";
     }
     }

[pablo1988]

Thanks but I got errors with that. Is there not a way of adding the DISTINCT function into the code somewhere, or GROUP BY? DISTINCT worked when I was using SELECT DISTINCT First_Name etc but not with SELECT DISTINCT*

 

Would hugely appreciate a solution to this! Thanks.

[Sajesh Mohan]

please use GROUP BY

[Muddy_Funster]

Thanks but I got errors with that. Is there not a way of adding the DISTINCT function into the code somewhere, or GROUP BY? DISTINCT worked when I was using SELECT DISTINCT First_Name etc but not with SELECT DISTINCT*

 

Would hugely appreciate a solution to this! Thanks.

What errors?

 

chalk up another reason not to use select *