CODEWITHSUNDEEP
phpmysqlforeach
Peter
Peter

Reputation: 29

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

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

Upvotes: 0

Views: 487

Answers (2)

Psi
Psi

Reputation: 6793

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.

Upvotes: 0

Barmar
Barmar

Reputation: 782285

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/>';
}

Upvotes: 1

Related Questions