Question 326325 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – query to search main table and associative table

I’m trying to create a search function on my website which searches for keywords in a number of columns in my main table, and also any tags which match those keywords linked via an associative table.

I’ve got a query which works for the keywords only, and a query which can find 1 tag, but i’m looking to do both in a single query.

Combining my 2 working queries and I came up with this query

SELECT * FROM flight_trips
WHERE MATCH (destination_country, destination_city, outbound_airport, return_airport, inbound_airport, accommodation_name) AGAINST (? IN BOOLEAN MODE)
AND status = ?
UNION
SELECT * FROM flight_trips
INNER JOIN trip_tags ON trip_tags.trip_id = flight_trips.id WHERE ".$tagsql." AND flight_trips.status = ? ORDER BY added_timestamp DESC

$tagsql is built using each keyword and adding " OR "

This query fails with the error The used SELECT statements have a different number of columns, which i’m assuming is because the JOIN adds the columns and they don’t add up.

I want to be able to search for example "beach alicante" and find all results which match either "beach" or "alicante" in the MATCH clause, and search my tags table for "beach" and "alicante", and any records linked to those tags. (beach would be a tag linked to a record, alicante would be in destination_city in the main table).

I cannot get my head around this and would appreciate any help! I’ve searched and searched but I don’t actually know what i’m searching for to find the answer to this.

flight_trips

INSERT INTO `flight_trips` (`id`, `destination_country`, `destination_city`, `destination_currency`, `outbound_airport`, `airline_depart`, `return_airport`, `airline_return`, `inbound_airport`, `outbound_takeoff`, `outbound_landing`, `inbound_takeoff`, `inbound_landing`, `flight_cost`, `overnight_stay`, `accommodation_cost`, `accommodation_name`, `airport_transfer_type`, `airport_transfer_cost`, `airport_transfer_duration`, `trip_details`, `rating`, `user_name`, `user_email`, `added_timestamp`, `approved_timestamp`, `status`) VALUES
(2, 'Spain', 'Alicante', 'Euro', 'London Gatwick', '', '', '', 'Alicante Airport', 1699423800, 1699429200, 1699481700, 1699487100, 45.00, 0, 0.00, '', 'bus', 8, 60, '', 2, 'Someone', '[email protected]', 1699313293, 0, 'approved');

tags table

INSERT INTO `tags` (`id`, `name`) VALUES
(6, 'beach');

Table to link tags with trips

INSERT INTO `trip_tags` (`id`, `trip_id`, `tag_id`) VALUES
(1, 2, 6);

Edit: Tried using this new query:

SELECT *, NULL as Col1, NULL AS Col2, NULL AS Col3 FROM flight_trips
                WHERE MATCH (destination_country, destination_city, outbound_airport, return_airport, inbound_airport, accommodation_name) AGAINST (? IN BOOLEAN MODE)
                AND status = ?
                UNION
                SELECT * FROM trip_tags
                JOIN flight_trips ON trip_tags.trip_id = flight_trips.id WHERE ".$tagsql." AND flight_trips.status = ? ORDER BY added_timestamp DESC

I’m no longer getting an error message and I am getting results, but something is wrong retrieving the data from the columns. I appear to get the correct number of results, but while($data = $search_info->fetch_assoc()){ and using $data['column_name'] does not give me the correct information.

How can I correct this?

EDIT: I’ve figured it’s something to do with the dummy columns because my data has shifted 3 columns along. For example, my $data[‘return_airport’] is "Euro" where as that data belongs in destination_currency 3 columns previous. How can I re-align these?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I think i've finally done it! It appears to be working correctly using this query:

    SELECT *, NULL as Col1, NULL AS Col2, NULL AS Col3 FROM flight_trips
WHERE MATCH (destination_country, destination_city, outbound_airport, return_airport, inbound_airport, accommodation_name) AGAINST (? IN BOOLEAN MODE)
AND status = ?
UNION ALL
SELECT * FROM flight_trips
JOIN trip_tags ON trip_tags.trip_id = flight_trips.id WHERE ".$tagsql." AND flight_trips.status = ? ORDER BY added_timestamp DESC

    I had an issue where the data had shifted 3 columns along so it wasn't in the correct columns. This is due to the dummy columns added in the query. My understanding is this happens because in the first SELECT query

    SELECT * FROM trip_tags JOIN flight_trips ON trip_tags.trip_id = flight_trips.id WHERE ".$tagsql." AND flight_trips.status = ? ORDER BY added_timestamp DESC

    I created 3 dummy columns, but the second SELECT query didn't have those, so the data didn't align causing something like this:

    |-----------------------------------------------------------------------|
|DUMMY1 | DUMMY2 | DUMMY3 | id | destination_country | destination_city |
|-------|--------|--------|----|---------------------|------------------|
|   1   | France | Paris  | Euro | London Stansted   |     Easyjet      |
|-----------------------------------------------------------------------|

    I swapped trip_tags and flight_trips like so:

    SELECT * FROM flight_trips JOIN trip_tags ON trip_tags.trip_id = flight_trips.id WHERE ".$tagsql." AND flight_trips.status = ? ORDER BY added_timestamp DESC

    Which added the dummy columns to the end, rather than the start, of the dataset, keeping all my "real" data where it was supposed to be.


  2. 0

    The error means you have different number of columns being returned in the first select then the second select. You can’t union different number of columns and names in a union.

    A possible solutions is to add 3 dummy columns to your first union at the end of the *. By doing this you will also need to declare the columns in the second select.

    Example:

    SELECT *, NULL As trip_tag_id, NULL As Trip_ID, NULL AS Tag_ID FROM flight_trips
WHERE MATCH (destination_country, destination_city, outbound_airport, return_airport, inbound_airport, accommodation_name) AGAINST (? IN BOOLEAN MODE)
AND status = ?
UNION
SELECT ft.*, T.id AS trip_tag_id, T.Trip_ID, T.Tag_ID  FROM flight_trips ft
INNER JOIN trip_tags T ON trip_tags.trip_id = flight_trips.id WHERE ".$tagsql." AND flight_trips.status = ? ORDER BY added_timestamp DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search