[SOLVED] Problem with LIKE statement

[defeated]

I want to return rows where a reference is in an array or references.

 

Database looks like:-

Tag                    count                  refs

this tag                3                        1 , 3 , 1234

another tag          2                        1234 , 12345

gumdrops            3                        123, 1234, 12345

appledrops            3                        123 , 12345 , 123456

 

$ref='1234';
$query=mysql_query("SELECT * FROM mytable
WHERE refs LIKE '$ref'") or die(mysql_error());
$num_rows=mysql_num_rows($query);
echo "<p>".$num_rows." contain the reference ".$ref.".</p>";

That code doesn't return any rows because the column refs is not exactly like 1234 in any instance.

 

I can't use

LIKE '%ref%'

because that would return the row 'appledrops' which doesn't actually contain the ref 1234

 

Any suggestions?

[kickstart]

Hi

 

Long term, redesign the database so that the refs are on a seperate table, with one row per ref per tag.

 

Short term if there is a space either side of the ref you could include the spaces in your like statement.

 

This thread dealt with a similar issue someone was having.

 

All the best

 

Keith

[defeated]

:-\ My head hurts.  I get that I need the three table schema.  I just can't get my head around the queries.  Haven't done any joins before....

 

Also, mine is going to be a little more complicated because I have to deal with expiring references and tags(if there are no more associated references for it).  I also need to be able to edit tags per reference.  It's doing my head in.

oh well, off to the tutorials with me I suppose.  Thanks for the direction Kickstart.  Your short term solution would work.  But I may as well learn how to do it properly.

[kickstart]

Hi

 

Joins are OK. If you struggle then plenty here can help.

 

Do the references expire, or does each occurance of a reference expire at a different time? Eg, in your example would 123 expire for all tags, or would 123 expire expire at different times for gumdrops than for appledrops?

 

All the best

 

Keith

[defeated]

123 expires with all tags.

 

If a topic ref 123 was tagged appledrops, gumdrops, some other tag

 

and then topic 123 expires (one month after it was posted)

 

then there is one less occurrences of each of the tags appledrops gumdrops and some other tag.  If the number of occurrences was then equal to 0 then the tag would be deleted.

 

To further complicate it, the topic can be deleted before it expires and the tags can be edited at any time.

 

If the topic expires it can be re-posted which would mean that the deleted information would have to be re-instated.  This means that I need another table of deleted tag/reference relationships to store deleted but possibly re-usable tags.

 

I will handle the expiring topics with a Cron Job running in the middle of every night.

 

I can't help but think that it's not worth it for the ability to have a tag cloud on my site.  Unfortunately now that I've started and it's kicking my ass it has become a challenge and I am unable to walk away from it.

 

The worst part is that once I have it finished nobody will be any the wiser to all the work and complications that have gone into it.  They will go , "oh a tag cloud.... naff!".

[kickstart]

Hi

 

Would have a table of refs and a table of tags. Both would have fields for expiry dates on them and also a deleted marker (no point moving them to another table, just mark them as deleted).

 

Then just have a table joining them together, so you have an ID, ref and tag:

 

Something like this:-

 

TagTable

Id, TagText, Deleted

1, This Tag, 0

2, Another Tag, 0

3, gumdrops, 0

4, appledrops, 0

 

RefsTable

Id, RefText, Deleted

1, 1, 0

2, 3, 0

3, 123, 0

4, 1234, 0

5, 12345, 0

6, 123456, 0

 

TagRefsTable

Id, TagId, RefId, Deleted

1, 1, 1, 0

2, 1, 2, 0

3, 1, 4, 0

4, 2, 4, 0

5, 2, 5, 0

6, 3, 3, 0

7, 3, 4, 0

8, 3, 5, 0

9, 4, 3, 0

10, 4, 5, 0

11, 4, 6, 0

 

SELECT *

FROM TagTable

INNER JOIN TagRefsTable ON TagTable.Id = TagRefsTable.TagId

INNER JOIN RefsTable ON TagRefsTable.RefId = RefsTable.Id

WHERE TagTable.Deleted = 0

AND TagRefsTable.Deleted = 0

AND RefsTable.Deleted = 0

 

All the best

 

Keith

[defeated]

Keith you're a bloody genius! 

That's saved me hours.  I hadn't thought of adding a deleted column.

 

I'm still reading left joins at the moment. Haven't got to inner joins yet. 

 

I have a feeling that this is going to be another one of those turning points where everything I've done before seems very basic and a whole lot of things suddenly seem a lot more possible.

 

Thanks for all your help.  To be honest I thought that my last post was more of a demonstration of "hopeless situation" than an actual request for good advice.  Your post was a very nice surprise!

[kickstart]

Hi

 

Surprised that what you are reading is dealing with outer joins (ie LEFT OUTER JOIN or RIGHT OUTER JOIN) before dealing with normal inner joins (often just referred to as a JOIN).

 

You could easily add an expiry date column to those tables as well and check that as well as the deleted column.

 

All the best

 

Keith