Jump to content


Photo

comma seperated rows


  • Please log in to reply
5 replies to this topic

#1 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 16 October 2006 - 05:46 PM

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

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

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

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

But it didnt seem to like that.

Any help would be great

Thanks in advance

Dave

#2 neoform

neoform
  • Members
  • PipPipPip
  • Advanced Member
  • 241 posts
  • LocationMontreal

Posted 16 October 2006 - 05:48 PM

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.
Newsique.com Social News Network

#3 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 16 October 2006 - 05:53 PM

make your search like this

SELECT * FROM members WHERE areaname LIKE '%,56 %'

Ray


#4 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 16 October 2006 - 05:55 PM

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

Ken

#5 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 16 October 2006 - 06:16 PM

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

<?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>';
       }?>

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

#6 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 16 October 2006 - 07:18 PM

thanks everyone




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users