Page 1 of 1

need suggestions

Posted: Tue Nov 07, 2006 5:24 pm
by me!
Ok I need some help.

I am trying to wright a script that will display a list of club memberships. But... Some memberships are family and have multiple people in them. I want one row for each membership, not each person.

Like this:

Code: Select all

------------------------------------------------------------------
  |   John Doe    |    487-594-2467    |    john@simesite.net    |
  ------------------------------------------------------------------
  |   Jack Off    |    785-534-3569    |    jack@freemail.com    |
  ------------------------------------------------------------------
  |   Paul Easy   |    785-634-2599    |    me@you.bla           |
  |   Joe Easy    |    785-634-2599    |    notme@you.bla        |
  ------------------------------------------------------------------
  |   B. Johnson  |    689-444-9595    |	 bigjohnson@n.com     |
  ------------------------------------------------------------------

Now I have the idea of assigning each “membership” (not member) a membership id #, I was thinking For the 50 members I already have I would just assign one and from now on just use a time stamp? This would give me unique numbers and I am thing that there should be some way to do an “if” statement and use that #?

Each person is a separate row in the DB, I am trying to figure out the best way to be able to recognize two (or more) people being in the same family.

Any suggestions would be helpful.

Thanks Me!

Posted: Tue Nov 07, 2006 5:40 pm
by John Cartwright
I would have a family table, and then a person table. Store all the details in the person table, and simply have a column which references that family's id in the family table. I'm not exactly sure on your current implementation, so can you post your current table structure?

Moved to Databases.

Posted: Tue Nov 07, 2006 6:58 pm
by me!
Ok, I am not sure what I will gain with a second table?
Please explain further.

Structure is Post Nuke _users table with some addition columns added. All information about each person is stored here. And needs to be here for the CMS to work, I am trying NOT to have duplicate information, in multiple locations.

Posted: Tue Nov 07, 2006 7:02 pm
by timvw
I would go along with Jcart, select the JOIN of membership and person (order by membership id, person id) and then loop through the resultset... Since they're ordered on membership id it's pretty easy to detect when a new membership starts (and thus a new table row should be added).

Posted: Tue Nov 07, 2006 7:11 pm
by me!
I think I am getting it...
Assuming we have two tables
“users” with the personal information
and
“members” with a member id #

Use the member ID to define the rows and put the data for each person in it.

Bare with me I have only been doing this for about a week. But why not just have a column called “member_id” in the “users” table?

How would I go about linking the member ID to a user or users information in the “users” table?

Posted: Wed Nov 08, 2006 1:01 am
by timvw
me! wrote: How would I go about linking the member ID to a user or users information in the “users” table?
Depends on the relationship between membership and user.

If a user can be a member of only one membership i would add an extra column to the user table that has a membership_id.

If a membership is only available for one user, i would add an extra column user_id to the membership table.

In case both conditions above are true, i would find out which of the two items can exist without the other. Is it possible to have a membership if there is no user? Is it possible to be a user without a membership? The one that can't exist without the other will get the extra column.

In case a membership can have many users, and users can have many memberships i'll add a membership_users table that contains a membership_id and a user_id.

Posted: Wed Nov 08, 2006 3:23 pm
by me!
The users table will contain people that are not members.

I have a column for user_id and member_id already;
user_id is for postnuke’s use, and is a unique number for all users.
member_id is the club members membership id (note in family memberships all family members have the same member_id) now since not all users of the site are club members non members have a member_id of 0 .

I think this may be more of a "how to code" question... :oops:

Posted: Wed Nov 08, 2006 7:30 pm
by me!
All I need it to do is display the users with the same member_id in the same row, one under the other.
Additionally I would like to have the rows alternate background color.

This is what I have so far...

Code: Select all

<?php
//Consept: To return a list of all Members

// Performing SQL query
$query = 'SELECT pn_name, pn_uname, pn_phone, pn_email, pn_duesexdate, member_id FROM _users WHERE member_id > 0 ORDER BY member_id ASC, pn_name ASC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);

?>

Posted: Wed Nov 08, 2006 7:33 pm
by RobertGonzalez
I would be helpful to see your table(s) structure...

Posted: Wed Nov 08, 2006 7:58 pm
by me!
MySQL table: Users
pn_name -- persons name (first and last)
pn_uname -- user name (for website use)
member_id -- member ID Unique PER MEMBERSHIP Not per Person (this will be the same for all members of one family) So in the example "table" Paul Easy and Joe Easy would have the same member ID

pn_phone -- phone number
pn_email -- e-mail address
pn_duesexdate -- the date there membership expires

Posted: Sat Nov 11, 2006 4:31 pm
by califdon
As a retired database instructor, I feel compelled to inject some cautionary words here. The questions and the response to showing the table structure indicate that you are a beginner in dealing with databases (which is nothing to be ashamed of, certainly!) and that consequently you don't understand relational database theory. My point is not to ridicule you, but to state that relational databases are governed by rather strict rules. Designing a schema (what tables to use and what fields in those tables) is not something that should be done on the basis of casual references to how you want the data to appear. If you do, your chances of it working properly are not good, and even if you get it to work, you'll be in trouble if you later decide to expand or change the design.

As a start, you need to define what 'entities' you have in your model, because every entity must have its own table. An entity is a person or a thing (invoice, record, whatever) or an event, roughly. An entity has 'properties' or 'attributes', which become the fields in that entity's table. At first glance, I would guess that your entities are Memberships and People. That means you need two tables with Primary Keys, related to each other. For the other data elements, you must decide whether they are attributes of a Membership or a Person. For example, expiration date is clearly an attribute of a Membership, but telephone number is probably an attribute of a Person. So you would define your two tables with the appropriate fields and link them as appropriate with Primary and Foreign Keys.

I recommend that you spend a little time reading a relational database tutorial or book. Here are a few online references:

http://www.surfermall.com/relational/lesson_1.htm
http://www.utexas.edu/its/windows/datab ... rview.html
http://www.extropia.com/tutorials/sql/toc.html
http://www.webmonkey.com/webmonkey/99/13/index1a.html
http://www.databasejournal.com/sqletc/a ... hp/1469521

If I come across as pedantic, I'm sorry, but in decades of consulting and teaching database technology, I've seen a whole lot of people innocently begin dabbling with databases and getting hopelessly mired down. My intent is to give you help, not criticize you.

Don