I am stuck on a problem, and can't seem to figure out why this isn't working, when I am pretty sure it should be...

I am trying to get my code to delete users from the dropdown list after the admin hits the delete button within the page. The form's action is to delete_user.php

Here is the add_delete_users.php page:

<?php

require('config.php');

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAMETWO);


if (mysqli_connect_errno()) {
    die("Connection Failed");
}

if (isset($_GET['pageno'])) {
    $pageno = $_GET['pageno'];
        } else {
            $pageno = 1;
    } // if

?>

<!DOCTYPE html>
<html>
<head>
<title>Manage Users</title>
<link type="text/css" rel="stylesheet" href="/css/layout.css" />
<link type="text/css" rel="stylesheet" href="/css/admin.css" />
<!--[if lte IE 7]><link rel="stylesheet" href="/css/adminie7.css" type="text/css" media="screen" /><![endif]-->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="/js/contentArea.js"></script>
<script type="text/javascript">
$(document).ready(function() {

  $('#content .row:odd').addClass('odd');

  $('#content .row:even').addClass('even');
});
</script>

</head>
<body id="index">
<div class="container-12">
<h1>Manage Users</h1>
<div id="content">
<ul id="statuses">
<li <?php if ($status === "") {echo "class=\"current_page\"";}?>><a href="index.php?&status=" id="nav-new">New</a></li>
<li <?php if ($status === "assigned") {echo "class=\"current_page\"";}?>><a href="index.php?&status=assigned" id="nav-assigned">Assigned</a></li>
<li <?php if ($status === "completed") {echo "class=\"current_page\"";}?>><a href="index.php?&status=completed" id="nav-completed">Completed</a></li>
<li><a href="bought.php">Bought</a></li>
<li class="current_page"><a href="add_delete_users.php">Users</a></li>
</ul>
<div class="clear"></div>

<?php
            $sql = "SELECT * FROM users";
            $result = mysqli_query($mysqli, $sql) or trigger_error("SQL", E_USER_ERROR);
            while ($row = mysqli_fetch_array($result)) {                            
                    $name = $row['name'];
                    $options.="<OPTION VALUE=". $name .">". $name ."</option>";
            }
            echo"<lable><strong>Delete User</strong></lable><form class=\"delete-form\" method=\"post\" action=\"delete_user.php\"><select name=\"user_name\" id=\"user_name\">\n" . $options . "</select>
            <input type=\"submit\" value=\"- Delete User\" /></form><br /><br />";

            echo"<lable><strong>Add User</strong></lable><form class=\"add-form\" method=\"post\" action=\"add_user.php\">
            <input type=\"hidden\" name=\"user_id\" value=\"\" /><br />
            <lable for=\"user_name\">User Name: </lable><input type=\"text\" name=\"user_name\" value=\"\" /><br />
            <lable for=\"email\">Email: </lable><input type=\"text\" name=\"email\" value=\"\" /><br />
            <input type=\"hidden\" name=\"password\" value=\"2f0727a8fd0c695e52bfa79a97b6c08ab418c1db\" />
            <lable for=\"name\">Name: </lable><input type=\"text\" name=\"name\" value=\"\" /><br />
            <input type=\"hidden\" name=\"privileges\" value=\"admin\" /><br />
            <input type=\"submit\" value=\"+ Add User\" /></form>";




mysqli_close($mysqli);              

?>

</div>
</div>
</div>
</body>
</html>`

And here is the delete_user.php script (separate page completely)!

<?php
require('config.php');       
$names = ($_POST['user_name']); 

// Connect to Database to store information

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAMETWO);

if (mysqli_connect_errno()) {
    printf("Connect Failed: %s\n", mysqli_connect_error());
} else {
    $sql = "DELETE FROM users WHERE user_name = '$names' ";
    $res = mysqli_query($mysqli, $sql);

    echo "$names has been deleted!<br />";
    echo "<a href=\"add_delete_users.php\">Go back to Manage more users</a><br /> 
    <a href=\"index.php?&status=\">Go back to the main page</a><br />";

    mysqli_close($mysqli);
}

// End Database interactions



?>

Now I have tried a couple of different iterations of SQL the delete, like $sql = "DELETE FROM users WHERE name = '$names' ";
and then the current one, and they have both worked a couple of times, but then suddenly stop working, without me even changing any of the code!

Pretty weird if you ask me... Anyways, I am sure it is something I have overlooked due to my eyes not being fresh to the code...

Any help is appreciated!

Thanks!

Member Avatar for diafol

Sounds like escape problems:

$names = mysqli_real_escape_string($mysqli, $_POST['user_name']);

If your name contains " or ' it needs to be escaped or it can break the sql statement.

$names = mysqli_real_escape_string($mysqli, $_POST['user_name'])

Nope. This didn't do anything... But thank you!

Member Avatar for diafol

Keep it anyway - it's essential.
As for this:

} else {
    $sql = "DELETE FROM users WHERE user_name = '$names' ";
    $res = mysqli_query($mysqli, $sql);
    echo "$names has been deleted!<br />";
    echo "<a href=\"add_delete_users.php\">Go back to Manage more users</a><br /> 
    <a href=\"index.php?&status=\">Go back to the main page</a><br />";
    mysqli_close($mysqli);
}

You need a check to see if the record really has been deleted:

} else {
    $sql = "DELETE FROM users WHERE user_name = '$names' ";
    $res = mysqli_query($mysqli, $sql);
    if(mysqli_affected_rows($mysqli)){
        echo "$names has been deleted!<br />";
        echo "<a href=\"add_delete_users.php\">Go back to Manage more users</a><br /><a href=\"index.php?&status=\">Go back to the main page</a><br />"; 
    }else{
       echo "Boom! Problem with" . $sql;
    }
}
mysqli_close($mysqli);

@HiWorld,

Deleting entries in mysqli is sometimes problematic. This is the first one I used before I learned how to use PDO.. you will have to go by its proper syntax. prepare->execute->close(), and then finally closing the database connection.

So, if I will be using a mysqli class on my project, instead of PDO, and wanted to delete item from my database, my codes can be something very similar to this. Not the best one I could come up with.. it is Friday which is a lazy day for me as always. No school and no worry :), just chillin' with my coffee mug..

<?php
  ## include your database info.
  require('config.php');

    ## try to connect to your database
    ## instantiate the mysqli class
    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAMETWO);

if (isset($_POST['submit'])&&(!empty($_POST['user_name'])))
    {
            ## clean up a little
            $user = mysqli_real_escape_string($mysqli,$_POST['user_name']);

            ## prepare statement to delete
            if ($deleteThis = $mysqli->prepare("DELETE FROM users WHERE user_name = '".$user."' "))
            {

                    $deleteThis->execute();
                    $deleteThis->close();
            }
            else
            {
               ## you can assign this error to some variable if you want.
               echo "ERROR: could not prepare MYSQLI statement.";
            }
            $mysqli->close();

            ## do whatever you want here after sucessful deletion
            echo "Member: ".$user." has been deleted from Users table.";
    }

    else{

            ## form is submitted empty
            ## can't delete an empty item
            ## send back to previous page.
        }

      ?>

There is catch in my codes above though. For instance, if we want to delete user based on its "id" which is an integer, then the easiest way to do it will be prepare->bind->execute->close()

Veedeo:

Thanks for the reply... I have done what you suggested, but now it isn't even doing anything except the last part

`}
    else{
            echo"Empty... Choose one user, please!";
            echo "<a href=\"add_delete_users.php\">Go back to Manage more users</a><br /> 
            <a href=\"index.php?&status=\">Go back to the main page</a><br />";`

I don't get it!

Diafol:

I did what you suggested and this was the outcome:

Boom! Problem withDELETE FROM users WHERE user_name = 'Michelle'

Member Avatar for diafol

Ok, copy the

DELETE FROM users WHERE user_name = 'Michelle'

into phpmysql and run it from the query box see what happens

DELETE FROM users WHERE user_name = 'Michelle'

Yeah... The thing with that is Michelle is under name in the db, not user_name...

I have change my code to this:

<?php
require('config.php');       

// Connect to Database to store information

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAMETWO);

$names = mysqli_real_escape_string($mysqli, $_POST['name']); 

if (mysqli_connect_errno()) {
    printf("Connect Failed: %s\n", mysqli_connect_error());
} else {
    $sql = "DELETE FROM users WHERE name = '$names' ";
    $res = mysqli_query($mysqli, $sql);

    echo "$names has been deleted!<br />";
    echo "<a href=\"add_delete_users.php\">Go back to Manage more users</a><br /> 
    <a href=\"index.php?&status=\">Go back to the main page</a><br />";

    mysqli_close($mysqli);
}

// End Database interactions


?>

add_delete_users.php (Where the forma are)

<?php

require('config.php');

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAMETWO);


if (mysqli_connect_errno()) {
    die("Connection Failed");
}

if (isset($_GET['pageno'])) {
    $pageno = $_GET['pageno'];
        } else {
            $pageno = 1;
    } // if

?>

<!DOCTYPE html>
<html>
<head>
<title>Manage Users</title>
<link type="text/css" rel="stylesheet" href="/css/layout.css" />
<link type="text/css" rel="stylesheet" href="/css/admin.css" />
<!--[if lte IE 7]><link rel="stylesheet" href="/css/adminie7.css" type="text/css" media="screen" /><![endif]-->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="/js/contentArea.js"></script>
<script type="text/javascript">
$(document).ready(function() {

  $('#content .row:odd').addClass('odd');

  $('#content .row:even').addClass('even');
});
</script>

</head>
<body id="index">
<div class="container-12">
<h1>Manage Users</h1>
<div id="content">
<ul id="statuses">
<li <?php if ($status === "") {echo "class=\"current_page\"";}?>><a href="index.php?&status=" id="nav-new">New</a></li>
<li <?php if ($status === "assigned") {echo "class=\"current_page\"";}?>><a href="index.php?&status=assigned" id="nav-assigned">Assigned</a></li>
<li <?php if ($status === "completed") {echo "class=\"current_page\"";}?>><a href="index.php?&status=completed" id="nav-completed">Completed</a></li>
<li><a href="bought.php">Bought</a></li>
<li class="current_page"><a href="add_delete_users.php">Users</a></li>
</ul>
<div class="clear"></div>

<?php
            $sql = "SELECT name FROM users
                                WHERE (user_id <= '3' OR user_id >= '12')";
            $result = mysqli_query($mysqli, $sql) or trigger_error("SQL", E_USER_ERROR);
            while ($row = mysqli_fetch_array($result)) {                            
                    $name = $row['name'];
                    $options.="<OPTION VALUE=". $name .">". $name ."</option>";
            }
            //This is the delete user form
            echo"<lable><strong>Delete User</strong></lable><form class=\"delete-form\" method=\"post\" action=\"delete_user.php\"><select name=\"name\" id=\"name\">\n" . $options . "</select>
            <input type=\"submit\" value=\"&#45; Delete User\" /></form><br /><br />";

            //This is the add form
            echo"<lable><strong>Add User</strong></lable><form class=\"add-form\" method=\"post\" action=\"add_user.php\">
            <input type=\"hidden\" name=\"user_id\" value=\"\" /><br />
            <lable for=\"user_name\">User Name: </lable><input type=\"text\" name=\"user_name\" value=\"\" /><br />
            <lable for=\"email\">Email: </lable><input type=\"text\" name=\"email\" value=\"\" /><br />
            <input type=\"hidden\" name=\"password\" value=\"2f0727a8fd0c695e52bfa79a97b6c08ab418c1db\" />
            <lable for=\"name\">Name: </lable><input type=\"text\" name=\"name\" value=\"\" /><br />
            <input type=\"hidden\" name=\"privileges\" value=\"admin\" /><br />
            <input type=\"submit\" value=\"&#43; Add User\" /></form>";




mysqli_close($mysqli);              

?>

</div>
</div>
</div>
</body>
</html>

But I am still getting no results... :(

Yeah... The thing with that is Michelle is under name in the db, not user_name...

but your codes above

else {
$sql = "DELETE FROM users WHERE user_name = '$names' ";
$res = mysqli_query($mysqli, $sql);

is using the user_name as the users column name, and not name as you have stated. So, if the column name is name, then both of Adav's and my suggestions should work without any problem..

In fact, I just run a test on my localhost.. including the codes I posted above, and it is working perfectly on my side.

Just change the query to

 $sql = "DELETE FROM users WHERE name = '$names' ";

Okay... So what you are saying is I need to use name through out my code, right? I have changed all of my code so that is all I am using without any reference to user_name, but it is not working on my end... Hmmm. Unless I am totally misunderstanding you, which is VERY possible.

Member Avatar for diafol

Post your modified code and if possible the table structure of your users table.

 if(mysqli_affected_rows($mysqli)){
         echo "$names has been deleted!<br />";
        echo "<a href=\"add_delete_users.php\">Go back to Manage more users</a><br /><a href=\"index.php?&status=\">Go back to the main page</a><br />"; 
     }else{
        echo "Boom! Problem with" . $sql;
     }
 }
 mysqli_close($mysqli);

AND I just did this again, only to my new code and got the same result... The code looks fine to me, and it has all of the right things in there, so why would it not like the statement?

ok,,

Let say we have a table named users, and this table have the following columns.

id -------- name ------- lastName -------- occupation ------- addresss ----- hobby .

and this table have one member named poorBoy, the entry would be something like this

01 ---- poor --------- Boy ---------------- Neo's Side Kick ------ Zion --------- drinking coffee

Then if we want to delete myself from our members table, then the query will be like this

 $names = "poor";

 $sql = "DELETE FROM users WHERE name = '$names' ";

Right... I get and know that. The problem seems to maybe be with how the form is set up? It is pulling the correct information from the dropdown list, just isn't deleting it. I have the add feature working just fine, and have gotten the delete to work, but then WITHOUT changing anything in my code, I will go and try to delete another user, and it "decides" it doesn't want to work. Very puzzling, though.

ok, I see why the codes is not working. let me test if my hunch is correct... maybe not..

And...

What should I do, then. Sorry I am totally fired after working on this for a while. Thanks for all of your help, though!

(sp)...totally fried...

Well I am done til Monday, but any help over the weekend would be stellar! I will reply and start debugging again on Monday morning!

Thanks, folks

ok.. sorry about the wait. I need to do some errands for my Dad...

Here is the complete script I used to test the same environment as yours. The only difference is that I did not make any user edit.

Save this as mysqlitest.php, type in your database credentials, the table name, and the column name. In this case it is name..run this script in your localhost. It should delete selected member on submit of the form.

I tested this script 10 times until all of my test members were completely deleted.. I also take advantage of the OOP the mysqli has to offer.

        <?php
            ## file name mysqlitest.php
        ## fill in the database credentials below
        $host = 'localhost';
        $dbuser = 'root';
        $dbpass = '';
        $dbname = '';

        ## define the following
       # database table
       $table = "users";
       $colName = "name";

        ## try to connect to the database
        ## instantiate the mysqli class.
        $mysqli = new mysqli($host, $dbuser, $dbpass, $dbname);


    ## get members from the database
      if ($resMembers = $mysqli->query("SELECT $colName FROM $table "))
                        { 
                                if ($resMembers->num_rows > 0)
                                {

                                        $option_block = '';

                                        while ($row = $resMembers->fetch_object())
                                        {
                                                $option_block .= '<OPTION  value="'.$row->name.'">'.$row->name.'</OPTION>'; 
                                        }

                                }
                        }


      ## end of members query.

      ## process delete on submit
            if (isset($_POST['submit'])&&(!empty($_POST['user_name'])))
            {
                    ## clean up a little
                    $user = mysqli_real_escape_string($mysqli,$_POST['user_name']);

                    ## prepare to delete
                    if ($deletethis = $mysqli->prepare("DELETE FROM $table WHERE $colName = '".$user."' "))
                    {

                            $deletethis->execute();
                            $deletethis->close();
                    }
                    else
                    {
                            echo "ERROR: could not prepare SQL statement.";
                    }
                    $mysqli->close();

                    ## do whatever you want here after sucessful deletion
                    echo "Member: ".$user." has been deleted from the database";
            }
            ## end of deletion

            else

            ## form is submitted empty
            {
             ?>

         <!-- test form -->        
        <form method = "post" action = "mysqlitest.php">

        <label>User Name</label>
        <!-- notice name = "user_name" -->
        <select name="user_name">
        <?php echo $option_block; ?>
        </select>

        <br/>
        <!-- notice name = "submit" -->
        <input type="submit" name="submit" value="submit"/>
        </form>
        <?php
                }

        ?>

Just adjust your codes similar to what I have here.

This code right here

 $option_block .= '<OPTION  value="'.$row->name.'">'.$row->name.'</OPTION>'; 

refers to the name column.. since mysqli is written in OOP, we can easily do this $row->name , Please pay attention to the form tags. Especially, the submit button and the select..

AWESOME!!!! Thanks so much for the help... Basically what was going on was I just wasn't using mysqli correctly, AND I had the names for the submit button missing, then, right? I just want to learn from this experience. If you wouldn't mind explaining EXACTLY what I was doing wrong that would be fantastic!

Thanks, again, Veedeoo!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.