SQL Search Keyword Search, Return Rows only once

Question

I currently have an SQL search query using LIKE. This is then used to generate returned results in HTML.

However I would like to only return a row once, perhaps first, if it contains more than one of the keywords. Currently it would return an item once for keyword x and again for keyword y.

At the moment I am using a for and while loop to carry out the searches for each of the keywords. Is this the best way to go about it or is there anyother way to do it?

For the moment I would like to keep it simple, so no solr or lucene, etc.

I have added code for clarity, but a large amount is not non-related. The code DOES work, so not looking for suggestions in that respect.

<?php
session_start();
include_once('connect_to_sql.php');
include_once('distance.php');
$itemList = '';
if(isset($_POST['words'])){
    $words = $_POST['words'];
} else { 
    $words = "";
}
$queries = preg_split("/[\s,]+/", $words);
for($i = 0; $i < count($queries); $i++){
    $keyword = $queries[$i];
    if($keyword!=""){
if(isset($_SESSION['myLong']) && isset($_SESSION['myLat'])){
    $request_long = $_SESSION['myLong'];
    $request_lat = $_SESSION['myLat'];
    $itemQuery = mysql_query("SELECT *, ( 3959 * acos( cos( radians('$request_lat') ) * 
cos( radians( item_lat ) ) * 
cos( radians( item_long ) - 
radians('$request_long') ) + 
sin( radians('$request_lat') ) * 
sin( radians( item_lat ) ) ) ) 
AS distance FROM offerings WHERE sold = 'no' AND item_desc LIKE '%$keyword%'
ORDER BY distance ASC");
} else {
    $itemQuery = mysql_query("SELECT * FROM offerings WHERE sold = 'no' AND item_desc LIKE '%$keyword%'
    ORDER BY id DESC");
}


//get all items

    if(mysql_num_rows($itemQuery) < 1){
        $itemList = "No items currently on sale.";
    } else {
        while($itemResult = mysql_fetch_array($itemQuery)){
            $from = $itemResult['from_user'];
            $locationQuery = mysql_query("SELECT postcode FROM users WHERE id = '$from' ");
            $locationResult = mysql_fetch_array($locationQuery);
            $se = urlencode($locationResult['postcode']);
            $num = urlencode($itemResult['id']);
            if($itemResult['item_picture'] == ''){
                $img = '<img src="../offerings/placehold.png" />';
            } else {
                $img = '<img src="../offerings/'.$itemResult['from_user'].'/'.$itemResult['item_picture'].'" />';
            }
                    $url = "http://maps.google.com/maps/api/geocode/json?address=".$se."&sensor=false";
                    $jsonData = file_get_contents($url);
                    $data = json_decode($jsonData);
                    $xlat = $data->{'results'}[0]->{'geometry'}->{'location'}->{'lat'};
                    $xlong = $data->{'results'}[0]->{'geometry'}->{'location'}->{'lng'};
                    if(isset($request_lat) && isset($request_long)){
                        $dist = number_format(distance($request_lat, $request_long, $itemResult['item_lat'], $itemResult['item_long']), 2)." miles / ";
                        $distkm = number_format(distance($request_lat, $request_long, $itemResult['item_lat'], $itemResult['item_long'])*1.609344, 2)." km";
                    } else {
                        $dist = $locationResult['postcode']; 
                        $distkm = '';
                    }

        $checkifsold = mysql_query("SELECT * FROM orders WHERE item_id='$num'");
        if(mysql_num_rows($checkifsold) > 0){
            $itemPrice = "SOLD";
        } else {
            $itemPrice = '£'.$itemResult['item_price'];
        }
        $itemList .= '<div class="anItemSearched"><a href="item.php?num='.$num.'">'.$img.'</a>
                        <div class="innerDetail">'.$dist.$distkm.'</div>
                        <div class="innerDetail">'.$itemPrice.'</div>
                        <div class="innerDetail">'.$itemResult['item_name'].'</div>
                        </div>';
        }
    }
    }
    }
    echo $itemList;
?>

Thanks, Cillian

**Warning:** you're using [a **deprecated** database API](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) yourself from. And you should also escape `%` and `_` in LIKE queries. — Marcel Korpel, Dec 07 '13 at 18:56
Hi Marcel, thank you for your comment, but it doesn't answer my question. — Cillian, Dec 07 '13 at 21:38

SQL Search Keyword Search, Return Rows only once

0 Answers0