Search a column for a portion of a string.

[K_N]

Say I have a column of data, where each entry is a series of numbers delimited with a comma like such:

 

row1: 23,5,72,187,96

row2: 88,321,7,23,40

row3: 34,5344,222,45

etc...

 

and I want to search the entire column and select only rows that contain 23 in them with an SQL query. What SQL function can I use to achieve this?

 

I know I could just return all results and explode the array and search in php, but this is a query that's going to be run a few hundred times a minute by different users, and something tells me doing that wouldn't be too friendly on the ol' processor.

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

 

However, if you really want your queries to work well, don't store the data as a comma separated list in the first place. Store each value as a separate row associated with who the data belongs to.

[K_N]

That doesn't seem like it'd do what I want. From what I've figured I need something like

SELECT * FROM table WHERE colname LIKE '%,23,%'

but, using that will ignore any that have 23 at the beginning of the list.

 

 

Unfortunately I can't do that, each row in this database needs it's own set with a comma separated list. Otherwise I'd end up with an incredibly large redundant database.

 

[ManiacDan]

Find_in_set is really what you want.  As long as the result is greater than zero, it's in the set.  Zero means it's not in the set.

 

Really, what you want is to make this database structure properly, as was already suggested.

 

-Dan

[K_N]

Find_in_set is really what you want.  As long as the result is greater than zero, it's in the set.  Zero means it's not in the set.

 

Really, what you want is to make this database structure properly, as was already suggested.

 

-Dan

So how would I structure a query to select all the data in that row if it's in the set? Can you give me an example based on my post?

 

 

And as I already replied to that suggestion, doing it "properly" would make my database about 500% of it's current size, which I don't need. Unless you know of a better way to include a column of data within a table cell? Attached is an example of the DB structure I'm working with, where "associated" refers to the ids of products from another table, and each row entry must have a list of all associated products.

 

 

[attachment deleted by admin]

[ManiacDan]

Doing it properly means making this single column its own table.  It won't increase the size of your database at all, and it will actually increase the speed.  Look into normal forms.

 

Also, read the MySQL manual page you've already been given for the proper syntax.  WHERE find_in_set('123', column) != 0

[K_N]

Doing it properly means making this single column its own table.  It won't increase the size of your database at all, and it will actually increase the speed.  Look into normal forms.

 

Also, read the MySQL manual page you've already been given for the proper syntax.  WHERE find_in_set('123', column) != 0

But wouldn't I have to make a seperate row for each and every relation between product and accessory?

 

Ah, that's what I was looking for, I didn't realize I could put the column name there.

[fenway]

Doing it properly means making this single column its own table.  It won't increase the size of your database at all, and it will actually increase the speed.  Look into normal forms.

 

Also, read the MySQL manual page you've already been given for the proper syntax.  WHERE find_in_set('123', column) != 0

But wouldn't I have to make a seperate row for each and every relation between product and accessory?

Indeed.

[ManiacDan]

Yes, you would, that's the whole point of a cross reference table.  There is one entry for every relationship.  That way, you can easily find anything you're looking for.

 

Databases don't get tired, bored, or overwhelmed.  I've run MySQL installations with more than 4 billion rows in each database.  Creating a cross-reference table of a few hundred thousand rows isn't going to be an issue, and it will make queries like this so much easier.

 

Plus, have you thought about what would happen if you wanted to delete a relationship?  Currently, you have to select the row, modify the list in-line, make sure it's still valid, check to see if the row has been updated in the mean time, then insert the new row value.  The correct way, you just delete from the cross reference table where the IDs match the relationship you want to clear.

 

-Dan

[K_N]

I see what you're saying. It does make more sense that way.

 

I'm still very unfamiliar with the limits of a database, as the largest script I've run so far has had a total of 200 weekly users.

[ManiacDan]

This forum runs PHP/MySQL. 

[K_N]

This forum runs PHP/MySQL.

I know this. But I have no idea what the database for this forum looks like.

[ManiacDan]

Look into normal forms.  Your database should be properly indexed and in third normal form.  If you're having speed issues and index tweaking doesn't fix it, you may need to make denormalized search tables, but that's a discussion for once you hit tens of millions of rows.

 

-Dan