Anyone know php + mysql? help needed

[TexasComputerDude]

Hey yall,

I'm trying to create a website that allows an "admin" to create groups and people to sign up for those groups.

I have a page with checkboxes that show what groups a user has signed up for but I can't quite think of a query that will allow me to show all groups fields in the group table plus the ones the user a has signed up for in the user table.

[userid] [general] [sports] [news]
001 1 0 1


[groups] [active]
general 1
sports 1
news 1


Is there a better way to do this?

[OldCannon]

Well, I'll state the obvious and say it looks like you're reinventing the wheel -- these kind of features already exist in Drupal or PPHBB. However, I'll answer this assuming you're doing this as an academic exercise. If I understand your question, you want to show a form that has a list of groups (likely in the form of checkboxes?) and then turn on those groups that the user is subscribed to?

Since I'm neither a PHP expert nor a SQL expert (but I work with both ), I'll do some generalized hand-waving here:
1) A group of checkboxes with the same name are treated as an array
2) You would create a SQL query that gets the complete list of group names from the server, sorted in whatever order suits you
3) You would create a SQL query that get the complete list of user-subscribed group names from the server, sorted in the same order
4) For each item in the complete group list query, you would create a checkbox with the "value" being the actual group name. If the first user-subscribed group list matches, mark the checkbox "on" and advance the user-selected list (unless, of course, you're out of user-selected list items)

When you're done, you have a collection that is an array of checkboxes that have the user-selected checkboxes turned on.

Easy peasy.

You probably want to take questions that are more complex than that to a PHP forum

[TexasComputerDude]

Well, I'll state the obvious and say it looks like you're reinventing the wheel -- these kind of features already exist in Drupal or PPHBB. However, I'll answer this assuming you're doing this as an academic exercise. If I understand your question, you want to show a form that has a list of groups (likely in the form of checkboxes?) and then turn on those groups that the user is subscribed to?

Since I'm neither a PHP expert nor a SQL expert (but I work with both ), I'll do some generalized hand-waving here:
1) A group of checkboxes with the same name are treated as an array
2) You would create a SQL query that gets the complete list of group names from the server, sorted in whatever order suits you
3) You would create a SQL query that get the complete list of user-subscribed group names from the server, sorted in the same order
4) For each item in the complete group list query, you would create a checkbox with the "value" being the actual group name. If the first user-subscribed group list matches, mark the checkbox "on" and advance the user-selected list (unless, of course, you're out of user-selected list items)

When you're done, you have a collection that is an array of checkboxes that have the user-selected checkboxes turned on.

Easy peasy.

You probably want to take questions that are more complex than that to a PHP forum

haha thanks, yea it is an academic exercise. I asked here cause I consider us to be the brightest lol. I was hoping to use one query to do it all, I may post on a php forum but now I think I'm just begging for a headache. Being a php/mysql beginner after all lol. I'm going to try your route and then later on after I'm a bit more advanced rework it.

[dalto]

The way you have it setup you don't really need the groups table.

You would just select the user record into a record set and display it in php.

If you change the table structure to a more conventional structure you could do it in a query

A more common way would be to have a table for groups, a table for users and table that joins them together.

user table
[userid][username]

groups table
[groupid][groupname][active]

membership table
[groupid][userid]

[TexasComputerDude]

thanks for all the help. Yall got the gears back to rolling and I successfully accomplished my objective!!!

[OldCannon]

Just fyi, if you are not opposed to using Microsoft for development, ASP.NET makes it ridiculous easy to do all this. Membership roles and Profiles are very easy to work with =)

I am actually busy completely recoding texas3006.com into .NET using C#. I'm going to be able to pull off some really nifty things that are a headache to do in PHP.

I wouldn't be too dismissive of PHP/SQL. While ASP.NET/C# are very powerful, the PHP community shines more as you scale upward into CMS stuff. Both sides have advantages and disadvantages. There's definitely things to do in ASP.NET that are a headache compared to PHP :-) ASP.NET 4.0 and the MVC2 library go a long way to addressing a lot of those concerns, but PHP isn't sitting still either.

[TexasComputerDude]

Just fyi, if you are not opposed to using Microsoft for development, ASP.NET makes it ridiculous easy to do all this. Membership roles and Profiles are very easy to work with =)

I am actually busy completely recoding texas3006.com into .NET using C#. I'm going to be able to pull off some really nifty things that are a headache to do in PHP.

Once I get php/mysql down I may learn asp. I like being able to pick the right language for the job.

[TexasComputerDude]

Cool thanks a lot. Right now my goal is an email queue. Lots of work lol