Member List Manager

Created and explained by Marfisa. Please credit In the Cards if you use this script.

This is an easy to use Member List Manager that uses PHP and MySQL. It can be used as-is, with just a couple of files you have to edit, or you can customize it to do pretty much anything. For those who want to customize it, I'll do my best to make the code clear and the instructions easy to understand.

This system uses PHP and MySQL.
Your host must support PHP and MySQL in order for you to use this member list manager.

First, download the files here.

Important!
When you're ready to upload all these files, put them in a directory that other people can't find easily. Otherwise anybody could wreak havoc with your member list by randomly updating or deleting members.

To make it easier to read the code, I'll be using highlighting and italics to point out ways to customize the files for your needs. PHP code will be in a different color than regular html code. Things in red MUST be changed to your settings. Things in italics can be changed or added to if you like.

What is MySQL?

MySQL is a programming language (a variant of SQL - Structured Query Language) that is designed to deal with databases. It gives you a way to set up and maintain information that you want stored permanently (in a special file). Your information is stored in a table, just imagine an html one like this-

Dinner Menu
Dish
Description
Price
Chicken Parmigiana Breaded chicken served over pasta
with mozzarella and marinara sauce.
$5.60

In this case "Dinner Menu" is the name of the table. The columns "Dish", "Description", and "Price" tell us what kind of information we will be looking at. This is exactly the way a MySQL database works. Each database can have multiple tables like this one.

First, you need to make sure your webhost supports MySQL. You can use the method in PHP basics "Is PHP on your server?". If you have MySQL available, it will mention it, along with the version number. If you're hosted with us, it's version 5.0.16. Not all hosts support MySQL, and even some that do require that you pay extra for using it, so ask your hosting service if you are in doubt. If have you WordPress on your site, then you already have a database set up and can use the same settings to just add another table to it. If you're hosted with us, we can set up the database for you. It's usually as simple as clicking on a link that says "add database" and giving it a name such as "My_Database" and entering the username and password.

In order to do anything with your newly created database, you have to allow the files to access it first.

DBINFO.PHP

<?
$hostname="###url_where_database_is_hosted###";
$user="###your_user_name###";
$password="###your_password###";
$database="###name_of_database###";
$tablename="###name_of_table###";
?>

This file is fairly self-explanatory; just change everything in red to your own settings. The hostname may be tricky because the format can vary from host to host. In our case, it doesn't begin with http:// and would be like mysql.clavis-sama.com. Ask your host if you're in doubt.

As for the table name, try to make it something appropriate, such as TCGmembers. Also, if you have more than one TCG and would like to use this member list manager, just give each of your TCGs a unique table name. They can all share the same database.

CREATE_TABLE.PHP

<?
include("dbinfo.php");
mysql_connect($hostname,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="CREATE TABLE " . $tablename . "
(id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name varchar(50) NOT NULL,
email varchar(100) NOT NULL,
site varchar(100) NOT NULL,
collecting varchar(40) NOT NULL,
birthday varchar(20) NOT NULL,
level varchar(20) NOT NULL,
prejoin varchar(5) NOT NULL,
isnew varchar(5) NOT NULL)";

mysql_query($query);
mysql_close();
echo "Table created";
?>

As you can see at the top, it will include dbinfo.php, which we set up earlier, in order to access the database. This file must be included in each directory where you want to call up your database.

I've set up various columns in the table, such as name and email. Feel free to add as many as you need, but remember that there is no comma on the very last one before the closing parenthesis. Don't mess with "id" though, it is the key (or "slot" number) for the array. Yep, you're actually just creating an array when you set up a database. If you read my PHP Programming tutorial, you might already recognize some of the code used below when reading information from the database.

As for the stuff next to the name with a number in parenthesis, that specifies the type of data and its length.

common types of data:
CHAR- max 255, stands for character. This allows pretty much any typeable character on the keyboard, the drawback is that it will fill the end with spaces to the max you specified, such as char(40), so that it will take up more space in the database, but it can allow for faster searching which doesn't really matter with our small tables. I recommend not using it.
VARCHAR- max 255, stands for variable (length) character. This is the same as char only it does not fill up the empty space, making it smaller. Use this instead of char.
TEXT- max 65535, same as varchar only much bigger. You do not specify a length, so no text(##), just text. This would be useful if you need something longer, like a paragraph or two for a bio.
INT- stands for integer, any non decimal point number from -2147483648 to 2147483647. You don't have to specify the number of digits, but you can if you like.
FLOAT- not sure of the max, but it handles some pretty hefty numbers, same as integer but it can have a decimal point. You have to specify both total digits and the number of decimal places like this- float(5,3). This would only allow a number up to something like 999.99, five digits total, two of which are decimal places, so be sure you give yourself plenty of room.
DATE- YYYY-MM-DD, you do not specify length.
TIME- hh:mm:ss, you do not specify length. The hours go up to 24.
DATETIME- YYYY-MM-DD hh:mm:ss, you do not specify length.

Using this information, you could add new columns to the example like this.

name varchar(50) NOT NULL,
email varchar(100) NOT NULL,
site varchar(100) NOT NULL,
collecting varchar(40) NOT NULL,
birthday varchar(20) NOT NULL,
level varchar(20) NOT NULL,
prejoin varchar(5) NOT NULL,
isnew varchar(5) NOT NULL,
joindate date NOT NULL,
numberofmasteredsets int(3) NOT NULL)";

VERY IMPORTANT!
Once you have finished editing this and all your files below, you must open create_table.php in your browser first! Otherwise, none of the other files will work and index.php will give you this error- "Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in index.php on line 7". It is a good idea to delete create_table.php from your site once you're sure everything is working.

ADD.PHP

<form action="added.php" method="post">
Name: <input type="text" name="name"><br>
Email: <input type="text" name="email"><br>
Site: <input type="text" name="site"><br>
Collecting: <input type="text" name="collecting"><br>
Birthday: <input type="text" name="birthday"><br>
Level: <select name="level">
	<option value="one" selected="selected">one</option>
	<option value="two">two</option>
	<option value="three">three</option>
	<option value="four">four</option>
	<option value="five">five</option>
	<option value="six">six</option>
	<option value="inactive">inactive</option>
	<option value="hiatus">hiatus</option>
	</select><br>
<input type="hidden" name="prejoin" value="true">
<input type="hidden" name="isnew" value="true">
<input type="Submit">
</form>

There's no PHP here, this is just a regular form that sends information to added.php. If you added columns to the example, make sure you add them here too. And you can have as many levels as you like and name them whatever you like, such as 1, 2, 3 or noob, apprentice, master, etc.

The two hidden fields don't have to be hidden. They just make it convenient so that when you add a new person during your prejoin phase, they automatically get marked as new and prejoin. When your prejoin phase is over, you can change that value to "false".

ADDED.PHP, UPDATE.PHP, UPDATED.PHP, DELETE.PHP

<?
include("dbinfo.php");
mysql_connect($hostname,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");

$name=mysql_real_escape_string($_POST['name']);
$email=mysql_real_escape_string($_POST['email']);
$site=mysql_real_escape_string($_POST['site']);
$collecting=mysql_real_escape_string($_POST['collecting']);
$birthday=mysql_real_escape_string($_POST['birthday']);
$level=mysql_real_escape_string($_POST['level']);
$prejoin=mysql_real_escape_string($_POST['prejoin']);
$isnew=mysql_real_escape_string($_POST['isnew']);

$query = "INSERT INTO " . $tablename . " VALUES
('','$name','$email','$site','$collecting','$birthday','$level',
'$prejoin','$isnew')";
mysql_query($query);
echo "Record Added<br>\n";
mysql_close();
?>

From here on, it's just a matter of making sure all the information matches the any changes you might have made.

Important!
If you add extra fields to your member list, be sure that you do not add a comma before "WHERE id='$ud_id" in updated.php. Your code will break!

Make sure that if you added any fields, you keep them in the same order as in your create_table.php, especially in the "$query=" line. If you change the order, it will create errors like making your birthday be your level, and your level be your prejoin status. This might cause your index.php to be blank or show incorrect info.

INDEX.PHP

<?
include("dbinfo.php");
mysql_connect($hostname,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM " . $tablename . " ORDER BY level ASC, name ASC";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
?>

<h2>member list management</h2>

<p>
Would you like to <a href=add.php>add a member</a>?
<p>

<!-- to make more levels, copy and paste from here -->

<h3>level one</h3>

<P>
<center>
<table>

<tr>
<td><b>prejoin</b></td>
<td><b>new</b></td>
<td><b>name</b></td>
<td><b>email</b></td>
<td><b>site</b></td>
<td><b>collecting</b></td>
<td><b>birthday</b></td>
<td><b>update</b></td>
<td><b>delete</b></td>
</tr>

<?
$i=0;
while ($i < $num) {
$name=mysql_result($result,$i,"name");
$email=mysql_result($result,$i,"email");
$site=mysql_result($result,$i,"site");
$collecting=mysql_result($result,$i,"collecting");
$birthday=mysql_result($result,$i,"birthday");
$level=mysql_result($result,$i,"level");
$prejoin=mysql_result($result,$i,"prejoin");
$isnew=mysql_result($result,$i,"isnew");
$id=mysql_result($result,$i,"id");

if ($level=="one") { ?>
<tr>
<td><? if ($prejoin=="true") { ?>Prejoiner<? } ?></td>
<td><? if ($isnew=="true") { ?>New<? } ?></td>
<td><?echo "$name"; ?></td>
<td><a href="mailto:<? echo "$email"; ?>">@</a></td>
<td><a href="<? echo "$site"; ?>" target="_new">www</a></td>
<td><? echo "$collecting"; ?></a></td>
<td><? echo "$birthday"; ?></td>
<td><a href=update.php?id=<? echo "$id"; ?>>Update</a></td>
<td><a href=delete.php?id=<? echo "$id"; ?>>Delete</a></td>
</tr>
<?
} // end if (for level)
++$i;
} // end while (for reading from database)
?>

</table>
</center>
</P>

<!-- to here and change the level in the h3 tag and in $level=="###" -->

This file is easier to understand than it looks. If you've added any columns in the other files, you'll have to add them here three times. First there are the table headings, and the usual php code right below. Then you must add them into the table data. Just follow the format of the other ones.

At this point, you can upload all your files and open create_table.php (as mentioned above), then come back to index.php.

Add a member to test it out. If it all went smoothly, you should see something like this-

prejoin
new
name
mail
site
collecting
birthday
update
delete
Prejoiner New Marfisa @ www raindrops June 26 Update Delete

If you don't see that, make sure the information in dbinfo.php is correct and if you made any changes, check your files if to make sure they all match.

This is where you should start styling the table to get it the way you want. For instance, instead of the words "Prejoiner" and "New", you could use images instead.

<td><? if ($prejoin=="true") { ?><img src=prejoin.gif><? } ?></td>
<td><? if ($isnew=="true") { ?><img src=new.gif><? } ?></td>

They don't have to be in their own cells either. Use view source to see ways you might like to change your table. Once you're satisfied, you must copy and paste the selection marked above to create more levels. Just follow the instructions and test it out by adding members and updating their information as you go along.

This system has the added bonus of telling you how many members you have. Just insert this bit of code wherever you want the total.

<?echo "$num"; ?>

This will show you the full number of records, whether it's members or whatever. In case you want this total shown on a page that doesn't already call the database, like with the Sailor Wars Standings shown on the front page of Moonlight Legend, include this bit of code at the top of the page and follow the guidelines below about dbinfo.php.

<?
include("dbinfo.php");
mysql_connect($hostname,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM " . $tablename . " ORDER BY level ASC, name ASC";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
?>

Once you're ready to have your member list show up on your actual member page, just remove the two columns for update and delete and the link at the top for adding new members. If you didn't put a full path on dbinfo.php, it will have to be in the same directory as your member page.

If you are uncomfortable having a copy of such a sensitive file in a public place (since it includes personal information), use the full path to the one you already have. You can find this by following the instructions on this page and looking under "PHP finds your full path!". Still, rest assured that no one can see the information unless you give them ftp access to look at the file itself. If you open dbinfo.php in your browser or try to save it, the source just shows a completely blank page.

troubleshooting

Unable to select database
This means the database could not be found. Make sure the settings in dbinfo.php are correct. Check with your host if you are not sure of the correct settings for your database.

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource...
This means the program was unable to find the table in the database. Be sure to run create_table.php once before trying to view index.php. This could also indicate an error create_table.php so check it carefully.

Nothing happens when you try to add a member
If you added extra fields to your member list, be sure that you did not add a comma before "WHERE id='$ud_id" in updated.php.

You can add members, but they don't show up on the index page
If the number of members goes up when you add members, but no information appears on the index page, check added.php and updated.php. Make sure that if you added any fields, you keep them in the same order as in your create_table.php, especially in the "$query=" line.