CODEWITHSUNDEEP
phpmysql
ryanc1256
ryanc1256

Reputation: 570

php search a mysql string results

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)

Upvotes: 0

Views: 128

Answers (6)

jtheman
jtheman

Reputation: 7491

You can use the like operator to match the pattern:

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

Upvotes: 0

Hearaman
Hearaman

Reputation: 8726

Try this

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

Upvotes: 0

ibtarek
ibtarek

Reputation: 795

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

Upvotes: 1

F.P
F.P

Reputation: 17831

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!

Upvotes: 5

Lobo
Lobo

Reputation: 4147

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.

Upvotes: 0

Najzero
Najzero

Reputation: 3202

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.

Upvotes: 0

Related Questions