SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Populate Checkboxes from MySQL Array

    I have an Array Field, called staff_id (Which can be, or cannot have an array, but will always have a certain number to it)

    I need to populate a checkbox area with each staff member, and those entered into the database will be checked.

    So if I have 12 staff members, it will display 12 checkboxes. But if I have 2,3,6 in the database, then staff 2,3 and 6 should be checked. :S

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You mean the staff_id field in a database store values, like "2,3,6"? That screams for database normalization.

    But, ok:
    PHP Code:
    //fetch data from database
    //...
    $ids=explode(",",$row['staff_id']);

    //get the number of staff members
    //...

    for($i=1$i<=$staff_members$i++){
      
    $checked=in_array($i,$ids)?'checked="checked"':'';
      echo 
    "<input type=\"checkbox\" name=\"staff_id[]\" value=\"$i\" $checked />";

    Saul

  3. #3
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean about database normalization? O_o

  4. #4
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I mean that storing comma separated values in a single field is a no-no. Basically you need another table for those values.

    Read more about database normalization in wikipedia if you're interested.
    Saul

  5. #5
    SitePoint Addict Skookum's Avatar
    Join Date
    Sep 2006
    Location
    Idaho
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Check this Link out.

    There are 5 normilizations currently and a 6th one on it's way.

    I am still learning about it other wise I would try to explain better, but from what I understand it is there to make sure that your database runs as effeciently as possible. It's a way of storing your data, or rather rules you use when storing your data.

    Also having a DB that is normalized up to the 5th isn't always the best solution either, as normalizing does add overhead to small databases, but helps out a bunch with large complex databases.

    Now that I have confused you I will be on my way.
    Paranoia is no longer a mental illness it is a way of life - Me

  6. #6
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I do have my Staff Table and my Group table.

    It just happens that once in a long while a Group will have several staff (Mostly 2), but very seldom. Which is why I thought my way would work out better O_o

  7. #7
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Yika View Post
    Well, I do have my Staff Table and my Group table.

    It just happens that once in a long while a Group will have several staff (Mostly 2), but very seldom. Which is why I thought my way would work out better O_o
    You're talking about many-to-many relationship. In this case you need another table to handle it. For example, group_staff: staff_id, group_id. If you know what I mean.
    Saul

  8. #8
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No.. I don't know what you mean. but it sounds smart.

    Ay ay ay... My head

  9. #9
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It means that you have to create another table, not to add al these staff IDs in one field.

    So you get a table called `groups`, another called `staff` and another one called `group_staff`.
    All tables got an auto_increment ID field. At `group_staff` you define a staff ID to a group ID.

  10. #10
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I also have another thing I just thought of.

    I have several tables for groups.. group06, group07, group08... etc etc. I keep a table per year.

    I get what you mean now, but I don't understand how it could help me out. =/

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Yika View Post
    Well, I also have another thing I just thought of.

    I have several tables for groups.. group06, group07, group08... etc etc. I keep a table per year.

    I get what you mean now, but I don't understand how it could help me out. =/
    In fact, you're already helped out . See the first post of php_daemon. This one works.
    What we're discussing now is your database structure. What if you, for example, are looking in what groups a staff is active in? Would be very easy if you got an extra table where you couple staff and group to each other.

  12. #12
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *cries*

    Im so lost. Im also stressed right now because of things going on at work (Work at an incentive company and we have 3 groups in town!!) I'll re-read and investigate, once Im done.


  13. #13
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Question-

    Does my group_staff table need to have an autoincrement or a key?

  14. #14
    SitePoint Addict Skookum's Avatar
    Join Date
    Sep 2006
    Location
    Idaho
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Welcome to my world for the past week.

    Check out this thread in the MySQL section
    http://www.sitepoint.com/forums/showthread.php?t=462965

    Ignore post #3 as it is just junk.
    But I found posts 10, 11, and 16 to be the most helpful. Plus the last post has links for help with normilization.
    Paranoia is no longer a mental illness it is a way of life - Me

  15. #15
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yika

    I think it may help if you were to post the details of what you are trying to do, also post your database table layout you have, then we can advise you how best to normalise the database.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  16. #16
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mandes,

    Alright. Sorry for being so dumb Right now I'm all over the place.

    I have a database, where I want to register groups that come in, who manages them, all their information, etc etc. (I work with an Incentive Company)

    So I use 4 main tables as of now:

    - clients
    - contact
    - groupXX (Where XX is ANY given year, from 06 to 12 for now)
    - staff

    A group may have ONE client ONE contact, but several staff.
    One Client can have MANY groups
    One Contact can have MANY groups
    One Staff can have MANY groups

    In clients, I have client_id and client_name
    In contact (the person who we are in contact with) I have contact_id, contact_name, contact_email, contact_phone, contact_fax, contact_company, contact_address, contact_country

    For groupXX (They ALL follow this format) :

    group_id
    client_id
    group_name
    group_type
    group_start_mm
    group_start_dd
    group_end_dd
    group_end_mm
    group_pax
    group_destination
    group_lead
    group_date_lead
    staff_id
    group_status
    group_date_proposal
    group_last_contact
    group_follow_up
    contact_id
    group_comments

    :S Hope this helps.. if more info is needed, let me know!

    - I do think I've come a lot way from my first version. This is version 3, version 1 had just ONE table. Yeah. Chaotic.

  17. #17
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Is there a reason why you have differing tables for groupXX depending on year, as opposed to putting the year as a field inside the Group record ??
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  18. #18
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes.

    We use file numbers for our groups, such as 07001, 07002, etc etc. So it was easier for me to get the file ID based on the auto increment number.

  19. #19
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok,

    I'm thinking about probably re-doing the whole database (which I probably will have to anyway, since It will now be in both English and Spanish.. :S)

    So I'll start from the beginning. Maybe I can get pointers on how to design.

    So I need at least 4 tables, Group, Staff, Client, Contact.

    Group has a Many-Many relationship with Staff
    Client has a One-Many relationship with Group
    Contact has a One-Many relationship with Group

    I use File Numbers in this format- 07002, 08235, 06302, to count how many groups we have per year, plus in accounting they also use that number.

    Any pointers?

  20. #20
    SitePoint Addict Skookum's Avatar
    Join Date
    Sep 2006
    Location
    Idaho
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright I am no where near a professional at this, but this may give you an idea.

    I am assuming that group_id is a UniqueID for the group table, staff_id is a UniqueID for the staff table, client_id is a UniqueID for the client table, and contact_id is a UniqueID for the contact table.

    So you would build your tables as you normally would. Since I didn't see a post of all of your fields I will work the ones that I come up with.

    To do your many to many relationship with the Group and Staff tables you would take the UniqueID from Group, and the UniqueID from Staff and put them into a seperate "linking" table. This table would only have 2 columns and you would designate the Primary Key over those two columns. So something like this
    Code:
    `group_id` int(11) NOT NULL,
      `staff_id` int(11) NOT NULL,
      PRIMARY KEY  (`group_id`,`staff_id`)
    Then you would run queries against the staff, group, and this staff-group-linking tables to pull the info. Sorry I don't really feel comfortable writing out the queries at the moment because I don't want to give you something that completely wouldn't work.

    For your one to many with Clients and Groups you would just add a column to your Clients table that was called group_membership or something. So it would look something like this.
    Code:
     `clientpk` int(11) NOT NULL auto_increment,
      `client_name` varchar(64) default NULL,
      `client_phone` varchar(64) default NULL,
      `group_id` int(11) default NULL,
      PRIMARY KEY  (`clientpk`)
    Now this I know how to run queries on so I can give you a little bit of an example.
    Say you wanted to know all the clients names that were members of the Group "Smileys". You would do something along the lines of this
    Code:
    SELECT client.client_name, client.group_id, group.group_id, group.group_name FROM group
    INNER JOIN
    client ON
    group.group_id = client.group_id
    WHERE
    group.group_id = "Smileys"
    I think that will work, have not tested it though.

    And for your one to many relationship between Contacts and Groups you would do the same thing as I have shown above with the Clients and Groups.

    If you want to post what your field names are, either myself or someone else could probably help you out more with the details, but I hope this gives you an idea of what you need to do.
    Paranoia is no longer a mental illness it is a way of life - Me

  21. #21
    SitePoint Addict Skookum's Avatar
    Join Date
    Sep 2006
    Location
    Idaho
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more thing before I get nailed again for naming conventions.

    It is harder to view and write code when you have fields in your table that begin the table name.
    For Example
    Group
    group_id
    group_name
    group_phone

    When you start running queries you get this
    Group.group_id, Group.group_name, Group.group_phone

    It is better to not use the group as part of the field name, but you can use an identifier, so something like this would look better, be easier to read and type.
    Group
    g_id
    g_name
    g_phone

    Group.g_id, Group.g_name, Group.g_phone
    Paranoia is no longer a mental illness it is a way of life - Me

  22. #22
    SitePoint Member Yika's Avatar
    Join Date
    Jul 2004
    Location
    Mexico
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow Skookum!! Thank you very very much!!

    I did read your other thread, and I was just like you, having clients_name clients_phone, in the clients table. I'll be better now.

    Thanks you so mucho!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •