need suggestions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

need suggestions

Post 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!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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).
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

Post 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:
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

Post 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);

?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I would be helpful to see your table(s) structure...
me!
Forum Contributor
Posts: 133
Joined: Sat Nov 04, 2006 8:45 pm

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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
Post Reply