mysqli search not working correctly returning multi found how do i search

im trying to search multiple fields and im getting multiple repeat returns
how do i properly make my code return correct info and the code does not limit the results too (is that due to finding multi matches for same records?)

how do i fix my code to return unique and can limit the returns please

also is prepare need here if so how would i implement it here.

thank you in advance for any code or help you may provide

my sql line (built)
sql: SELECT * FROM iwia_events WHERE (event_title LIKE '%food%' OR event_location LIKE '%food%' OR event_text LIKE '%food%' OR extra_info LIKE '%food%' OR categories LIKE '%food%' OR tags LIKE '%food%' OR type LIKE '%food%') AND event_start >= now() ORDER BY event_start ASC LIMIT 0,5
Searching for: food 

Open in new window


my code so far
<?php
include("../../mysqli_ctx.php");

if ($_POST['limit']) {
     $limit = " LIMIT ".$_POST['limit'];
}
elseif ($_GET['limit']) {
     $limit = " LIMIT ".$_GET['limit'];
}else {
	$limit = " LIMIT 0,15 \n";
}    
       
if ($_POST['search_value']) {
     $e = $_POST['search_value'];
}
elseif ($_GET['search_value']) {
     $e = $_GET['search_value'];
}
else {
    //die("no var set or passed");
    $e ="";
}  
$search_like = "";

if ($e) {
    $search_like = "WHERE (event_title LIKE '%$e%' OR \n" .
           "event_location LIKE '%$e%' OR \n" .
           "event_text LIKE '%$e%' OR \n" .
           "extra_info LIKE '%$e%' OR \n" .
           "categories LIKE '%$e%' OR \n" .
           "tags LIKE '%$e%' OR \n" .
           "type LIKE '%$e%') \n" .
           " AND event_start >= now() ORDER BY event_start ASC \n";
}

   $sql = "SELECT * FROM iwia_events " .$search_like."".$limit;

   echo "sql: ".$sql."<br>";
   //$query_result = $mysqli->query($query);
   $res = $mysqli->query($sql);
 $result_tb ="";  
// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . '<br> ERRNO: '
    . $mysqli->errno
    . '<br> ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    //echo "<br/>QUERY: $sql ";
    $result_tb .= "<br/>No Entrys Found<br> ";
    echo PHP_EOL;
}
else
{
        if ($e) {
            echo "Searching for: ".$e." "."<br>";
        } else {
            //
        }
         
            //echo "<br/>QUERY: $sql ";
    if ($num_fmt = 1) {
        //$result_tb .=" FOUND $num_fmt Event <br/><br/>";
        echo PHP_EOL;
    } else {
       //$result_tb .=" FOUND $num_fmt Events <br/><br/>";
       echo PHP_EOL; 
    }
    
    
}
$event = array();
// do event data output block
while ($row = $res->fetch_array())
{
    $event[] = $row;
}

?>

Open in new window

JohnnyAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
>>im getting multiple repeat returns
could we see some results that demonstrate the problem?

>>is that due to finding multi matches for same records?
this should not be possible.

Your sql query indicates a single table, so unless that table really is storing duplicated records, then it will not matter how many times the word "food" appears in a record - it is still one record when returned.

e.g.
|   EVENT_TITLE | EVENT_LOCATION |                     EVENT_START |     EVENT_TEXT |        EXTRA_INFO |        CATEGORIES | TAGS | TYPE |
|---------------|----------------|---------------------------------|----------------|-------------------|-------------------|------|------|
| foodie-foodie |         mycity | November, 05 2013 00:00:00+0000 | celebrate food |   snacks and food | food:snacks:music | food | food |
|     dog-world |         mycity | November, 05 2013 00:00:00+0000 | celebrate dogs | dogs and dog food |              dogs | dogs | dogs |

Open in new window

produced by your query against this assumed data:
    CREATE TABLE iwia_events
    	(`event_title` varchar(13), `event_location` varchar(6), `event_start` datetime, `event_text` varchar(16), `extra_info` varchar(17), `categories` varchar(17), `tags` varchar(5), `type` varchar(5))
    ;
    	
    INSERT INTO iwia_events
    	(`event_title`, `event_location`, `event_start`, `event_text`, `extra_info`, `categories`, `tags`, `type`)
    VALUES
    	('etitle', 'elocn', '2013-11-05 00:00:00', 'etext', 'ex-info', 'ecat', 'etag', 'etype'),
    	('foodie-foodie', 'mycity', '2013-11-05 00:00:00', 'celebrate food', 'snacks and food', 'food:snacks:music', 'food', 'food'),
    	('dog-world', 'mycity', '2013-11-05 00:00:00', 'celebrate dogs', 'dogs and dog food', 'dogs', 'dogs', 'dogs'),
    	('blue-cheese', 'mycity', '2013-11-06 00:00:00', 'blue grass music', 'blues music', 'music', 'music', 'music')
    ;

**Query 1**:

    SELECT *
    FROM iwia_events
    WHERE (
       event_title LIKE '%food%'
    OR event_location LIKE '%food%'
    OR event_text LIKE '%food%'
    OR extra_info LIKE '%food%'
    OR categories LIKE '%food%'
    OR tags LIKE '%food%'
    OR type LIKE '%food%'
       )
    AND event_start >= now()
    ORDER BY event_start ASC 
    LIMIT 0,5
;

-- http://sqlfiddle.com/#!9/573dc/1 

Open in new window

So, if you were to run the query directly against your database using a sql query tool you should not get multiple rows from a single record just because it has more than one reference to food.

If you do run the query against your db and get multiple rows - then you really do have duplication in the table.

My guess is that the repetition is in the PHP (others will assist on that).
0
 
JohnnyAuthor Commented:
@PortletPaul thank you

im definitely getting dupes as i have a id number in the link and im getting more then one of the same returned.. so far i cant track t down.
0
 
PortletPaulfreelancerCommented:
ok, but are you running the EXACT query that yo show in the question?

If not please post that query

"repetition" can be introduced to a query by joins to other tables for example.
0
 
JohnnyAuthor Commented:
accepted as it answered if i had it wrong or not..thank you.

fyi it was a nested problem with how i outputted it for printing i didn't have the <li> inside the do while for the output.

thanks for looking at my code
0
 
PortletPaulfreelancerCommented:
:) no problem, I did only look at the sql, PHP's not really my thing.
Thanks for the grading, Cheers, Paul.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.