Hello all.
Decided it was time to get to grips with PHP and on my quest have bumped into this little query, wonder if anyone can help.
I have a field within a database that contains a list of numbers seperated by commas.
eg: 1,2,7,9,100, etc, etc.
Now... I want to be able to select records based on whether they have a particular number within this field.
eg:
SELECT pid, pnumbers, pname from tbl_users WHERE 2 IN(pnumbers)
Like that but I've only ever done IN statements the other way round.
Is this possible?
Thanks,
JonHmmm.... sorry about putting this in the wrong forum. Must say though. This PHP forum seems a little hidden!
Or do people just not know the answer to this?
JonOriginally posted by jonirvine
Hello all.
Decided it was time to get to grips with PHP and on my quest have bumped into this little query, wonder if anyone can help.
I have a field within a database that contains a list of numbers seperated by commas.
eg: 1,2,7,9,100, etc, etc.
Now... I want to be able to select records based on whether they have a particular number within this field.
eg:
SELECT pid, pnumbers, pname from tbl_users WHERE 2 IN(pnumbers)
Like that but I've only ever done IN statements the other way round.
Is this possible?
Thanks,
Jon
just a guess:
SELECT pid, pnumbers, pname FROM tbl_users WHERE pnumbers LIKE "%2%"
try that...Thanks for the reply.
Unfortunately I've considered that but that would include all numbers with a 2 in, not just 2.
eg: 2, 12, 20, 21, 22, 23, ...... 62, etc.
Thanks for the reply though.n/m..that wouldn't work..hmm..I will look into it...Originally posted by jonirvine
SELECT pid, pnumbers, pname from tbl_users WHERE 2 IN(pnumbers)
Is this possible?
Thanks,
Jon
I beleive you hav eto have something in there and not just a variable.
try this:
SELECT pid, pnumbers, pname FROM tbl_users WHERE pnumbers LIKE "2"
the % are wildcards so if you don't want them don't use them.good your here scoutt....couldn't he do something like substr on it to look for 2 as a sub string er...that would also probably return 22,222,etc....42,...etc.well you could. there is a lot of ways it can be done. one easy way is to grab all of them and split them up at the comma and do a compare. all depends on if he wants to hard code it or not.true..if you exploded it and checked the array you could probably just search the array values.so....wouldn't something like this work scoutt?
$query = mysql_query("SELECT pid, pnumbers, pname from tbl_users");
while($row = mysql_fetch_array($query))
{
$exploded = explode(","$row["pnumbers"]);
}
and then just check the array for 2's?yup, once you have them in an array you can do just about anythingCheers for the replies guys.
After looking into this more I realised that although there were a couple of methods, none of them were going to be as efficient as they could so I went for an alternative database structure instead.
Should of know really... must have been the lack of sleep.
Thanks again,
Jonyou are quite correct in that redoing the db structure would be more efficent.But I am glad to have at least given u some workaround ideas if u had needed them.
Decided it was time to get to grips with PHP and on my quest have bumped into this little query, wonder if anyone can help.
I have a field within a database that contains a list of numbers seperated by commas.
eg: 1,2,7,9,100, etc, etc.
Now... I want to be able to select records based on whether they have a particular number within this field.
eg:
SELECT pid, pnumbers, pname from tbl_users WHERE 2 IN(pnumbers)
Like that but I've only ever done IN statements the other way round.
Is this possible?
Thanks,
JonHmmm.... sorry about putting this in the wrong forum. Must say though. This PHP forum seems a little hidden!
Or do people just not know the answer to this?
JonOriginally posted by jonirvine
Hello all.
Decided it was time to get to grips with PHP and on my quest have bumped into this little query, wonder if anyone can help.
I have a field within a database that contains a list of numbers seperated by commas.
eg: 1,2,7,9,100, etc, etc.
Now... I want to be able to select records based on whether they have a particular number within this field.
eg:
SELECT pid, pnumbers, pname from tbl_users WHERE 2 IN(pnumbers)
Like that but I've only ever done IN statements the other way round.
Is this possible?
Thanks,
Jon
just a guess:
SELECT pid, pnumbers, pname FROM tbl_users WHERE pnumbers LIKE "%2%"
try that...Thanks for the reply.
Unfortunately I've considered that but that would include all numbers with a 2 in, not just 2.
eg: 2, 12, 20, 21, 22, 23, ...... 62, etc.
Thanks for the reply though.n/m..that wouldn't work..hmm..I will look into it...Originally posted by jonirvine
SELECT pid, pnumbers, pname from tbl_users WHERE 2 IN(pnumbers)
Is this possible?
Thanks,
Jon
I beleive you hav eto have something in there and not just a variable.
try this:
SELECT pid, pnumbers, pname FROM tbl_users WHERE pnumbers LIKE "2"
the % are wildcards so if you don't want them don't use them.good your here scoutt....couldn't he do something like substr on it to look for 2 as a sub string er...that would also probably return 22,222,etc....42,...etc.well you could. there is a lot of ways it can be done. one easy way is to grab all of them and split them up at the comma and do a compare. all depends on if he wants to hard code it or not.true..if you exploded it and checked the array you could probably just search the array values.so....wouldn't something like this work scoutt?
$query = mysql_query("SELECT pid, pnumbers, pname from tbl_users");
while($row = mysql_fetch_array($query))
{
$exploded = explode(","$row["pnumbers"]);
}
and then just check the array for 2's?yup, once you have them in an array you can do just about anythingCheers for the replies guys.
After looking into this more I realised that although there were a couple of methods, none of them were going to be as efficient as they could so I went for an alternative database structure instead.
Should of know really... must have been the lack of sleep.
Thanks again,
Jonyou are quite correct in that redoing the db structure would be more efficent.But I am glad to have at least given u some workaround ideas if u had needed them.