CODEWITHSUNDEEP
mysqlstringselectfield
Brandon. G
Brandon. G

Reputation: 23

How would one select words in a string separated by commas in a MySQL field?

I'd like to take data from a field which is a string of words separated by commas and save each of those words to a variable. After which, I'd like to take the stored words and use them to retrieve information.

An example of this would be a field containing the information: "Bob,Sue,Adam,Tim,Hank" My goal is to select each name, using the commas to detect each different name, and save each name to a variable or, more efficiently, an array, then use a "for" command or similar to display relevant info about each person.

I'm using MySQLi commands, which I have been told are poorly documented; however, that is what my book instructed me to use.

Upvotes: 2

Views: 1093

Answers (5)

Balaji Kandasamy
Balaji Kandasamy

Reputation: 4506

You can use find_in_set in MySQL:

assume mysql field "username" has these values : "Bob,Sue,Adam,Tim,Hank"

$name="bob";

QUERY is

  select * from TABLE_NAME where FIND_IN_SET('$name', username);

Upvotes: 4

mhelvens
mhelvens

Reputation: 4313

The explode function the other answers suggest will work fine, but it would be more elegant if you stored these names separately in a second table.

So, where you now have:

  • main: id, names

I suggest:

  • main: id
  • names: main_id, name

You can then retrieve the names belonging to one row with a query such as:

SELECT name FROM names WHERE id = $id

To add a name belonging to a certain id, use:

INSERT INTO TABLE names (main_id, name) VALUES ($id, "$newname")

Upvotes: 2

Mihalis Bagos
Mihalis Bagos

Reputation: 2510

You want to EXPLODE !

For example:

$persons= explode(",", "Bob,Sue,Adam,Tim,Hank");

Upvotes: 0

codefreak
codefreak

Reputation: 7131

$arrayList = explode(',', "Bob,Sue,Adam,Tim,Hank");

checkout http://php.net/manual/en/function.explode.php

Upvotes: 0

Lee
Lee

Reputation: 10603

  1. select the field
  2. $names = explode(',', $thefield);

You now have an array of the values

Upvotes: 0

Related Questions