SELECT query selects a row multiple times, how to select only once

Question

I'm trying to run a search of a photos table based on tags.

I split the search term into individual keywords, and search the table for each individual keyword, pushing any matches to an array.

$keywords = explode(' ', $keywords);
$photos = array();

// loop through each keyword
foreach($keywords as $keyword){

$row = $mysqli->query(" SELECT * FROM photos WHERE tags LIKE '%$keyword%' ");

// loop through each row result
    while( $query_result = $row->fetch_assoc() ){

        // push result to photos array
        array_push($photos, $query_result);

    }

}

however, this can return the same table row multiple times, e.g if I search for 'mod modded', any photo with a tag of 'modded' would be pushed top the array twice as it matches both keywords.

How do I make sure to only select a row once?

EDIT

Thanks for the replies but neither worked, i've achieved what Im after with:

$keywords = explode(' ', $keywords);
$photos = array();

// loop through each keyword
foreach($keywords as $keyword){

    $row = $mysqli->query(" SELECT * FROM photos WHERE tags LIKE '%$keyword%' ");

    // loop through each row result
    while( $query_result = $row->fetch_assoc() ){

        $dontPush = false;

        // check photos array to see if the query_result already exists
        foreach($photos as $photo){
            if($photo == $query_result){
                $dontPush = true;
            }
        }

        if($dontPush === false){
            // push result to photos array if it dodesnt already exist
            array_push($photos, $query_result);
        }

    }

}

but this creates so many loops that if i have a big database it will surely take a long time to return the results? Is there a better way

First you dont want to use LIKE '%%' an index on tags will be useless because this will require an FULL table scan to find the matching records... — Raymond Nijland
– Raymond Nijland, Commented Oct 19, 2013 at 14:06

zzlalani · Accepted Answer · 2013-10-19 13:02:49Z

1

Try this

<?php 
$keywords = explode(' ', $keywords);
$photos = array();

$query = " SELECT * FROM photos ";

// loop through each keyword
foreach($keywords as $k => $keyword){

    if ($k==0) {
       $query .= " WHERE ";
    }

    $query .= " tags LIKE '%$keyword%' ";

    if ($k-1 < count($keywords)) {
       $query .= " OR ";
    }
}

$row = $mysqli->query( $query );
while( $query_result = $row->fetch_assoc() ){

    // push result to photos array
    array_push($photos, $query_result);

}
?>

answered Oct 19, 2013 at 13:02

zzlalani

24.6k16 gold badges48 silver badges73 bronze badges

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

Comments

Sumurai8 · Accepted Answer · 2013-10-19 14:20:58Z

1

Try Select Distinct

SELECT DISTINCT * FROM photos WHERE tags LIKE '%$keyword%'

W3 Schools

Alternatively you can also add LIMIT 1 to the end to return only the first row.

edited Oct 19, 2013 at 14:20

Sumurai8

20.8k11 gold badges70 silver badges103 bronze badges

answered Oct 19, 2013 at 12:55

Sparatan117

1388 bronze badges

3 Comments

Sumurai8 Over a year ago

I would encourage to use the official documentation over w3schools. See w3fools.com for more information.

Sparatan117 Over a year ago

While I see where you're coming from, I also counter that with the fact that there are some good working examples to show. If you take their entire website as law, then you're sadly mistaken, but if you're just starting out then it's a good reference to make sure you're at least getting your syntax right.

madebydavid Over a year ago

The duplicates wont be returned from the query - it is being run on the photos table - I assume that each photo only exists once in the photo table and so adding a DISTINCT will do nothing. The duplicates are appearing because of the loop around the query.

rpsep2 · Accepted Answer · 2013-10-19 14:07:45Z

0

For anyone interested I managed to solve this to make sure no duplicates are returned:

$keywords = explode(' ', $keywords);
$photos = array();
$query = " SELECT * FROM photos WHERE tags LIKE ";

// loop through each keyword
foreach($keywords as $i => $keyword){

    $query .= "'%$keyword%'";

    if($i+1 < count($keywords) ){
        $query .= " union SELECT * FROM photos WHERE tags LIKE ";
    }


}

$row = $mysqli->query( $query );

// loop through each row result
while( $query_result = $row->fetch_assoc() ){

    array_push($photos, $query_result);

}

answered Oct 19, 2013 at 14:07

rpsep2

3,12110 gold badges41 silver badges52 bronze badges

Collectives™ on Stack Overflow

SELECT query selects a row multiple times, how to select only once

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related