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

Php – SQL search many conditions must return one row

Problem is that i get a whole bunch of users, that does not match the search string no matter what i search. And it seems like there is baseline of users that is allways passed. I cant figure out what exactly it is finding, but the lenght of the return of users vary depending on what i search.

I need to return all the rows

WHERE orders.restaurant_fk = :restaurant_id (must allwayss be true) OR users2.user_name LIKE :search 

OR users2.user_last_name LIKE :search OR users2.user_address LIKE :search

By that i mean: if i search for a string value i want to get an order that has a single match in the search query or multiple.
The important is that i cannot get same order twice.
Forexample if the same user has a email called "[email protected]" and a user_name called "jens". it should only be returned once

Another important thing is that the orders must contain the specific restaurant_id.

<?php

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

session_start();
try {
    $json = file_get_contents('php://input');
    $data = json_decode($json);

    $db = _db();
    $restaurant_id = $data->restaurant_id;
    $search = $data->search;

    $q = $db->prepare(
        'SELECT DISTINCT restaurants.restaurant_id, orders.*, users2.user_name, users2.user_last_name, users2.user_address, users2.user_city, users2.user_zip
        FROM orders
        JOIN users2 ON orders.user_fk = users2.user_id
        JOIN restaurants ON orders.restaurant_fk = restaurants.restaurant_id
        WHERE orders.restaurant_fk = :restaurant_id
        OR users2.user_name LIKE :search
        OR users2.user_last_name LIKE :search
        OR users2.user_address LIKE :search
        ORDER BY orders.created_at DESC'
    );
    $q->bindValue(':restaurant_id', $restaurant_id);
    $q->bindValue(':search', "%{$search}%");
    $q->execute();
    $orders = $q->fetchAll();


    echo json_encode(['orders' => $orders]);
} catch (Exception $e) {
    try {
        if (!$e->getCode() || !$e->getMessage()) {
            throw new Exception();
        }
        var_dump($e);
        http_response_code($e->getCode());
        echo json_encode(['info' => $e->getMessage()]);
    } catch (Exception $ex) {
        http_response_code(500);
        echo json_encode($ex);
    }
}

Tables:
I am using case insensitive MYSQL database.

Orders Table

enter image description here
Users2 Table

enter image description here

Tags:

2

Answers


  1. 0

    Here’s what I changed:

    1. The subquery (SELECT * FROM orders WHERE restaurant_fk = :restaurant_id) o filters the orders by restaurant_id before joining with users2.
    2. The GROUP BY o.order_id ensures that each order is listed only once, even if there are multiple matches in the user’s details.
    SELECT restaurants.restaurant_id, o.*, u.user_name, u.user_last_name, u.user_address, u.user_city, u.user_zip
FROM (
    SELECT * FROM orders
    WHERE restaurant_fk = :restaurant_id
) o
JOIN users2 u ON o.user_fk = u.user_id
WHERE u.user_name LIKE :search
OR u.user_last_name LIKE :search
OR u.user_address LIKE :search
GROUP BY o.order_id
ORDER BY o.created_at DESC
    Login or Signup to reply.
  2. 0

    Sounds like you need some AND OR conditions

    SELECT
  restaurants.restaurant_id,
  orders.*,
  users2.user_name,
  users2.user_last_name,
  users2.user_address,
  users2.user_city,
  users2.user_zip
FROM orders
JOIN users2 ON orders.user_fk = users2.user_id
JOIN restaurants ON orders.restaurant_fk = restaurants.restaurant_id
WHERE orders.restaurant_fk = :restaurant_id
  AND (
    users2.user_name LIKE :search
    OR users2.user_last_name LIKE :search
    OR users2.user_address LIKE :search
  )
ORDER BY orders.created_at DESC;

    You can alternatively move some conditions to the joins

    SELECT
  restaurants.restaurant_id,
  orders.*,
  users2.user_name,
  users2.user_last_name,
  users2.user_address,
  users2.user_city,
  users2.user_zip
FROM orders
JOIN users2 ON orders.user_fk = users2.user_id
JOIN restaurants
  ON orders.restaurant_fk = restaurants.restaurant_id
 AND orders.restaurant_fk = :restaurant_id
WHERE (
    users2.user_name LIKE :search
    OR users2.user_last_name LIKE :search
    OR users2.user_address LIKE :search
  )
ORDER BY orders.created_at DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search