Jump to content

Archived

This topic is now archived and is closed to further replies.

CanMan2004

comma seperated rows

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
make your search like this

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

Ray

Share this post


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

Ken

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.