how to remove duplicate results from mysql query in php?

Question

I'm trying to provide a search function for my PHP site. The users should be able to search rows and columns for their desired query, like "search engine". I tried this php code:

<?php
    $con = @mysqli_connect("localhost", "root", "", "search");
  $output = '';

  if(isset($_POST['search'])) {
    $search = $_POST['search'];
    $search = preg_replace("#[^0-9a-z]i#","", $search);

    $query = mysqli_query($con, "SELECT * FROM sites WHERE (site_title LIKE '%$search%' or site_link LIKE '%$search%' or site_keywords LIKE '%$search%')") or die ("Could not search");
    $count = mysqli_num_rows($query);

    if($count == 0){
      $output = "There was no search results!";
    print $output;

    }else{

      while ($row = mysqli_fetch_array($query)) {

    $site_keywords = $row ['site_keywords'];
    $site_tit = $row ['site_title'];
    $site_link = $row ['site_link'];

    $output .='<div> '.$site_tit.''.$site_keywords.''.$site_link.'</div>';
    print $output;

      }

    }
  }
?>

Everything works just fine but I'm getting duplicate results. I've read a lot of answers and here's I've done so far: I used SELECT DISTINCT * FROM .... and also SELECT DISTINCT site_id FROM .... but didn't return any result. I tries GROUP BY but they didn't remove the duplicates and returned nothing. I also applied PHP array_unique() on $row = mysqli_fetch_array($query) in where condition, but it also didn't return any result. If I can do this by using only SQL please or I have to remove duplicates by PHP like using a function, please guide me. Thanks in advance.

Wait a sec. You get duplicates and so you switch to SELECT DISTINCT site_id FROM... and you get NOTHING? I don't believe it. At any rate, @manassehkatz has definitely found an issue in your PHP that may be more of a culprit than your sql. — JNevill
– JNevill, Commented Jul 9, 2018 at 19:26
Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. — tadman
– tadman, Commented Jul 9, 2018 at 19:29
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Jul 9, 2018 at 19:30
thank you @tadman, I'll look into it, seems very interesting. But eventually I have to get mysql query right. — MrJay
– MrJay, Commented Jul 9, 2018 at 19:39

manassehkatz-Moving 2 Codidact · Accepted Answer · 2018-07-09 19:26:54Z

4

Move:

print $output;

outside the loop.

Right now $output is being printed every time through the loop. If your results are A,B,C then your output will be A,A,B,A,B,C (with divs, etc.)

answered Jul 9, 2018 at 19:26

manassehkatz-Moving 2 Codidact

3,0542 gold badges17 silver badges27 bronze badges

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

8 Comments

MrJay Over a year ago

Thank you my friend, I feel like such an idiot! This worked wonderfully, now I don't have to set my hair on fire!

manassehkatz-Moving 2 Codidact Over a year ago

@MrJay - I've had things in loops before that didn't belong. As long as we learn from our mistakes and move onward & upward, we'll all be OK.

MrJay Over a year ago

I'm too familiar with these minor fails that ruin everything, I'm quite good at python but when you're on new territories things just get confusing! Thank you again @manassehkatz and my best wishes to you.

MrJay Over a year ago

yes @manassehkatz , python is almost too easy compared to many programming languages. I learned c++ and java years ago but python makes everything so simple that have made me lazy. C++ drives me insane now, with all that pointers and such. First I wanted to use Django or Flask for my website but in the end PHP won because most of my website is in HTML and JavaScript and didn't need to use python that much... It seemed PHP is a more reasonable choice, given that I had studied it too some time ago. sometimes these minor adjustments in the language you're working on takes time getting used to.

MrJay Over a year ago

Of course you're right. PHP is much more hosting friendly. It's the go to language for website development. But using Django or Flask with a cheap VPS also works fine, just takes more effort. There are far more CVEs for PHP than Flask and Django but considering it's a much more popular option and it's much older than python frameworks, I assume bugs would be found and fixed faster... This is based on this assumption that they have security in mind more than developers working on newer frameworks...

|

Collectives™ on Stack Overflow

how to remove duplicate results from mysql query in php?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related