PHP / MySQL preg_match words beginning with hash symbol

Question

I've devised a tagging system for my Website where tags beginning with a hash (#) function differently to those without. I'm trying to extract all hash tags from my database and load them into an array:

$keywords = mysql_query("SELECT Keywords FROM Tags WHERE Keywords LIKE '#%'") or die("Query failed with error: ".mysql_error());
$stack = array();
while ($row = mysql_fetch_array($keywords))
{
    $wrds = $row['Keywords'];
    $val = preg_match("/\b\#\w+(?=,|\b)/", $wrds, $matched);
    while (!empty($matched))
    {
        $val = array_pop($matched);
        if (array_search($val, $stack) === FALSE)
        {
            array_push($stack, $val);
    }
    }
}

The MySQL query returns the following:

+------------------------+
| Keywords               |
+------------------------+
| #test1, test           |
| #test1, #test2, #test4 |
| #test3, #est5          |
| #test3                 |
+------------------------+

I want an array like the following:

Array(
  [0] => #test1
  [1] => #test2
  [2] => #test4
  [3] => #test3
  [4] => #est5
  )

What am I doing wrong?

Why are you putting serialized values in an RDBMS? This is [bad design](http://en.wikipedia.org/wiki/Database_normalisation). It *will* [come back](http://stackoverflow.com/questions/7201158/join-tables-with-comma-values) [to haunt you](http://stackoverflow.com/questions/7212282/is-it-possible-to-query-a-comma-separated-column-for-a-specific-value) in the future even if you get this to work. — NullUserException, Sep 01 '11 at 12:22

score 0 · Answer 1 · answered Sep 01 '11 at 12:12

0

try this regexp: preg_match("/^\#\w+$/", $wrds, $matched);

answered Sep 01 '11 at 12:12

k102

7,861
7
49
69

Thanks but this just returns '#test3'. – Miles Sep 01 '11 at 13:12
i didn't get it, sorry. what is the reason of storing strinhs this way? – k102 Sep 01 '11 at 13:39

score 0 · Answer 2 · answered Sep 01 '11 at 13:40

0

As @NullUserException said, it is bad design fo putting serialized values in RDBMS, doing this just make things complicated.

And for your question, you can try another way:

$result = array_filter(explode(',', $wrds), function($a){ return $a[0]==='#' } );

answered Sep 01 '11 at 13:40

xdazz

158,678
38
247
274

I'm aware that it's bad design but I have not managed to find a way to efficiently retrieve multiple rows for one row from another table. Basically, I have the thread info in another table, each thread has one id. I can't retrieve 15 rows from a 'Tags' table and join it to one row from a 'Topic' table within a while loop without continuously querying the tags table. – Miles Sep 01 '11 at 14:55
I have this in a while loop: "SELECT LEFT(Topics.Body, 500), Topics.Title, Topics.id AS tID, Topics.Timestamp, Topics.MemberID, Users.id, Users.FirstName, Users.LastName, Tags.Keywords, COUNT(Comments.id) AS NumberOfComments FROM Topics LEFT JOIN Users ON Topics.MemberID = Users.ID LEFT JOIN Comments ON Topics.id = Comments.TopicID LEFT JOIN Tags ON Topics.id = Tags.TopicID GROUP BY Topics.id ORDER BY Topics.Timestamp LIMIT $plim" but if there are 7 fields in the tag table matching that topic only the first will be retrieved. – Miles Sep 01 '11 at 15:01
@Miles, RE: combining row data. You can. See (e.g.) [PHP & MYSQL: using group by for categories](http://stackoverflow.com/questions/2977213/php-mysql-using-group-by-for-categories), [MySQL (or PHP?) group results by field data](http://stackoverflow.com/questions/3638952/mysql-or-php-group-results-by-field-data), [how do I display records sorted by time and grouped by date?](http://stackoverflow.com/questions/6435496/php-mysql-how-do-i-display-records-sorted-by-time-and-grouped-by-date). – outis Sep 01 '11 at 21:40
... [build multilevel associative array from database](http://stackoverflow.com/questions/6921360/php-build-multilevel-associative-array-from-database-sorting-cities-by-state-f). The `GROUP_CONCAT(DISTINCT ...)` solution from the 2nd question is acceptable because the stored column only contains simple values, so can be operated on by the normal DB operations (comparisons, joins). – outis Sep 01 '11 at 21:41
@outis: Thank you so much for these links! I've managed to get it to work without serialized values. – Miles Sep 02 '11 at 23:37

score 0 · Accepted Answer · answered Sep 01 '11 at 14:32

use preg_match_all :

$arr = array('#test1, test','#test1, #test2, #test4','#test3, #est5','#test3');
$stack = array();
foreach($arr as $wrds) {
    $val = preg_match_all("/#\w+(?=,|$)/", $wrds, $matched);
    while (!empty($matched[0])) {
        $val = array_pop($matched[0]);
        if (array_search($val, $stack) === FALSE)
        {
            array_push($stack, $val);
        }
    }
}
print_r($stack);

output:

Array
(
    [0] => #test1
    [1] => #test4
    [2] => #test2
    [3] => #est5
    [4] => #test3
)

PHP / MySQL preg_match words beginning with hash symbol

3 Answers3