php search a mysql string results

Question

guys just a quick question. I have a string in a mysql row resembling this

'google.co.nz, stackoverflow.com, facebook.com, grubber.co.nz'

and so on... I would like to search all the rows in the table and check how many times 'facebook.com' shows up in all the rows that are like the ones above so to clarify

my rows look like this

-- id -- user -- likes 
   1      bob     facebook.com, google.co.nz, grubber.co.nz, stackoverflow.com

and i would like to check how many times facebook.com shows up in the whole table (in every row)

is it comma separated value? i mean you have columns like this, ID, User and Likes (which is csv)? — John Woo
– John Woo, Commented Sep 7, 2012 at 9:46
@knittl yer i know, the stucture is crap it's just for a bit of testing — ryanc1256
– ryanc1256, Commented Sep 7, 2012 at 9:57
The problem is that many programs written as a simple prototype end up going into production. It's not a good idea to write bad code even when you're "just testing an idea", because before you know it your bad DB design will end up being the master database and you won't be able to fix it. — SDC
– SDC, Commented Sep 7, 2012 at 10:00

F.P · Accepted Answer · 2012-09-07 09:52:16Z

5

Assuming every user can only like the same page once, this should work:

SELECT COUNT(*)
FROM table
WHERE likes REGEXP '[[:<:]]facebook.com[[:>:]]'

PS: Normalize your table, you will run into serious trouble with a layout like this in the very near future!

edited Sep 7, 2012 at 9:52

answered Sep 7, 2012 at 9:46

F.P

18k34 gold badges127 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Quentin Over a year ago

Doesn't that match notfacebook.com too?

VDP Over a year ago

Just normalize! csv fields are almost always a bad idea

F.P Over a year ago

Sure, but that's not the question.

VDP Over a year ago

true... that's why I upvoted. (just emphasis-sing your PS :p)

ibtarek · Accepted Answer · 2012-09-07 09:54:39Z

1

You could do it in a lazy fashion, issuing a LIKE SQL query :

SELECT count(*) FROM my_table 
WHERE my_table.likes LIKE '%facebook.com%'

This is really (REALLY) not cpu friendly. Especially with large tables

Otherwise, you could use MySQL fulltext indexes feature.

You may find more details in this article

answered Sep 7, 2012 at 9:54

ibtarek

8055 silver badges16 bronze badges

Comments

Najzero · Accepted Answer · 2012-09-07 09:47:30Z

0

you could use the "LIKE" statement. It goes like this (if you want to know which user likes facebook.com)

SELECT * FROM yourtable
WHERE likes LIKE '%facebook.com%'

Anyway, please consider storing the likes in a 1:n table construct and not as a string

By that you have a a table storing all like-able sites, a user table and a user-likes table storing the assignment.

answered Sep 7, 2012 at 9:47

Najzero

3,22220 silver badges18 bronze badges

Comments

Lobo · Accepted Answer · 2012-09-07 09:48:20Z

0

Other option. Get the row data in a variable and then use the function preg_match_all (http://www.php.net/manual/es/function.preg-match-all.php).

This function returns the number of occurrences of the regular expression passed in the first parameter.

answered Sep 7, 2012 at 9:48

Lobo

4,1578 gold badges40 silver badges69 bronze badges

Comments

Ed Heal · Accepted Answer · 2012-09-07 09:55:03Z

0

Try this

select count(likes) from yourTable where likes like '%facebook.com%'

edited Sep 7, 2012 at 9:55

Ed Heal

60.3k18 gold badges90 silver badges137 bronze badges

answered Sep 7, 2012 at 9:48

Hearaman

8,78813 gold badges45 silver badges58 bronze badges

Comments

Deanna · Accepted Answer · 2012-09-07 10:04:09Z

0

You can use the like operator to match the pattern:

SELECT COUNT(id) AS occurences  WHERE likes LIKE "%facebook.com%"

edited Sep 7, 2012 at 10:04

Deanna

24.4k7 gold badges76 silver badges162 bronze badges

answered Sep 7, 2012 at 9:47

jtheman

7,5013 gold badges32 silver badges40 bronze badges

Collectives™ on Stack Overflow

php search a mysql string results

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related