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.

[tomyknoker]

I tried 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';
echo $approved; it echo'ed the statement but without a result...

[thetestingsite]

Ok, are you using this in a different script than that of mlist.php (the first php script on the first page of this post)? If so, then you will need to use the queries for $aCount and so on. It would look like the following:



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



$approved = "There are '.$aCount.' members that are Approved";

echo $approved;


Hope this helps.

[tomyknoker]

Apologies yes I was going to put it on the page that shows the members, once you click one of the status links...

[tomyknoker]

Do you know of any way of being able to make the last line work for all of the status areas?
$approved = "There are '.$aCount.' members that are Approved";, so depending on what status is selected it filled in the appropriate fields of that line? Or do I need to do it spererately?

[thetestingsite]

You could do it seperatly (spelling?), or use if-then conditionals for this.

Example:



if ($cat == "A") {
$field = "Approved";
$theCount = $aCount;
}

elseif ($cat == "H") {
$field = "On Hold";
$theCount = $hCount;
}

echo 'There are '.$theCount.' members that are '.$field;


Hope this helps.

[tomyknoker]

I just had to make them lowercase but it works beautifully!
if ($cat == "a") {
$field = "Approved";
$theCount = $aCount;
}

elseif ($cat == "h") {
$field = "On Hold";
$theCount = $hCount;
}

[thetestingsite]

Great, let us know if you need any more help on this.

[tomyknoker]

I do want to take it a bit further... I guess the first part which hopefully will be simple. Once you get to the Status > Show Members page I want to have a link, which says 'Download', actually I also want to have a 'Download' link on the mlist.php page but that one would need to be in each cell of the table if you get what I mean? Is this hard... Can you define how it should be saved? I think .xls would be the best course of action... What do you think?

[tomyknoker]

Sorry I posted a question in the wrong post ha! How would I get the date's here to be back to how they were using this
echo($qry['loginDateTime']?date('d/m/Y H:i:s', strtotime($qry['loginDateTime'])):'unknown')?

[tomyknoker]

Great, let us know if you need any more help on this.Hiya! I tried to add an 'Edit' link, but now it's not displaying anything... Basically the 'Edit ' link when clicked on is meant to change the member from 'MemberStatus' 'A' to 'D'... any ideas?
<?php
$cat = $_GET['cat'];

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

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

//navigation
include("nav.php");

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

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

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

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

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

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

if ($cat == "a") {
$field = "Approved";
$theCount = $aCount;
}

elseif ($cat == "d") {
$field = "Deleted";
$theCount = $hCount;
}

elseif ($cat == "h") {
$field = "On Hold";
$theCount = $hCount;
}

elseif ($cat == "p") {
$field = "Pending";
$theCount = $pCount;
}

elseif ($cat == "n") {
$field = "Not Sure";
$theCount = $nCount;
}

elseif ($cat == "r") {
$field = "Rejected";
$theCount = $rCount;
}

echo 'There are '.$theCount.' members that are '.$field;


if (isset($_GET['del']) AND $_GET['del'] <> "") {
/* construct and run our "deactivate" query */
$query = "UPDATE tblmembers SET MemberAPP = 'D' WHERE `MemberID`='" . $_GET['del'] ."' LIMIT 1";
$result = mysql_query ($query);
}

/* set the allowed order by columns */
$default_sort = 'LastName';
$allowed_order = array ('JoinDate', 'FirstName','LastName', 'loginDateTime');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise,
* set it to what was passed in. */
if (!isset($_GET['order']) OR !in_array ($_GET['order'], $allowed_order)) {
$order = $default_sort;
} else {
$order = $_GET['order'];
}

/* construct and run our query */
$query = "SELECT * FROM tblmembers WHERE `MemberApproved`='$cat' ORDER BY $order";
$result = mysql_query ($query);

/* make sure data was retrieved */
if (mysql_num_rows($result)) {
echo "No data to display!";
} else {

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<table border=1>\n";
$table_init = true;
echo "<TR>\n";
while ($row = mysql_fetch_assoc ($result)) {
if ($table_init) {
$table_init = false;
/* check if the heading is in our allowed_order array. If it is, hyperlink it so that we can order by this column */
echo "<tr>\n";
foreach ($row as $heading => $column) {
echo "<td>";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&cat=$cat\"><b>$heading</b></a>";
} else {
echo $heading;
}
echo "</td>\n";
}
echo "<td>Del?</td>";
echo "</tr>\n";
} else {
echo "<tr>\n";
foreach ($row as $heading => $column) {
echo "<td>$column</td>\n";
}
echo "<a href=\"{$_SERVER['PHP_SELF']}?del=" . $row['MemberID'] . "&order=$heading&cat=$cat\">Delete</a>";
echo "</tr>\n";
}
}
echo "</table>\n";
}
?>

[thetestingsite]

In the above code, I don't see where you put the edit link. Is it the one that says "Del?". Either way, I don't see any reason why it wouldn't show anything.

Hope this helps.