-2

Please help me am a beginner:

I want to select data from sql column let's say image_description which contains data in form of tags. e.g one entry in the column is "Moscow, lights, water, Russia, night". The other table entries in this column contain data in a similar format.

I want to use keywords to select the data and put them in a constant say $tag. So the $tag variable may contain at one point e'g "Moscow, lights, water, Russia, night" from above.

How can I formulate my SELECT statement to fetch any row which includes any word in my $tag variable.

Don Louis
  • 61
  • 1
  • 7
  • a simple select query can result in proper data......select * from table_name where column_name like '%your_string%'; – Ankush Bist Jan 16 '17 at 09:45
  • As juergen said, [normalize](http://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization) your tabels. I highly recommend creating another table where you can store single tags belonging to a image. The table would include `image_id` and `tag`. When an image has multiple tags, there would be multiple entries in this table, one for each tag. – kscherrer Jan 16 '17 at 09:56

2 Answers2

1

Not a good idea to use mulitple values in single column if matching with whole tag.. but if you are using the same way, try something like this

$string1= yoursting_var.",";
$string2= ", ".yoursting_var.",";
$string3=", ".yourstring_var;
select * from table_name where (column_name like '$string1%' || column_name like '%$string2%' || column_name like '%$string3');..

if you use simple like %string%, it will create problem in comparing some words which comes in other words as well. like "water" and "ate", if you search with "ate" it will return both.

//for mulitple words

$words_arr;  // this is your words array contain any nummber of words

$com_string="";

foreach($words_arr as $words){
    $string1= $words.",";

    $string2= ", ".$words.",";

    $string3=", ".$words;

//make query using loop for all words with AND/OR  condition    
    $com_string .= " AND (column_name like '$string1%' || column_name like '%$string2%' ||column_name like '%$string3')";

}

select * from table_name where 1 $com_string;
Tariq
  • 218
  • 2
  • 13
  • Thank you Tariq..What if I don't know the number of words in the variable. Like the way you used 3 words in your example. It is generated by a system so number of words may vary..sometimes "word1,word2,word3" or sometimes "word 1, word 3" or even "word 1, word3, word7, word 4" etc – Don Louis Jan 16 '17 at 10:14
  • it will be easier if u change your table structure.. but in this case you need some trick $words_arr; // this is your words array contain any nummber of words $com_string=""; foreach($words_arr as $words){ $string1= $words.","; $string2= ", ".$words.","; $string3=", ".$words; //make query using loop for all words with AND/OR condition $com_string .= " AND (column_name like '$string1%' || column_name like '%$string2%' ||column_name like '%$string3')"; } select * from table_name where 1 $com_string; – Tariq Jan 16 '17 at 10:25
  • @KaiLean just append them to your query string; something along the lines of ```queryString = 'SELECT * from blabla... WHERE condition1; queryString+='OR condition2;';``` – Tudor Leustean Jan 16 '17 at 10:25
  • check answer for multiple word hv just edited.. might this will help you – Tariq Jan 16 '17 at 10:27
0

This should do the trick:

SELECT * FROM your_table WHERE image_description LIKE 'your_tag%';

Or if you want to check for multiple tags, just add OR conditions, like this: SELECT * FROM your_table WHERE image_description LIKE 'your_tag%' OR image_description LIKE 'your_second_tag% OR image_description LIKE 'your_third_tag%;

Check this question out;

Community
  • 1
  • 1
Tudor Leustean
  • 517
  • 3
  • 15