Mysql result to limit output

Question

I have a problem realizing some output to echo a list of results that comes from an Array. I would like to create a Live search engine that runs a query by the help of keyup-function by using AJAX.

Everything works fine when the output will be echoed for every match that is listed in the table. Now I would like to to combine all entries that are duplicates.

The code is like:

$search_term = $_POST['search_term'];

$where = "";

$search_term = preg_split('/[\s]+/', $search_term, -1, PREG_SPLIT_NO_EMPTY);
$total_search_terms = count($search_term);

$total_search_term = 0;

foreach ($search_term as $key=>$value) {

    $total_search_term = $total_search_term + 1;

    if ($total_search_term === 1){
        if (is_numeric($value) ){
            $where .= "(`a` LIKE '%$value%')";
        } else {
            $where .= "(`b` LIKE '%$value%')";
        }
    }else if ($total_search_term > 1){
        $where .= " AND ";

        if (is_numeric($value) ){
            $where .= "(`a` LIKE '%$value%')";
        } else {
            $where .= "(`b` LIKE '%$value%')";
        }
    }   
}
$duplicate = $db->query("SELECT a, b, COUNT(*) counter
                        FROM `table`
                        GROUP BY a, b
                        HAVING COUNT(*) > 1
                        ");

$check = $duplicate->fetch_assoc();
$query = $db->query("SELECT a, b FROM table WHERE $where");
$result = $query->num_rows;

if ($result !== 0 ){
    echo '<li id="hit">There are $result results!</li>';

    while ($row = $query->fetch_assoc() ) {

    echo '<li>', 
    $row["a"], 
    ' ', 
    $row["b"],  
    '</li>';
    }
} else {
        echo '<li id="hit">no result!</li>';
    }

To give an example of the output:

There are 3 results!
12345 New 
12345 New
56789 Chicago

And thats how it should be:

There are 3 results!
12345 New (2x)
56789 Chicago

So the table is:

a     |    b
12345   New 
12345   New
56789   Chicago

Thanks alot.

hello, sorry. a is zpicode and b is city. i updated the code. thanks — bonny
– bonny, Commented Apr 16, 2013 at 10:47

parascus · Accepted Answer · 2013-04-16 11:36:53Z

1

I thought of something like this:

$query = $db->query("SELECT a, b, COUNT(*) counter FROM `table` WHERE ".$where." GROUP BY a, b");
$result = $query->num_rows;
if ($result !== 0 ){
  $resultSizeQuery = $db->query("SELECT COUNT(*) counter FROM `table` WHERE ".$where);
  $resultSize = $resultSizeQuery->fetch_assoc();
  echo '<li id="hit">There are '.$resultSize["counter"].' results!</li>';
  while ($row = $query->fetch_assoc() ) {
    echo '<li>'.$row["a"].' '.$row["b"];
    echo ($row["counter"] > 1 ? " (".$row["counter"]."x)" : "");
    echo '</li>';
  }
} else {
    echo '<li id="hit">no result!</li>';
}

Replacing all lines from "$duplicates = ..." to the end it should do it's work. Just give it a try because sometimes the step before the problem should be thought over.

Regards

parascus

answered Apr 16, 2013 at 11:36

parascus

1,2691 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bonny Over a year ago

Alright, I had to work around a bit but all in all that works great. Thank you very much for help. Have a nice day.

parascus Over a year ago

You're welcome and if you've got questions to the code for understanding just post them.

parascus · Accepted Answer · 2013-04-16 10:58:15Z

0

first of all, your statement will return just 1 line with New York and the column counter will have 2. Chicago is missing because counter is just 1. So I think your result looks like: Ther are 1 results! 12345 New York

If you want to have "3 results" jsut do 2 queries, one for the number of rows (just leave out the group and having clause, also don't ask for a and b). So you get the output: There are 3 results!

Next you have to omit the having clause for getting all rows (also those without duplicates). You could write something like: echo ($row["a"].' '.$row["b"].($row["counter"] > 1 ? " (".$row["counter"]."x)" : "")

I hope this helps.

Regards

Parascus

answered Apr 16, 2013 at 10:58

parascus

1,2691 gold badge11 silver badges15 bronze badges

3 Comments

bonny Over a year ago

hello, I'm sorry, I did not posted real code. the results will be displayed and counted the right way. I edited the table to NEW instead of NEW YORK. So this is not the problem. the problem is just the way of displaying and limiting the duplicates.

parascus Over a year ago

Hi bonny, well now I'm a bit confused. You've got two different results (all lines in $query and all lines with duplicates in $check) but you are not using the one with the duplicates. I think getting the two results and combining them is a bit complicated (two many fetching of the right rows of each other). By using "SELECT a, b, COUNT(*) counter FROM table WHERE $where GROUP BY a, b" you should have all what you need in a single row (except the total number of rows) and can use the last statement I gave in my answer. Or am I missing an important point? Regards, parascus

bonny Over a year ago

hello and thanks for answering. your right. $duplicate is not used at the moment. i can`t touch the first query because it is still working, and counts all results. so it would be better to add a second query that looks for duplicates. i already tried using foreach loops to handle this but i still stuck. the main goal should be something like: if $result !== 0 then look if there is a duplicate foreach result. if there is a duplicate then list the result only once and add the number of duplicates behind, if there is no duplicate list the result once.

Collectives™ on Stack Overflow

Mysql result to limit output

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related