How do I run a query like this?

I got a problem with a SQL query. Just can't get my head around it.

I have a table like this

Table A
id = is a simple auto incrementing counter
text = these are keywords separated with | eg.

James Bond|007|License to kill

url = a website address

and another table

Table B
id = again a simple auto incrementing counter
text = a long piece of text

Now I want to run a query where I can select all entries from TABLE A where one or more keywords are found in the text of TABLE B.

Hope you understand what I mean.

PLEASE HELP!!

Thanks.

 

Hmm.. you'll have to use the WHERE and LIKE instructions.

Maybe something like 'SELECT * FROM tablea, tableb WHERE tablea.text LIKE %tableb.text%'

Can't remember if the percentage operators are correct, try taking them out if it doesn't work.

 

Thanks I will give it a try

 

try something like:

SELECT b.id, a._id
FROM a b
LEFT  JOIN text b on a.id = b.id
WHERE b.text LIKE '%somestring%'

Code (markup):

Along those lines.

Also I am not sure what language you are using for your output but I would go about this a bit different.

Cleanse and search for the text then got to table A once you have found the "id" in table b.

So simply put:

<?php
#write escshell funtion to block sql injection attemtps
$q="select * from tableb where text like esc('%$string%')";
$r=mysql_query($q);
while($row = mysql_fetch_array($r)){

$bid=$row['id'];
$bcontent=$row['text']
   
$q2="select * from tablea where id='$bid'";
$r2=mysql_query($q2);

   while($row2 =mysql_fetch_array($r2)){
   $acontent=$row2['text'];
   echo "B ID: $bid B Content: $bcontent  A Content: $acontent";
   }
}
?>

PHP:

Very simple crude code but just to give you proof of concept.

 

Thanks for all the help so far. i got nothing to work so far. Maybe I should explain a little bit more about what I am trying to do.

I am writing a script (mixture between JavaScript, PHP and MySQL) which can automatically add links to keywords on a page. It is designed for bloggers mainly. So they don't have to go away and find the urls. Its all added automatically.

Now I got the javascript to work but there will be hundreds of urls stored in the system. To run through all of them would lock down the website. Soooooo I want to do the following.

I have another table which will hold a copy of the page where links should be added. I remove all the html stuff and some other things that would cause problems and store it together with the url and the date of that page.

Now bloggers might wright in different ways like lets assume a blogger writes about military jets. He could write "MiG-29","mig 29 "fulcrum", "mig-29 fulcrum" and more. So I have a table where I simply store the url and then have a list with loads of possible words or phrases for that url.

All I need to do now is compare the keywords with the page text and get a list of keywords that are found on the website. So I can create javascript on the fly with just the replacements for the keywords that are found on the site.

Hope this helps you to understand what I am trying to do.

 

Ok I realised that this is not really working. So I am making a small change to the database structure. Instead of storing all keywords in the same field using a separator I am now storing each keyword on its own.

I was hoping to be able to do something like this

SELECT tablea.text,tablea.url FROM tablea,tableb WHERE tableb.text LIKE "%tablea.text%"

But that doesn't work. What am I doing wrong?

 

try SELECT * first instead of just those rows

 

nothing is being returned. Any other suggestion you can give?

 

Stephen,

Copy and paste my PHP code above.

Adjust the field names to yours then work from there.

The code above all though a bit dirty, will do exactly what you want.

 

Not quite. You have a variable here.

But I do not have a variable. Instead of the variable I want to match it against the tablea.text.

 

Right I got it working now. I had to use two queries and its not as efficient as I wish it would have been but for now it is fine. if I see that it is too slow then I will try to tweak it better.

Thanks for all you help guys.

 

can you try
SELECT tablea.text,tablea.url FROM tablea, tableb where tableb.text LIKE (select text from tablea)

 

Throws the following error