duplicates

[dandixon]

Right the code below selects staff members from the database by the column gp(group) which enables me to select all or filter down by their group, but i need certain members to appear in more than one group

any ideas would be much appreciated

<form name="staff" action="staffs.php" method="POST">
            <select name="staff" class="drop_menu">
            <option value="%">All</option>
            <option value="2">Directors</option>
            <option value="3">UK Sales</option>
            <option value="4">Export Sales</option>
            <option value="5">Technical</option>
            <option value="6">Marketing</option>
            <option value="7">Purchasing</option>
            <option value="8">Accounts</option>
            <option value="9">Warehouse</option>
            </select>
            <input type="submit" value="Show" />
            </form>
<?php 
include(connection details);
?>
<?php
  $var = $_POST['staff'] ;

//connect to mysql
$connection = mysql_connect($host,$account,$password)
    or die ("Couldn't connect to server");
    
//select which database you want to edit
$db = mysql_select_db("test",$connection)
    or die ("Couldn't connect to database");
    
    // Build SQL Query  
$string = ("select * from staff where gp like \"%$var\" ORDER BY ord "); // SQL query
$query = mysql_query($string) or die (mysql_error());
$row_Recordset1 = mysql_fetch_array($query); //The Recordset array
?>

[nonshatter]

Can you post your table structure?

[dandixon]

team gp ord name position
Director 12 AA Gary Exec

hope this helps

[arionyx]

The easiest, fastest way I can think of to resolve this issue is to take note from rights management. Build an array of the groups as follows:

$array = array( 1 => 'group1', 2 => 'group2', 4 => 'group3', 8 => 'group4' )

You could then set the group field as bits and just test for the bits. You could also set the array as a data table in the DB would require an extra query which could create a bottleneck if you're planning on using a large number of groups > 1000

Note: Groups field would look as follows:

<form name="staff" action="staffs.php" method="POST">
    <select name="staff" class="drop_menu">
        <option value="1">Directors</option>
        <option value="2">UK Sales</option>
        <option value="4">Export Sales</option>
        <option value="8">Technical</option>
        <option value="16">Marketing</option>
        <option value="32">Purchasing</option>
        <option value="64">Accounts</option>
        <option value="128">Warehouse</option>
    </select>
    <input type="submit" value="Show" />
</form>

[nonshatter]

You will have to add a new column to the table called gp2 or something. This will be the only way to assign a member more than one group. If a member only has one group, then their gp2 field should be NULL.

ALTER TABLE staff ADD COLUMN gp2 INT(5);

Then your php/mysql query will need to be changed such that it queries the new field:

"select * from staff where gp like \"%$var\" OR gp2 like \"%$var\" ORDER BY ord ";

[mschroeder]

You need to use a many-to-many join table.

Create a table like StaffGroups, with columns GroupId (key to group table) and StaffId (key to staff table).
So it allows for n staff members to be in n groups at any one time.

[hielo]

Adding a second column is really NOT the ideal approach/solution. What if you add more groups and a person needs to belong to many groups.

What you need to do is to add a table to list the groups and another table that holds the membership info:

Person
id firstName lastName
1  John      Smith
2  Sally     Jones


Group
id name
1  Directors
2  UK Sales
3  Marketing


PersonGroup
Person_id Group_id
1         1
1         2
2         3

Your query would be:

$sql=sprintf("SELECT p.id as personId, p.firstName, p.lastName
FROM Person p INNER JOIN PersonGroup pg ON p.id=pg.Person_id
INNER JOIN `Group` g on g.id=pg.Group_id
WHERE gp.id=%d", intval($var) );

[mschroeder]

Exactly as hielo wrote. Nicely laid out example.

[arionyx]

This all boils down to one question, do you want to add more SQL or more PHP code, in my opinion the PHP code would be the better, easier solution.
It's easier/faster to change the data than to change the code, if you need help, I'll be more than happy to work with you on a full blown solution.

[dandixon]

Thanks for this, im just trying to weigh up my options because one person will only need to be in a max of 3 groups, i have a person that is a sales director so he goes in directors, sales and technical

[arionyx]

[inane_rambling]
Either way you go, and keep in mind, I'm telling you this because 1.) I'd prefer to propagate good code than to be proven right, 2.) hands on will teach you much faster and for a longer time than any of the self-proclaimed gurus you'll find here or elsewhere, and 3.) it's just more fun.
Anyway, back on topic, make sure you do benchmarks yourself, if you'd like I could help you set up a profiling script like one I use. While I will always swear to my own methods, I am no more perfect than any other 'guru' out there (except for the fact that I know when to admit I'm wrong or don't know). Oh, and looking at other languages, especially C, C++ will never let you down, 99% of my inspiration has come from looking at such low-level languages.
[/inane_rambling]
Either way you go I would suggest a bit system, it's easier, and faster, especially where MySQL is involved, take the following example:

1 = directors
2 = sales
3 = technical

PHP (Concept SQL)

$query = "SELECT Groups from employees WHERE id={$id}";
$result = Execute_SQL( $query );
if ( $result & 1 ) {
    echo 'Employee is in the directors group';
}
if ( $result & 2 ) {
    echo 'Employee is in the sales group';
}
if ( $result & 4 ) {
    echo 'Employee is in the technical group';
}
if ( $result & 7 ) {
    echo 'Employee is in all groups';
}

Simple huh?

[dandixon]

That would be really helpful, looking at that code now i dont know why i didnt do this in the first place. I managed to get it working by increaing the db and now i can play about with the code to make it more efficient

thanks
ive pm'd you my email