Showing Status Of Members

[tomyknoker]

Hi all,

Am very new to PHP so please bear with me... I have a MySQL database which has members in it, I have a column called 'MemberApproved', which has these values A, D, H, N, P and R... Which stand for:

A = Approved
D = Deleted
H = On Hold
P = Pending
N = (UNSURE)
R = Rejected

The 'N' I am very confused about as I didn't design the DB... Anyway I want to create a PHP page which looks like the attached image, and from there I want to make the 'Amount' and also the 'Approval' a link so you can click on that and it shows all the members on a nother page which are Approved etc. In that list I would like to show FirstName, LastName, State, loginDateTime. I would also on that page like to have an 'edit' link and a 'delete' link. If the user clicks the edit link would be simple just a drop down list with the Approved, Deleted, On Hold, Pending, Rejected and that can be saved and updated...

As I said I am very new to PHP so any help would be greatly appreciated! Thanks all in advance!

[thetestingsite]

Here is just a simple example of how you could go about doing this:

mlist.php:

<?php

/* connect to the mysql database and use different queries for the count of members */

mysql_connect('localhost','username','password');
mysql_select_db('database');

//approved
$a = mysql_query("SELECT * FROM `table` WHERE `MemberApproved`='A'");
$aCount = mysql_num_rows($a);

//deleted
$d = mysql_query("SELECT * FROM `table` WHERE `MemberApproved`='D'");
$dCount = mysql_num_rows($d);

//on hold
$h = mysql_query("SELECT * FROM `table` WHERE `MemberApproved`='H'");
$hCount = mysql_num_rows($h);

//pending
$p = mysql_query("SELECT * FROM `table` WHERE `MemberApproved`='P'");
$pCount = mysql_num_rows($p);

//not sure
$n = mysql_query("SELECT * FROM `table` WHERE `MemberApproved`='N'");
$nCount = mysql_num_rows($n);

//rejected
$r = mysql_query("SELECT * FROM `table` WHERE `MemberApproved`='R'");
$rCount = mysql_num_rows($r);


/* Make the layout */
?>
<table border="1" cellpadding="3" cellspacing="1">
  <tr valign="top">
    <td colspan="2">Membership Status</td>
  </tr>
 
  <tr valign="top">
    <td>Status</td>
    <td>Total</td>
  </tr>

  <tr valign="top">
    <td>Approved</td>
    <td><a href="showMembers.php?cat=a"><?php echo $aCount;?></a></td>
  </tr>

  <tr valign="top">
    <td>Deleted</td>
    <td><a href="showMembers.php?cat=d"><?php echo $dCount;?></a></td>
  </tr>

  <tr valign="top">
    <td>On Hold</td>
    <td><a href="showMembers.php?cat=h"><?php echo $hCount;?></a></td>
  </tr>

  <tr valign="top">
    <td>Pending</td>
    <td><a href="showMembers.php?cat=p"><?php echo $pCount;?></a></td>
  </tr>

  <tr valign="top">
    <td>Not sure</td>
    <td><a href="showMembers.php?cat=n"><?php echo $nCount;?></a></td>
  </tr>

  <tr valign="top">
    <td>Rejected</td>
    <td><a href="showMembers.php?cat=r"><?php echo $rCount;?></a></td>
  </tr>
</table>

showMembers.php:

<?php
$cat = $_GET['cat'];

/* connect to the mysql database and use a query to get the members info */

mysql_connect('localhost','username','password');
mysql_select_db('database');

//approved
$info = mysql_query("SELECT * FROM `table` WHERE `MemberApproved`='$cat'");

echo '<table border="0" cellpadding="3" cellspacing="1">
  <tr valign="top">
    <td>First Name</td>
    <td>Last Name</td>
    <td>State</td>
    <td>loginDateTime</td>
  </tr>';

if (mysql_num_rows($info) < 1) {
echo '<tr valign="top">
  <td colspan="4">There are no members that match the query. Please go back and try again</td>
    </tr>';
}

else {
   while ($qry = mysql_fetch_array($info)) {

//create the layout
?>
  <tr valign="top">
    <td><?php echo $qry['FirstName']; ?></td>
    <td><?php echo $qry['LastName']; ?></td>
    <td><?php echo $qry['State']; ?></td>
    <td><?php echo $qry['loginDateTime']; ?></td>
  </tr>
<?php
   }
}

echo '</table>';
?>

Simply edit the parts in red for you mysql database info. Hope this helps.

[tomyknoker]

Thanks you so much I am going to give it a try, hopefully if I have a few questions I can ask you? Can I just add this

include 'library/config.php';
include 'library/opendb.php';

at the top? Do I need to call anythign else? These are set up files which hopefully connect to the db...

[thetestingsite]

hopefully if I have a few questions I can ask you?

Sure, just let me know. If I can't help you, I'm sure another members could.

Can I just add this at the top? Do I need to call anythign else? These are set up files which hopefully connect to the db...

You can, just be sure to take out the mysql_connect snippets at the top or each page.

Hope this helps.

[tomyknoker]

That was great! Just quickly as I know you have to go... on the second part where we see members, how can I add an 'edit' link that when clicked displays a drop down menu and the Members Status can be changed and then saved...

[thetestingsite]

That could be fairly simple to do, it would just be a little bit of coding.

Try something like this as a basis (edit for your code):

<?php

include('dbconnect.php');

  if ($_POST['act'] == "update") {
     $status = $_POST['status'];
     $firstname = $_POST['firstname'];
     $lastname = $_POST['lastname'];

     mysql_query("UPDATE `table` SET `MemberApproved`='$status' WHERE `FirstName`='$firstname' AND `LastName`='$lastname'");

      header('index.php');
  }

  else {

$firstname = $_GET['firstname'];
$lastname = $_GET['lastname'];

$info = mysql_query("SELECT * FROM `table` WHERE `FirstName`='$firstname' AND `LastName`='$lastname'");

$qry = mysql_fetch_array($info);

/*display the form*/
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
  <input type="hidden" name="firstname" value="<?php echo $qry['FirstName'];?>">

New status: <select name="status">
<!--List status options here-->
</select>
<input type="submit" value="Edit User">
</form>

<?php
  }
?>

Then make the link something like filename.php?firstname=$firstname&lastname=$lastname

Where $firstname is the field first name from the table, etc.

Hope this helps (I am now logging off the computer and going home if you need any more help you can PM me, or post in the board - as another member will probably be able to help out as much as I can).

[tomyknoker]

DOes anyone know once I display my list of members by status, at the top I want to say

There are <NUMBER> members that are <STATUS>.

So it would read "There are 250 members that are Approved. "

[thetestingsite]

You would want to do something like this somewhere in your code.

$info = mysql_query("SELECT * FROM `table` WHERE `status`='Approved'");
$approved = 'There are <b>'.mysql_num_rows($info).'</b> members that are Approved';

That would show for example:

There are 20 members that are Approved

Hope this helps.

[tomyknoker]

So would I need to do a line for each status?

$approved = 'There are <b>'.mysql_num_rows($info).'</b> members that are Approved';
$rejected = 'There are <b>'.mysql_num_rows($info).'</b> members that are Rejected';
$onhold = 'There are <b>'.mysql_num_rows($info).'</b> members that are On Hold'; 

[thetestingsite]

Well, I basically already done this for you. Just try doing something like this:

$approved = 'There are <b>'.$aCount.'</b> members that are Approved';
$rejected = 'There are <b>'.$rCount.'</b> members that are Rejected';
$onhold = 'There are <b>'.$hCount.'</b> members that are On Hold';

Then echo each variable like so:

echo $approved;

Hope this helps.