CODEWITHSUNDEEP
php
Tao
Tao

Reputation: 85

Get stored id's using iteration

I please need some help: I have this database, which has this fields with their respect values:

agency_id | hostess_id
3         | 12-4-6
5         | 19-4-7
1         | 1

In hostess_id are stored all hostesses ids that are associated with that agency_id but separated with a "-"

Well, i login as a hostess, and i have the id=4 I need to retrieve all the agency_id which contain the id=4 , i can't do this with like operator.. i tried to do it by saving the hostess_id row to an array, then implode it, but i can't resolve it like this.

Please, please any idea?

Upvotes: 1

Views: 50

Answers (2)

Henrik Mühe
Henrik Mühe

Reputation: 429

First, let me say that I absolutely agree with @JvdBerg on that this is terrible database design that needs to be normalized.

Let's think for a minute though, that you have no way of changing the database layout and that you must solve this with SQL, an inefficient but working solution would be

select agency_id from tablename where
  hostess_id LIKE '4-%' OR
  hostess_id LIKE '%-4-%' OR
  hostess_id LIKE '%-4'

if you were searching for all agencies with hostess id 4. I build this on sqlfiddle to illustrate more thoroughly http://sqlfiddle.com/#!2/09a52/1

Mind though, that this SQL statement is hard to optimize since an index structure for substring matching is rarely employed. For very short id lists it will work okay though. If you have ANY chance at changing the table structure, normalize your schema like @JvdBerg suggested and look up database design and normal forms on google.

Upvotes: 1

JvdBerg
JvdBerg

Reputation: 21856

You should change your database design. What you are describing is a typical N:N relation

Agencies:

agency_id | name
3         | Miami
5         | Annapolis
1         | New York

Hosteses

Hostes_id | name
4         | Helen
12        | May
19        | June

AgencyHostes

Hostes_id | agency_id
4         | 1
4         | 3
4         | 5
12        | 1
12        | 3
19        | 1

Upvotes: 1

Related Questions