Exploding PHP

[steveangelis]

I have a database that has multiple entries per field such as one in particular looks like this:

 

6,7,8,9,10,11,100,114

 

Every user has a set of numbers, and not all of them are the same.  For instance some users will have a 6 and some will not in their group, some will have a 110 and some will not.  What I want to do is have a query that will ONLY get me those that have a selected number, say, 6 in the bunch.  I tried this query but it did not work:

 

mysql_query("select * from user where groupids in(6)")

 

I tested it without the where part and it works fine but with it it returns no entries.  Any ideas?

[steveangelis]

Ok I figured out the first part of my problem but I cannot figure out the second.

 

$uquery = mysql_query("select userid,username,membergroupids from user") or die(mysql_error());

while ($uresult = mysql_fetch_array($uquery))
{
$pieces = explode(",", $uresult['membergroupids']);
echo "Officer";
if ($pieces[0] == '32')
{
echo $uresult['username']."<br>";
}
echo "Junior Officer";
elseif ($pieces[0] == '31')
{
echo $uresult['username']."<br>";
}
}

What I am trying to do is get a header above each section of the explosion but it does not work very well.  I get a massive amount of Officer and what not also coming up.  How would I do it so that I only see Officer once and I still get the whole list?

[mikesta707]

My advice would be to make an array of the title's that have already been said, and once something has been echoed, add that entry to the array. You could then check if the title is in the "said" array, and if it isn't echo it out. I can provide a sample if you want.

 

Hope that helps!

[Psycho]

Utterly terrible database design. The "ranks" should be stored in a separate table with a reference back to the parent record. But, if you are going to use this crap, this should work:

 

$uquery = mysql_query("select userid,username,membergroupids from user") or die(mysql_error());

$ranks = array();

while ($uresult = mysql_fetch_array($uquery))
{
    $groupIDs = explode(',', $uresult['membergroupids']);

    if (in_array('32', $groupIDs))
    {
        $ranks['Officer'] = $uresult['username'];
    }
    elseif (in_array('31', $groupIDs))
    {
        $ranks['Junior Officer'] = $uresult['username'];
    }
}

foreach($ranks as $rankName => $members)
{
    echo "<b>{$rankName}</b><br /> ";
    foreach($members as $memberName)
    {
        echo "{$memberName}<br /> ";
    }
}

[steveangelis]

I am not the one that designed it this way.  I am just simply trying to utilize what I have here, and the ranks are set as part of groups which is why this is so difficult.

 

It shows all the users which I want, but it is not showing the rank title that I assign to it.  I have tried messing with the array, assigning it the number, for instance, with officers the number 32, then echoing the rank but no rank name appears, just the list of officers.

 

 

$uquery = mysql_query("select userid,username,membergroupids from user") or die(mysql_error());

$ranks = array('Officer' => '32');

while ($uresult = mysql_fetch_array($uquery))
{
    $groupIDs = explode(',', $uresult['membergroupids']);

    if (in_array('32', $groupIDs))
    {

        $ranks['Officer'] = $uresult['username'];
	echo $ranks[0];
	echo $uresult['username']."<br>";
    }
    elseif (in_array('31', $groupIDs))
    {
        $ranks['Junior Officer'] = $uresult['username'];
	echo $ranks[0];
	echo $uresult['username']."<br>";
    }
}

 

 

This is the output I am looking for:

 

Officer

me

you

him

her

Junior Officer

then

them

they

its

 

And so on...

[steveangelis]

Does anyone have any idea of how this can be done?

[asmith]

Here's The Query that will solve your problem, As long as you seprate your values with coma:

 

$uquery = mysql_query("select userid,username,membergroupids from user WHERE FIND_IN_SET('YOUR NUMBER', membergroupids)") or die(mysql_error());

 

[steveangelis]

That would work except for when I am looking for something like 6 and there is a 36 or 46 in the database.

[asmith]

Did you try it?

 

If you are looking for 6, That query won't give you results when there are numbers which contain 6 in them. That's this query :

 

$uquery = mysql_query("select userid,username,membergroupids from user WHERE membergroupids

LIKE '%YOUR NUMBER%'") or die(mysql_error());

 

FIND_IN_SET will actually search between the comas to find your number.