Retrieve all data from SQL array LIKE a given value

Question

I'm trying to retrieve all the data id from a database where their tags(array) is like a given value. This is what I have done so far...

$new_string = 'nice phone';
$construct = mysql_query("SELECT tag_array, name, id FROM details 
WHERE tag_array LIKE $new_string%")
or die("<p>died 20: $construct<br>" . mysql_error());

while($getThis = mysql_fetch_array($construct)){
echo $getThis['id'].'<br />';
echo stripslashes($getThis['name']).'<br />';
}

It doesn't work ATALL. Could you please point me to the right direction? I'm really struggling!!

i added the single quotes, sorry that was typo. It doesn't match the words with all word in the array. on the first word. Any ideas on how to fix this? — Buki
– Buki, Commented May 16, 2011 at 8:18

enoyhs · Accepted Answer · 2011-05-16 08:24:17Z

1

You should put $new_string in quotes.

NOTE It is very bad practice and you should always escape all variables you are passing to SQL. You should really read up on SQL injection and other security issues.

Also if you want to match $new_string anywhere in tag_array (which you most likely want), you need to add dollar sign in front of it too. You can read up more at MySQL reference manual.

So in the end:

"SELECT tag_array, name, id FROM details WHERE tag_array LIKE '%" . mysql_real_escape_string($new_string) . "%'"

edited May 16, 2011 at 8:24

answered May 16, 2011 at 8:12

enoyhs

2,0592 gold badges16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

enoyhs Over a year ago

@Konerak: Thanks. I am aware of that, but simply forgot to escape.

Buki Over a year ago

i added the single quotes, sorry that was typo. It doesn't match the words with all word in the array. on the first word. Any ideas on how to fix this?

Buki Over a year ago

Just to be clear. Array1 is = (boys, girls, tables, chairs). word to search is tables. it doesnt echo Array1 because it only compares with first word.

enoyhs Over a year ago

Not sure I understand you correctly, but you need to add % in front of and at the end of searched word, if you want to find this word anywhere in tag_array. As now it only searches tag_arrays that start with your word.

Adam Arold · Accepted Answer · 2011-05-16 08:26:04Z

0

You should sanitise the data before putting it in the query like:

  $new_string = "blah...; DROP TABLE tag_array; #";
  $sql = mysql_real_escape_string($new_string);
  $sql = "SELECT tag_array, name, id FROM details WHERE tag_array LIKE %'$sql'%"

This is not enough though it just helps preventing sql inject, consider using regular expressions to clean the data. If you don't yet know about regexp check out this site: regexp info. It helped me mutch.

edited May 16, 2011 at 8:26

answered May 16, 2011 at 8:18

Adam Arold

30.8k25 gold badges124 silver badges226 bronze badges

1 Comment

Adam Arold Over a year ago

Please add a comment about your problem (downvoting).

Collectives™ on Stack Overflow

Retrieve all data from SQL array LIKE a given value

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related