Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-11-01
5
Medium Priority
?
351 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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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 49

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

876 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