Hello,
I have been looking at a games website, I have got my games and the categories and tags are stored in the MYSQL database like this,

tag1, tag2, tag3, tag4
cat1, cat2, cat3, cat4

So anyway how could I get either PHP or MYSQL to split them and have the ability to search for all the games with "tag1" or all the games in "cat4".

I know this seems like a complex thing to do but any help would be great.

Thanks in advance.

u mean like:


$query="SELECT * FROM table WHERE tag LIKE '$searchedTag, %' OR tag LIKE '%, $searchedTag, %'"


well I don't know if I understood you fine... gave you a fast solution to what I understood. Hope it helps

Indeed, that would work however there must be a way to split it, for example what if I wanted to do a tag cloud with it?

hmmm tag cloud? explain please

Well if the tags can be split at a MYSQL or PHP level then it could count how many times a tag appears and show the most common ones, wordpress can do this however I am not sure. I could think of splitting into an array however I am not sure how I could do this for about 4000 tag collections and finding a way that would not kill the server.

I guess you should have put the tags in a seperate table

like
id - tag - appeared_count(or whatever)
1 - tag1- X
2 - tag2- Y

then in the games table you will have only ids of tags 1, 2, 3 at the place of tag1, tag2, tag3

that would be better for the database and more easily manageable for the kind of stuff you're willing to do

This is true however since I use a JSON parser to download the tags from the game hosts website so this wasunfortunately not an option otherwise I would have jumped at the idea, however there is still the problem of splitting and finding the tags.

My best guess is:

you should still do the tags table in the database then in PHP



$countsArray=array();
$tags_query=mysql_query("SELECT tag FROM tags");
while($tags_array=mysql_fetch_assoc($tags_query)){
$thisTag=$tags_array["tag"];
$count_query=mysql_query("SELECT COUNT(*) AS countThisTag FROM table WHERE tag LIKE '$thisTag, %' OR tag LIKE '%, $thisTag, %'");
$count_array=mysql_fetch_assoc($count_query);
$thisTagCount=$count_array["countThisTag"];
$countsArray[$thisTagCount]=stripslashes($thisTag);/*Putting the count as the array index which will help in ordering the tags with respect to how much they appear*/
}


EDIT: u'll have a problem with 2 tags that has the same count... still needs some further work I guess

hmm... seems a bit of an odd way of doing it really.

that's basically an idea, but at last u'll have to put the tags in an array with their counts then order them in a way or another... this:

$countsArray[$thisTagCount]=stripslashes($thisTag);

is odd (remarked after posting)...

don't know trying to think of a solution... can't you keep everything the same way and add a tags table as I said in post #6 then each time you add a tag to a game you increment the tag's count in the database table. This way you rid your server of very much processing and memory usage

just don't change anything in your db structure... all you have to do is add the table and increment the count of tags, then get the count the ordering with respect to that table.

The database isn't optimized.

If you want to use clouds, each tag should be on its own. Use a table to store tags with the game id.

Peace,

I can attest to the design of saving each tag as a row in a "Tags"
database and then having a cross-reference table that links "whatever"
to tags. At my last job we implemented a tagging system, and at first
we considered having a text row on the table for tags and then parsing
them in PHP.

This was an utter disaster.

Just doing things like getting the number of occurrences of a given
tag would result in tons of data being pulled from MySQL and a good
amount of processing in PHP. Having tags as a separate table means
that doing things like looking up a tag count is a simple as:


SELECT COUNT(*) FROM `posts2tags` WHERE tag_id = `1`;


And looking up which tags a given post (or whatever the data you are
tagging is) would only consist of some simple SQL JOINS. Also then
would mean that your database would be bearing the bulk of the
processing instead of your scripts, which is always good because I can
assure you that MySQL can process data a lot faster than PHP.

I know it sounds like a lot of work and re-factoring, but you will
save yourself a lot of trouble down the road.

I think I can safely say I have no idea what you are all talking about.

What are these "tags"?

And what is the problem?