Solved

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

Posted on 2013-11-01
5
333 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

746 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

13 Experts available now in Live!

Get 1:1 Help Now