find if instance/phrase exists in a row

[designalex]

guy plz help me out with this.

If i have a table, in the table is a column called veh_status, the row data will be varchar storing one or many comma seperated numbers
so
veh_status could be 1,2,3

each number represents an id to the relevant vehicle statuts name and description etc.

My question is:
How do i do a mysql query to select a row where veh_status contains 1 or many numbers.
for example, i want to select all cars with veh_status 1 and 2 and 3 or only veh_status that is equal to 3.

I have no code to show at the moment as i am only looking at optoins fornow.
Im using the codeigniter framwork but a standard mysql query will work fine and ill find the appropriate codeigniter equivelent.

Thanks in advance

[rch1231]

Hello,

If you are trying to be able to find which vehicles have only one specific status then using like in your where statement will do the trick

where status like 1% 
or
where status like %2%

If you are going to want to be able to find which vehicles have a status of 1 and 3 the I would do one of two things:

1. Have a seperate field for each status and make it 0 or 1 (0 not this status 1 has this status)

   Where status1 = 1 and status3 = 1

2. Make your statuses 2 to the (status-1) power and store the sum (1=1, 2=2, 3=4, 4=8, 5=16, 6=32, 7=64, etc.) Status one only is stored as 1, status one (1) and three (4) are stored as 5 (1+4), one (1) and two (2) are stored as 3 (1+2), 2 (2) and 5 (16) are stored as 18 (2+18). Then your where statement just becomes the sum you are looking for. and finding statuses that contain a specific number (lets say you want all things that have a status of 2 included)

   where status in (2,6,10,18,34,66)
   or for status contains 3 it would be:
   where status in (3,6,11,19,35,67)
   

And finding all vehicles with only a status of 2 and 4 would be:

where status = 10

This may seem a wild way to store the numbers but it is only limited by the size of the integer variable chosen to store the number and you can add statuses on the fly without adding fields and having to update the data.

Hope that helps and is not to confusing.

[designalex]

Buddy you got my head spinning with the second one!

The first option wont work but is the following not a solution?

SELECT *
FROM tablename
WHERE
vehicle_status IN ('2','4','5');

a real query that would be common in the application is something like

SELECT *
FROM tablename
WHERE
vehicle_status IN ('2','4','5')
AND
veh_make = '1'
ORDER BY da ASC

I suppose i have to try it out, but you input will be appreciated

[smantscheff]

This is bad table design. veh_status should be a separate table with a 1:n relation between vehicles and veh_status.

[designalex]

veh_status is a seperate table, but its also the name of a column on the vehicle table, so a vehicle can have pne or many status's

for example, vehicle "superFastCar" has a status of 1 and 3, then a check in the veh_status table what 1 and 3 is.

[rch1231]

What smantscheff is suggesting is really more like a true sql solution. You store statuses in a seperate table that contains the original table_id, status number so in the first table you have the original information and you join to the second table to find out what statuses are active.
Your statustable would be something like

CREATE TABLE `statustable` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `tablenameid` int(11),
    `status_id` smallint(4),
    PRIMARY KEY (`ID`),
    KEY `tablenameid` (`tablenameid`)
    );

Create a record for each status that the original table was assigned and link them using the original table id

Select * from tablename 
left join statustable on tablename.id = statustable.tablenameid 
and statustable.status in (2,4,5)

would get all vehicles with a status of 2 or 4 or 5.