deleting customer records

[Anon]

Hello,

Everybody usually goes over creating databases, adding to tables, modifying entries and such. So what about removing the users entries to keep the database clean? For some reason, Im having a hard time finding tutorials that go over this topic. All I really want to do is remove a users "row" from a table. Is this the wrong method anyway?

Should I be making a table for each user, then having the php script remove the whole table?

Ok, I have a Webclass User Administration Panel, and you can do various things to the user entries in the database using the tool. On the delete username tool there is a first page that grabs the usernames from the "user" column in the database, and populates a drop down list with a submit button right next to it in an HTML form. After clicking the button, the script "remuser.php" is activated, and the username selected from the form is sent to it.

My problem is that the script reports the output: "0 user entry removed" without any errors or warnings, and of course, the users entry is never removed from the table. (code below)

The "user" column is not the primary key, but a column called "Login_ID" is. Can you only remove a row in a table using the primary key column? If so, how do I set it up so that the users Login_ID can be used to remove the whole row, but from selecting the username from the drop down list?

Here is the HTML and PHP for the form "RemoveUser.php":
...blah, blah, blah, server and database connection code...

$db is the container for the database connection code

here is the meat:

/ Set up the query and proceed to query the database for the user column
/
$query = "SELECT user FROM login
ORDER BY user ASC";
$query_result = mysql_query($query, $db);
if (!$query_result)
{
echo "Unable to query the database";
exit;
}

/ Gather the rows from the user column and assign them to $option_block
/

while ($row = mysql_fetch_array($query_result))
{
$username = $row["user"];

/ Set $option_block up to display the entries in the drop down list
/

$option_block .= "<OPTION value=\"$username\">$username</OPTION>";
}
?>

<form name="RemoveUserName" method="post" action="remuser.php">
Select Username to remove:
<select name="User_Name">
<?
echo "$option_block";
?>
</select>
<input type="submit" name="Submit" value="Remove Selected">
</form>
</body>

Here is the PHP code in the script "remuser.php":

...blah, blah, blah... server and database connection code...
and heres the meat of the problem:

/ Remove the username from the previous page from the database
/
$query = "DELETE FROM login WHERE user = '$username'";
$result = mysql_query ($query);
if (!$query)
{
echo "Error while removing user entry.";
exit;
}
else
{
echo mysql_affected_rows()." user entry removed.";
}
?>

</p>
<p>To return to Control Panel Index, click <a href="/useradmin/indexWorkingframe.htm" target="_self">here</a>.

Thank you so much in advance for you help

Jon Moore
VP of IT
adhdservices.org

[Anon]

Two problems.

You are using two different variables for the user name. You pass $User_Name to remuser.php from the select box, but your delete query is expecting $username.

The reason it always says no errors and 0 rows affected is because you are checking to see if $query has a value, and you should be checking to see the value of $result. You know $query has a value, becuase you assigned the query string to it, so it will always evaluate to true.

I think that will fix you up.

[Anon]

David R is absolutely correct.

in the following block of code:

{
<select name="User_Name">
<?
echo "$option_block";
?>
</select>
}

<select name="User_Name">

should be

<select name="username">

or

$query = "DELETE FROM login WHERE user = '$username'";

should be

$query = "DELETE FROM login WHERE user = '$User_Name'";

Also...

if (!$query)
{
echo "Error while removing user entry.";
exit;
}

should be

if (!$result)
{
echo "Error while removing user entry.";
exit;
}

---

Now, my suggestion:

Can your login table have duplicate usernames? If not, then you'll be okay, however I would suggest using Login_ID to query your record for deletion. I would also make sure that Login_ID is set to auto_increment (probably already is I assume.)

So......Here's the new code......

/ Set up the query and proceed to query the database for the user column
/
$query = "SELECT Login_ID,user FROM login
ORDER BY user ASC";
$query_result = mysql_query($query, $db);
if (!$query_result)
{
echo "Unable to query the database";
exit;
}

/ Gather the rows from the user column and assign them to $option_block
/
while ($row = mysql_fetch_array($query_result))
{
$id = $row["Login_ID"];

$username = $row["user"];

/ Set $option_block up to display the entries in the drop down list
/

$option_block .= "<OPTION value=\"$id\">$username</OPTION>";
}

moving on........

<select name="userid">
<?
echo "$option_block";
?>
</select>

moving on........

DELETE FROM login WHERE Login_ID = '$userid'";