How to check one value in field looking like (1,2,3)

mhenke

I need to check if 1 or 2 or 3, or a combination, is present in a field.

Values are separated by a comma, so the field looks like this: 1,2,3.

How do I do this?

Trying to figure this out for a day now 🙁

TheDefender

SELECT * FROM table WHERE $value IN(field)

I believe this is the syntax you want... This nugget is difficult to find in the MySQL manual...

mhenke

Hi Defender THX, but it doesn't seem to work though 🙁

I have:

  [code=php]                <?

dbsystem();
$search = "SELECT name FROM producer WHERE dvd IN type order by name";
$result =mysql_query ($search);
while($row = mysql_fetch_array($result)){
$producer=$row[0];
echo("<option value=\"prod_search.php?producer=$producer&type=dvd\">$producer</option>");
}
mysql_close;
?>
[/code]

The field 'type' looks like this: dvd,magazine,book.

Nothing gets returned with the above script...

devinemke

$search = "SELECT name FROM producer WHERE type IN('dvd','magazine','book') ORDER BY name";

mhenke

I corrected a syntax error that I made, I put brackets around the field:

$search = "SELECT name,type FROM producer WHERE dvd IN (type) order by name";

It now returns records that ONLY have 'dvd' in the field, no records from that have 'dvd, magazine, book' in the field 🙁

mhenke

I now use:

SELECT name,type FROM producer WHERE type LIKE '%dvd%' order by name.

It works 🙂

mhenke

Couldn't get that to work either DIVINEMKE.

Using the above now, although it doesn't really do what I want, but can work with it.

If anyone knows the proper way to check a value in a string in a field, please let me know.

Can't believe it's so hard to find, as it seems so fundamental. I just stored 3 values from checkboxes in a field (dvd,mag,book) and MySQL should provide an easy way to check if one or more of these values is in the field.

Just a beginner here 🙁

Lars_Berg

It's hard because your database design is completly wrong.

Store each value as a separate record and use an equality comparison.

mhenke

I have a total of 20 types, so I should create 20 different fields?

Isn't this what implode/explode was meant for?

What's an equality comparison?

I just want to see if one value in a string exists...

So, if dvd exists in this string dvd,mag,book,calendar,poster.

devinemke

let's back up here. mhenke, please tell us exactly what your data structure is: table names, field names, field types, etc...

mhenke

DIVENEMKE,

My form looks like this:

<input type="checkbox" name="type[]" value="dvd">DVD
<input type="checkbox" name="type[]" value="mag">Magazine
<input type="checkbox" name="type[]" value="book">Books

It stores the data as a string in field TYPE in the db, which has 2 fields: NAME, TYPE. The stored data looks like: (dvd,mag,book) or (dvd,book) etc.

Now I want to do a query to find all records with say, 'book' as a type.

That's it basically 🙂

mhenke

Oh and TABLE name is 'producer' and FIELD TYPE is VARCHAR

tsinka

Hi,

I think in that case you might need the MySQL regexp functions (slow in some cases) or to change you table structure.

Thomas

mhenke

THX! I will study regular expressions, they look like as if they could provide the solution for the query prob 🙂

About the result prob:

When I search for a name, I get a record returned with the NAME and TYPE values for that one record.

The NAME is one value, the TYPE a string (mag,book,dvd).

If I just want to see if one part of TYPE is present in the string ('book' for instance), how would I do that?

tsinka

Ok,

I didn't completely understand ...

Do you want to check if one of the words in the search string is part (exactly) of the TYPE string (I didn't completely understand what you mean with 'the string') ?

Thomas

mhenke

Thx for helping Thomas, will try to be more clear:

As the field TYPE has data stored as a string ('book,mag,dvd,calendar,etc...') the result from a query will contain this string, for instance 'book,dvd,mag'.

I want to check if 'book' or 'dvd' (for instance) is in the result of my query, so I can let it be followed by an action.

Hope this explains is better?

tsinka

Ok,

just to make sure, TYPE contains e.g. book,dvd,mag and not (book,dvd,mag)

That makes a difference in the following code (shortened):

<?PHP
$word = 'book';
$type = 'book,mag,dvd';
// $type = $row['TYPE'];

if (preg_match("§(^|,)$word(,|$)§i",$type)) {
  echo "$word found";
}

// or something like this if you want to search
// for more than word
if (preg_match_all("§(^|,)(book|dvd|mag)(,|$)§i",$type,$matches)) {
  // print array with matching strings
  // $matches[2] contains the words found
  print_r($matches);
}
?>

mhenke

Yes,

It's book,dvd,mag and not (book,dvd,mag) 🙂

Gonna try it right now but can already see this is it!

Mike

mhenke

THX I can work with this 🙂