Changing Data In MySQL Through PHP

[tomyknoker]

Hi,

I have the following php page, it displays my members, by there Status... Approved, Deleted, On Hold etc... I want to add a 'Delete' next to each member in the table that gets displayed, but only want this 'Delete' button to move the members to the 'Delete' column. Which from a database POV would mean changing the member in the 'MemApp' column from an 'A' to a 'D'... Any help would be greatly appreciated!

/* 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']) || 
    !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 */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

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

/* reset the $result set back to the first row and 
 * display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";
?> 

[thetestingsite]

Something like this?

<?php
/* 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']) || 
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}


if ($_REQUEST['act'] == "del") {

$del = $_REQUEST['del'];

$info = mysql_query("SELECT * FROM tblmembers WHERE `UserID`='$del'");
$q = mysql_fetch_array($info);

$username = $q['username'];

mysql_query("INSERT INTO `deleted` (`id`,`username`,`userid`) VALUES ('null', '$username', '$del')");

mysql_query("DELETE FROM `tblmembers` WHERE `UserID`='$del'");

header('Location: '.$_SERVER["PHP_SELF"]);
}

else {



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

$result = mysql_query ($query);

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

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

/* reset the $result set back to the first row and 
 * display the data */

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>
                <td><a href=\"{$_SERVER['PHP_SELF']}?del=".$column['id']."&act=del\">Delete User</a></td>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";

}
?>

That will insert the username, and userid of the selected user into a "deleted" database table. Then it will delete the original record from the tblmembers table.

Let me know if you need to modify it a little more. Hope this helps.

[tomyknoker]

Ok looks great, yea I think what I initially want to happen is say you look at the Approved members, on each members line in the table I want to have a 'Delete' link, if that's clicked, I just want to make the user from a value 'A' to value 'P', that way they aren't deleted as such and they still are in tblmembers table, they just have 'D' = 'Deleted' as there status... You know what I mean?

[tomyknoker]

Ok looks great, yea I think what I initially want to happen is say you look at the Approved members, on each members line in the table I want to have a 'Delete' link, if that's clicked, I just want to make the user from a value 'A' to value 'P', that way they aren't deleted as such and they still are in tblmembers table, they just have 'D' = 'Deleted' as there status... You know what I mean?

[thetestingsite]

Then you would want something like this:

<?php
/* 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']) || 
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}


if ($_REQUEST['act'] == "del") {

$del = $_REQUEST['del'];
 
mysql_query("UPDATE `tblmembers` SET `status`='D' WHERE `userID`='$del'");


header('Location: '.$_SERVER["PHP_SELF"]);
}

else {



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

$result = mysql_query ($query);

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

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

/* reset the $result set back to the first row and 
 * display the data */

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>
                <td><a href=\"{$_SERVER['PHP_SELF']}?del=".$column['id']."&act=del\">Delete User</a></td>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";

}
?>

Hope this helps.

[tomyknoker]

Ok great well it loads, I haven't tested the 'Delete' yet, just 2 questions, it displays 'Delete User' for every field how can I fix that?

ID Delete? FirstName Delete? LastName Delete? DateOfBirth Delete? Gender Delete? PhoneNumber Delete? MobileNumber Delete? Email Delete? Address Delete? Suburb Delete? City Delete? State Delete? Country Delete? PostCode Delete? MembershipLevel Delete? GeographicalTerritory Delete? PreferredContact Delete? HearAboutUs Delete? PreviousMember Delete? PreviousMemberNumber Delete? OnlineOffers Delete? CocktailCompetitions Delete? RSA Delete? PreviousBarTraining Delete? YearsExperience Delete? CareerStep Delete? CocktailList Delete? JoinDate Delete? MemberApproved Delete? UserName Delete? Password Delete? RemovedDate Delete? rep_NBR Delete? loginDateTime Delete?
193 Delete User Kevin Delete User Clark Delete User 1977-02-27 Delete User M Delete User

AND

How do I just get it to show user's FirstName, LastName, PhoneNumber, MobileNumber, Email, City, State, JoinDate, MemberApproved, loginDateTime... And if possible the email to be an email link and also to keep the date as D/M/Y... Thanks for all your help so far!

[tomyknoker]

Arg, I also want to have an 'Edit', next to 'Delete' for each user... And when that's clicked I wanted a drop down menu to appear which contains 'Approved', 'On Hold', 'Deleted' etc. So say I'm on the 'Approved' list and I want to make one of the members back 'On Hold' I click edit in their table row, select 'On Hold', then submit and it send them to 'On Hold' or whatever is selected...

[thetestingsite]

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

$allowed_display = array('FirstName', 'LastName', 'PhoneNumber', 'MobileNumber', 'Email', 'City', 'State', 'JoinDate', 'MemberApproved', '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']) || 
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}


if ($_REQUEST['act'] == "del") {

$del = $_REQUEST['del'];
 
mysql_query("UPDATE `tblmembers` SET `status`='D' WHERE `userID`='$del'");


header('Location: '.$_SERVER["PHP_SELF"]);
}

else {



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

$result = mysql_query ($query);

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

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);

echo '<script type="text/javascript">
function showForm(id) {

obj = document.getElementById(\'edit_\'+id);

 if (obj.style.display == "none") {
     obj.style.display = "";
 }

 else {
     obj.style.display = "none";
 }
}
</script>
';

echo "<TABLE border=1>\n";
echo "<TR>\n";

foreach ($row as $heading=>$column) {
    /* check if the heading is in our allowed_order
     * array. If it is, hyperlink it so that we can
     * order by this column */
    echo "<TD><b>";

 if (in_array($heading, $allowed_display)) {
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&cat=$cat\">$heading</a>";
    } else {
        echo $heading;
    }                
    echo "</b></TD>\n";
 }
}

<td>Edit</td>
<td>Delete</td>

echo "</TR>\n";

/* reset the $result set back to the first row and 
 * display the data */

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>
<td><a href=\"#\" onclick=\"showForm('".$column['id']."'); return false;\">Edit</a>

<div id=\"edit_".$column['id']."\" style="display: none;">
<!--status form-->

<form method=\"POST\" action=\"{$_SERVER['PHP_SELF']}\">
<input type=\"hidden\" name=\"act\" value=\"edit\">
<input type=\"hidden\" name=\"id\" value=\"".$column['id']."\">

<select name=\"status\">
<option value=\"A\">Approved</option>
<!--enter options for status menu-->
</select>

</div>

</td>

<td><a href=\"{$_SERVER['PHP_SELF']}?del=".$column['id']."&act=del\">Delete User</a></td>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";

}
?>

Not sure how well this will work (or if it even will), but hope this helps nonetheless.

[tomyknoker]

Hiya! Ok I tried it and got an error which was

Parse error: parse error, expecting `','' or `';'' in e:\showMembers.php on line 117
PHP Parse error: parse error, expecting `','' or `';'' in e:\showMembers.php on line 117

... Line 117 is this one

obj = document.getElementById('edit_'+id); 

[thetestingsite]

Sorry, forgot to escape the quotes. Fixed it in the code I posted above, but you can simply replace that line with this:

obj = document.getElementById(\'edit_\'+id);