Question 305594 in PHP Question posted in
Official manual can be found here.

sql search in PHP, with join, returns identical rows

I am trying to search my database for a wide range of values. And get all the users that match the criteria but not get the same user if it machtes multiple critearia.
I think the problem in my code is that:

If i search for "john" and the user has an email "[email protected]" and his name is john, i will get the user twice.

I have tried SELECT DISTINCT but it still returns hell of alot of duplicates

Some of the users are owners of a restaurant so i need to be able to find the user by searching the restaurants name also.

I could just remove duplicates from the result before i sent it as JSON but i would hope that sql could handle this for me

<?php
require_once __DIR__ . '/../_.php';

try {

    $json = file_get_contents('php://input');
    $data = json_decode($json);
    $search = $data->search;

    if (empty($search)) {
        echo json_encode(['info' => 'Search string is empty']);
        exit;
    }

    $db = _db();

    $q = $db->prepare("SELECT users2.*, restaurants.*
        FROM users2
        JOIN restaurants
        WHERE users2.user_email LIKE :user_email 
        OR users2.user_name LIKE :user_name
        OR users2.user_last_name LIKE :user_last_name
        OR users2.user_address LIKE :user_address
        OR users2.user_zip LIKE :user_zip
        OR users2.user_city LIKE :user_city
        OR users2.user_id = restaurants.fk_user_id AND restaurants.restaurant_name LIKE :restaurant_name
    ");

    $q->bindValue(':user_email', "%{$search}%");
    $q->bindValue(':user_name', "%{$search}%");
    $q->bindValue(':user_last_name', "%{$search}%");
    $q->bindValue(':user_address', "%{$search}%");
    $q->bindValue(':user_zip', "%{$search}%");
    $q->bindValue(':user_city', "%{$search}%");
    $q->bindValue(':restaurant_name', "%{$search}%");

    $q->execute();
    $result = $q->fetchAll();

    echo json_encode($result);
} catch (Exception $e) {
    $status_code = !ctype_digit($e->getCode()) ? 500 : $e->getCode();
    $message = strlen($e->getMessage()) == 0 ? 'error - ' . $e->getLine() : $e->getMessage();
    http_response_code($status_code);
    echo json_encode(['info' => $message]);
}

Database design:
The user can only have one restaurant, some users dont have restaurants.
Restaurants have fk_user_id which is related to user_id
enter image description here

enter image description here

Tags:

2

Answers


  1. 0

    A good option could be for you to share your database design, in that way, we can get you a better answer, based on the information that you gave us:

    In a SQL join clause, if you don’t specify the "ON", the SQL engine will combine every row from one table (user) to every row from the other table(restaurants), so the user A on your user table will be combined with each restaurant, and so on, with the others users, which could lead to duplications of rows.

    If the table restaurants has a column for the owners, you can point to it to filter the matching values:

    SELECT users2.*, restaurants.*
FROM users2
JOIN restaurants
ON users2.id = restaurants.owner_id
// your where clauses

    I hope this can help you.

    Login or Signup to reply.
  2. 0

    Try modifying the SQL query to use a LEFT JOIN with a subquery that groups restaurant names for each user, ensuring each user is returned only once, even if they match multiple search criteria across different fields or are associated with multiple restaurants.

       $q = $db->prepare("
        SELECT DISTINCT 
            users2.*
        FROM 
            users2
        LEFT JOIN (
            SELECT 
                fk_user_id, 
                GROUP_CONCAT(restaurant_name SEPARATOR ', ') AS restaurant_names
            FROM 
                restaurants
            GROUP BY 
                fk_user_id
        ) AS grouped_restaurants ON users2.user_id = grouped_restaurants.fk_user_id
        WHERE 
            users2.user_email LIKE :search 
            OR users2.user_name LIKE :search
            OR users2.user_last_name LIKE :search
            OR users2.user_address LIKE :search
            OR users2.user_zip LIKE :search
            OR users2.user_city LIKE :search
            OR grouped_restaurants.restaurant_names LIKE :search
    ");
    
    $q->bindValue(':search', "%{$search}%");
    
    $q->execute();
    $result = $q->fetchAll();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search