how to get single value from comma separated array when fetching data from MYSQL

Question

I am entering data in data base like this:

ID | Name | Email   | Place
1  | Khan | abc@... | Page1, Page2, Page10

Now I am fetching data like this :

$conn = mysqli_connect($servername, $username, $password, $dbname);
$sql = "SELECT * FROM story Where Place='Page2' Limit 1";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);

But unfortunately it show me nothing :(

I am uploading this Place row in comma separated form, now I want it show me the data where I select place.

What do you want to be returned? Do you want any value where Page2 is in an comma separated array? — Reisclef, Aug 11 '15 at 20:45
There is no column named `phone` according to sample data. You could use `like` if `phone` = `place`. You probably should separate out each one into its own record. — chris85, Aug 11 '15 at 20:46
you didn't even include the code where you try to display anything — developerwjk, Aug 11 '15 at 20:47
This value will show on different pages, like If I want to show this data on page 1 (Home Page), Page 4 and Page 7 I will select page1, page4, page7. Now on page1, page 4 and page 7 it will show. — Hamza Khan, Aug 11 '15 at 20:48
kind of looks like you want `SELECT * FROM story Where Place like '%Page2%' Limit 1";` — , Aug 11 '15 at 20:48
NOT working for me :( `mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given` — Hamza Khan, Aug 11 '15 at 20:52
data really should be normalized and place be given its own table — , Aug 11 '15 at 20:52
Is the column `phone` or `place`? I'd consider using a regex for this so Page20 doesn't equal Page2. With regex it'd be something like `Page2(,|$)`.. — chris85, Aug 11 '15 at 20:53

score 3 · Accepted Answer · edited May 23 '17 at 11:57

3

Please consider normalizing your data. Is storing a delimited list in a database column really that bad? The design you have here will make you crazy if your application actually grows.

In the meantime, you want to use column LIKE '%value%' in place of column = 'value'. Beware: the performance of this kind of query is very bad and can't be improved.

EDIT Queries using column = 'value' or even column LIKE 'value%' can exploit indexes in the database management system. It's possible to look up values in an index in O(log(n)) time. That permits even a vast table, with billions of rows, to be accessed efficiently. However, a query containing column LIKE '%value%' has to scan through all the data, row by row, exhaustively looking for matches, in O(n) time. If you have a few hundred rows you won't notice this poor performance. But if your table grows you will definitely be sorry.

Try this query.

     SELECT * 
       FROM table
      WHERE column LIKE CONCAT('%' , 'Page2', '%')
      LIMIT 1

I suggested the use of CONCAT() so the query can easily be modified to use bind parameters. For example, CONCAT('%' ,? , '%')

Another edit. If you have Page1, Page100, Page101 you'll get a lot of false positive matches on Page1. In MySQL FIND_IN_SET() can do a search of a comma-separated string.

     SELECT * 
       FROM table
      WHERE FIND_IN_SET('Page2', column) <> 0
      LIMIT 1

This will not work if you have spaces after your commas. Page1,Page2,Page3 is fine, but Page1, Page2, Page3 is no good. We can patch that problem like so.

     SELECT * 
       FROM table
      WHERE FIND_IN_SET('Page2', REPLACE(column, ', ', ',')) <> 0
      LIMIT 1

But now things are getting out of hand. What if you have two spaces after a comma someplace? Or three?

This too is an unoptimizable search.

edited May 23 '17 at 11:57

Community

1
1

answered Aug 11 '15 at 20:52

O. Jones

103,626
17
118
172

amazing ! Clap for you. Its work for me. But why this query is bad and can't be improved ? – Hamza Khan Aug 11 '15 at 20:56
or if there is to many values like 200+ or I want to get only one like above. So can I use this query ? – Hamza Khan Aug 11 '15 at 20:59
4

what happens at page20 21 etc – Aug 11 '15 at 21:00
nice link Ollie. can't even get to 1NF :> – Drew Aug 11 '15 at 21:00
@user3396968 `LIKE %page2%` will match `page2` or `page20` or `page21` or `page200` etc – Aug 11 '15 at 21:05
@Dagon, this strategy falls apart very quickly indeed. I suppose one could use RLIKE and a regular expression. But, grumble, – O. Jones Aug 11 '15 at 21:05
2

@OllieJones we all know the *real* answer is to normalise – Aug 11 '15 at 21:06
I can't get that, how can I normalize my data? – Hamza Khan Aug 11 '15 at 21:08
with a junction table. Someone wanna take a whack at it ? – Drew Aug 11 '15 at 21:09

score 1 · Answer 2 · answered Aug 11 '15 at 21:14

A move toward data normalization.

A proposed junction table (eradicates the Place CSV column):

create table story_place_jnc
(   -- one row per story/place combo
    id int auto_increment primary key,
    storyId int not null,
    place varchar(20) not null  -- you size this thing

    -- perhaps 2 FK's go here (1 to story, 1 to place):

    -- Unique composite key on (storyId,place):
);

As Patsy said in Monty Python's Holy Grail, "It's only a model."

Who knows if you even have a place table :>

how to get single value from comma separated array when fetching data from MYSQL

2 Answers2