Solved

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

Posted on 2013-11-01
5
334 Views
Last Modified: 2013-11-01
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

0
Comment
Question by:Johnny
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39618276
>>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
 

Author Comment

by:Johnny
ID: 39618288
@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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618324
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
 

Author Closing Comment

by:Johnny
ID: 39618347
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618363
:) no problem, I did only look at the sql, PHP's not really my thing.
Thanks for the grading, Cheers, Paul.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now