searching mySQL by first char(s) in fields with php

Question

I have a database that's setup in the following way

id  coach   team                event              status
14  test    8,7,12,13,15        4,1,2,14,4         2      
13  test    8,12,13,14,15,16    1,2,8,16,16        3  

What i need to do, is search for rows where the first number in the "event" column matches the query. They are separated by commas, but it can be 1 or 2 or 3 digits. Im sure this is possible, just not sure how or where to begin.

Answer 1

$sql = "SELECT * FROM table_name WHERE event LIKE '" . $query . "',%'";

Answer 2

This will select all rows where the first number in event is 1:

SELECT * FROM `tableName` WHERE event LIKE '1,%';

Answer 3

You could use SUBSTRING_INDEX to get the first value, something like this:

SELECT * FROM table_name WHERE SUBSTRING_INDEX( event, ',', 1 ) = 'value'

With this approach you can use a prepared statement with a placeholder for the search value. Also works fine if there is just one number in the event column, i.e. no commas present to match against.

Answer 4

You'd be better of by changing your database scheme. Storing fields with lists of ids is not very handy.

Make extra tabels to make the links. For example:

coach_team
id coach_id team_id
1  14       7
2  14       8
3  14       12
4  14       13

Than you can use queries like:

SELECT * FROM table_name WHERE id in
    (SELECT coach_id FROM coach_team WHERE team_id = 1)

(This of course also applies to events.

Extra information: http://en.wikipedia.org/wiki/Database_normalization

Answer 5

Have you considered normalizing your database? Isn't it a pain to work with a database, in which a field may contain an arbitrary number of arbitrarily formatted values? As a side effect (haha), it will solve the problem you've described in your question.

Example database schema:

create table whatever (
    id int not null auto_increment primary key,
    coach varchar(64),
    status int
)

create table teams (
    id int not null auto_increment primary key,
    name varchar(255)
)

create table events (
   id int not null auto_increment primary key,
   name varchar(255)
)

create table whatever_teams (
    id int not null auto_increment primary key,
    whatever_id int,
    team_id int
)

create table whatever_events (
    id int not null auto_increment primary key,
    whatever_id int,
    event_id int
)

I want to apologize in advance for the obvious lack of sql-injection-enabled code, that can be always found in the questions and answers under the tags "php" and "mysql".

Answer 6

I strongly recommend you to change your database schema because from my experience, sooner or later, you have to change it to serve all your needs in the future. SHould do it now be4 too late