How to ignore duplicate rows in foreach loop when retrieve the data from database by MYSQL

Question

I have got the following code to find the similar keywords in body of a text and display the related links with same keyword.

But the problem is for example if two keywords are in row 2 body, Row 2 displays two times but I need the row 2 is displayed once. I tried SELECT DISTINCT but it does not work in foreach loop correctly.

$tags2=explode(",",$tags);
foreach ($tags2 as $i) {
    $cat_sqlii="SELECT DISTINCT id, source,title,summary,newsText,photo,mainphoto,link,Date,tags FROM newxtext WHERE (newsText LIKE '%$i%')";
    $cat_sql_queryii=mysqli_query($con,$cat_sqlii);
    $cat_sql_rowii=mysqli_fetch_assoc($cat_sql_queryii);
    do{
        echo $cat_sql_rowii['id'].'<br/>';
    }while($cat_sql_rowii=mysqli_fetch_assoc($cat_sql_queryii));
}

distinct only works for the concurrent query. in your foreach loop, you issue one query for each keyword. these queries don't know about each other. I recommend working with a temporary table receiving the result and then querying this table using distinct. — Psi
– Psi, Commented Aug 11, 2017 at 23:44

Barmar · Accepted Answer · 2017-08-11 23:54:28Z

1

Just do one query that tests for any of the tags using OR.

$patterns = array();
foreach ($tag in explode(',', $tags)) {
    $patterns[] = "newstext like '%$tag%'";
}
$where = implode(' OR ', $patterns);
$cat_sqlii="SELECT id, source,title,summary,newsText,photo,mainphoto,link,Date,tags 
            FROM newxtext 
            WHERE ($where)";
$cat_sql_queryii=mysqli_query($con,$cat_sqlii);
while ($cat_sql_rowii = mysqli_fetch_assoc($cat_sql_queryii)) {
    echo $cat_sql_rowii['id'].'<br/>';
}

edited Aug 11, 2017 at 23:54

answered Aug 11, 2017 at 23:46

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Psi Over a year ago

Careful: When there are additional conditions to be added later, this may lead to unwanted behavior. Better place brackets around the $where-clause

Psi · Accepted Answer · 2017-08-11 23:57:26Z

Another approach could be using a temporary table receiving the results for each iteration and querying that table in the end:

mysqli_query($con, "CREATE TEMPORARY TABLE tmpSearchResults(id int primary key) ENGINE=Memory");

$tags2=explode(",",$tags);
foreach ($tags2 as $i) {
$insertToTemp ="INSERT INTO tmpSearchResults 
        SELECT id 
        FROM newxtext 
    WHERE (newsText LIKE '%$i%')";
    mysqli_query($con,$insertToTemp);
}

$queryFromTemp = "SELECT DISTINCT n.id, n.source,n.title,n.summary,n.newsText,n.photo,n.mainphoto,n.link,n.`Date`,n.tags
    FROM tmpSearchResult r
    JOIN newxtext n
    WHERE r.id = n.id";

$resultSet = mysqli_query($con,$queryFromTemp);
while($data = mysqli_fetch_assoc($resultSet)){
   // ... process here
}
mysqli_free_result($resultSet);

When you close the connection, the temporary table will be dropped automatically.

If you expect huge search results, consider using another storage engine than MEMORY for the temptable.

Collectives™ on Stack Overflow

How to ignore duplicate rows in foreach loop when retrieve the data from database by MYSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related