CODEWITHSUNDEEP
phpmysql
mrpatg
mrpatg

Reputation: 10117

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

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.

Upvotes: 0

Views: 118

Answers (6)

vodkhang
vodkhang

Reputation: 18741

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

Upvotes: 1

shylent
shylent

Reputation: 10086

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".

Upvotes: 3

martin clayton
martin clayton

Reputation: 78115

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.

Upvotes: 2

Inspire
Inspire

Reputation: 2060

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

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

Upvotes: 2

Jimmy Shelter
Jimmy Shelter

Reputation: 1540

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

Upvotes: 2

Jan Hančič
Jan Hančič

Reputation: 53931

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

Upvotes: 1

Related Questions