MySQL - Searching in a multiple value field

Question

In my database table I have the following fields:

Table Supplier:

• id
• name
• vehicles

A supplier can have multiple vehicles. The field 'vehicles' will store multiple values. At the moment I am delimiting the values on a 'pipe' symbol, although this can be changed to a comma if need be.

On my front-end form I have a checkbox list - a user can select multiple vehicles. The back end script needs to do a search and bring back all suppliers that contain any of the specified vehicle id's.

So in other words we are searching with multiple values in a multiple value field.

The checkbox list name is vehicle_type[] and will end up in the $_POST array as (for example):

Array 
( 
    [0] => 1 
    [1] => 4 
    [2] => 6 
)

Is this possible to do? I could obviously do this using a join table but ideally I would like to do it this way. I am using PHP as my scripting language if that helps.

Answer 1

The field 'vehicles' will store multiple values. At the moment I am delimiting the values on a 'pipe' symbol, although this can be changed to a comma if need be.

Please don't do that. Storing delimited data in a text field is no better than storing it in a flat file. The data becomes unqueryable.

You want a nice, happy, normalized database.

CREATE TABLE Suppliers (
    supplier_id INTEGER PRIMARY KEY,
    ...
);

CREATE TABLE Vehicles (
    vehicle_id INTEGER PRIMARY KEY,
    ...
);

CREATE TABLE Supplier_Vehicles (
    supplier_id INTEGER NOT NULL REFERENCES Suppliers(supplier_id),
    vehicle_id INTEGER NOT NULL REFERENCES Vehicles(vehicle_id),
    UNIQUE KEY(supplier_id, vehicle_id)
);

-- Grab all the Vehicles for Supplier 39
SELECT Vehicles.*
  FROM Vehicles, Supplier_Vehicles
 WHERE Supplier_Vehicles.supplier_id = 39
   AND Supplier_Vehicles.vehicle_id = Vehicles.vehicle_id

The checkbox list name is vehicle_type[] and will end up in the $_POST array as (for example) [...] Is this possible to do?

It's very possible, and is a good idea. Try it and find out how well it works.