comma seperated rows

[CanMan2004]

Hi all

Am kicking myself tryin to get this to work.

Basically I have a database which holds users details, each row have an id number, full users name etc and there is also a field called

areasallowed

within this field is a bunch of numbers seperated by commas, for example

12, 56, 78, 101, 120, 167, 200, 271, 325, 427

and that's that.

I then have a front end search form which I have a input field called

areanamefield

the user can enter a number value into, for example

23

and then perform a search.

The current query I use is

[code]SELECT * FROM members WHERE areaname = $_GET['areanamefield'][/code]

but at the moment, I cant get a result back for a number stored within the commas seperated field, so if I search for.

56

then it will only return rows which have just

56

stored in it, if

56

appears within a commas seperated value, then it wont return it.

Can anyone help?

I have tried the query

[code]SELECT * FROM members WHERE areaname IN ($_GET['areanamefield'])[/code]

But it didnt seem to like that.

Any help would be great

Thanks in advance

Dave

[neoform]

you're going to want to not store your data like that in your database..

each number should be it's own row in a table, not comma delimited.

[craygo]

make your search like this

[code]SELECT * FROM members WHERE areaname LIKE '%,56 %'[/code]

Ray

[kenrbnsn]

You have the arguments reversed in your query. Try:
[code]<?php $q = "SELECT * FROM members WHERE "' . $_GET['areanamefield'] . "' in areasallowed"; ?>[/code]

Ken

[craygo]

if you are going to search for more than one area seperated by a comma then you can use this. I put a test form together to test. You can change to fit your needs

[code]<?php
if(isset($_POST['submit'])){
/**** important stuff ****/
$areaname = explode(", ", $_POST['area']);
foreach($areaname as $key => $val){
$sql = "SELECT * FROM members WHERE areasallowed LIKE '%$val%'";
  $res = mysql_query($sql) or die (mysql_error());
    while($r = mysql_fetch_assoc($res)){
    echo $r['name']."--".$r['areasallowed']."<br>";
    }
}
/**** END STUFF ****/
} else {
print '<form name=test method=POST action="'.$_SERVER['PHP_SELF'].'">
      Area: <input type=text name=area><br />
      <input type=submit name=submit value=submit>';
      }?>[/code]

This will take the area's entered, seperated by commas, and divide it up and search each area.
Only thing you really need is the area between the important stuff :)

Ray

[CanMan2004]

thanks everyone