Page 1 of 1

Delete Script Not Working

Posted: Thu Jan 29, 2009 10:24 pm
by p3rk5
I've used this code before but now that I've transferred servers it's not working. If anyone could take a look at this and see what the deal is I'd greatly appreciate it. Thanks!

Code: Select all

<?php session_start (); ?>
<style type="text/css">
.bodyspan3 {
    color: #FFFFFF;
    font-weight: bold;
    background-color: #666666;
}
</style>
<h3>Delete Users</h3>
<?php
$host="localhost"; // Host name
$username="***"; // Mysql username
$password="****"; // Mysql password
$db_name="login"; // Database name
$tbl_name="users"; // Table name
$logged_in = $_SESSION['logged_in'];
$level = $_SESSION['level'];
$alternate = "2"; 
if ($logged_in && $level == '1') {
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");
    $sql="SELECT * FROM `$tbl_name` ORDER BY `username` ASC";
    $result=mysql_query($sql);
    $count=mysql_num_rows($result);
?>
<table border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table>
<tr bgcolor="#666666">
<td align="center"></td>
<td align="center"><span class="bodyspan3">Username</span></td>
<td align="center"><span class="bodyspan3">Name</span></td>
<td align="center"><span class="bodyspan3">Level</span></td>
<td align="center"><span class="bodyspan3">Active</span></td>
</tr>
<?php
    while($r=mysql_fetch_array($result)){
        $db_id = $r["id"];
        $db_first = $r["first"];
        $db_last = $r["last"];
        $db_username = $r["username"];
        $db_level = $r["level"];
        $db_active = $r["active"];
        if ($alternate == 1) {
            $color = "#CCCCCC";
            $alternate = 2;
        } else {
            $color = "#FFFFFF";
            $alternate = 1;
        } if ($db_level == 0) {
            $userlvl='Member';
        } elseif ($db_level == 2) {
            $userlvl = 'Moderator';
        } else {
            $userlvl='Admin';
        } if ($db_active == 0) {
            $active2='<a href="edit.php?id='.$db_id.'&enable=1" style="color:#FF0000;">No</a>';
        } else {
            $active2='<a href="edit.php?id='.$db_id.'&enable=0" style="color:#339900;">Yes</a>';
        }
        echo '<tr bgcolor="'.$color.'">
<td align="center"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="'.$db_id.'"></td>
<td><span>'.$db_username.'</span></td>
<td><span>'.$db_first.' '.$db_last.'</span></td>
<td><span>'.$userlvl.'</span></td>
<td><span>'.$active2.'</span></td>
</tr>';
}
?>
<tr>
<td colspan="5" align="center"><input name="delete" type="submit" id="delete" value="Delete" class="input2"></td>
</tr>
<?
if($delete && $level == '1'){
for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}
}
if($result){
echo "Member(s) deleted";
}
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>
<?php
} else {
echo '<h1>Forbidden</h1><p class="logintxt">You are not authorized to view this page</p>';
}
?>

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 1:42 am
by infolock
What errors are you getting?

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 6:01 am
by p3rk5
It says Member(s) deleted without clicking submit and also isn't deleting the users selected.

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 6:08 am
by sdas
I hope the php user has delete permissions on the table.
Also check for encodings in mysql. In case $del_id is a string you would need to add the encoding scripts.

Try separating the php var from string. Some servers expect this.
$sql = "DELETE FROM ". $tbl_name." WHERE id LIKE '".$del_id."'";

I hope you have the $del_id as numeric , else you should check for spaces, cases , etc

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 6:22 am
by p3rk5
sdas wrote:I hope the php user has delete permissions on the table.
The user does.
sdas wrote:Also check for encodings in mysql. In case $del_id is a string you would need to add the encoding scripts.
What do you mean by this?
sdas wrote:Try separating the php var from string. Some servers expect this.
$sql = "DELETE FROM ". $tbl_name." WHERE id LIKE '".$del_id."'";
I tried this and the same thing happens.

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 10:47 am
by infolock
try echoing out the $sql variable.

Also, when you do a mysql_query, issue an OR DIE statement with it to see if maybe the query is failing...

example:

Code: Select all

 
<?php
$sql = "DELETE FROM TABLE WHERE name = 'foo'";
echo "<br />$sql<br />";
mysql_query($sql) or die(MySQL_Error());
?>
 
Finally, grab the echo'ed query string and try to run it manually from a console window and see if it works. could just be an issue with your query...

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 10:53 am
by Attero
Is TABLE the name of your table? Because that will be a reserved word and it will just fail straight off.

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 11:08 am
by p3rk5
infolock wrote:try echoing out the $sql variable.
When echoed it come out correct.
SELECT * FROM `users` ORDER BY `username` ASC
infolock wrote:Also, when you do a mysql_query, issue an OR DIE statement with it to see if maybe the query is failing...
When I visit the page the "User's deleted message appears. Also appears when I click delete but nothing gets deleted.
infolock wrote:Finally, grab the echo'ed query string and try to run it manually from a console window and see if it works. could just be an issue with your query...
Not sure how to do that.

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 11:29 am
by infolock
well if you're only seeing a select message then the delete query isn't being called.

you need to start echoing out line numbers and test values inside of if statements to determine where an if you are expecting to ring true isn't, or where the script is stopping before it gets to the delete line.

Re: Delete Script Not Working

Posted: Fri Jan 30, 2009 11:46 am
by p3rk5
I fixed it so the message only is displayed after the button is pressed but the deletion still is not working.

Re: Delete Script Not Working

Posted: Sat Jan 31, 2009 12:20 am
by p3rk5
I couldn't figure out what was wrong so I decided to write my own script. After messing with it for a while and trying to get it to work, I finally got it. Here's the final result:

Code: Select all

<?php session_start (); ?>
<?php
    $host="localhost"; // Host name
    $username="***"; // Mysql username
    $password="***"; // Mysql password
    $database="login"; // Database name
    $logged_in = $_SESSION['logged_in'];
    $level = $_SESSION['level'];
    $alternate = "2";
    $id=$_POST['id'];
    $order = $_GET['order'];
    $submit = $_POST["submit"];
    if (!$order) {
        $order = 'username';
    } elseif ($order == 'username') {
        $order = 'username';
    } elseif ($order == 'name') {
        $order = 'first';
    } elseif ($order == 'level') {
        $order = 'level';
    } elseif ($order == 'active') {
        $order = 'active';
    } else {
        $order = 'username';
    } if (!isset($_GET['page_num']) || $_GET['page_num'] <= 0 || !is_numeric($_GET['page'])) {
        $page = 1;
    } else {
        $page = $_GET['page'];
    }
    $max_results = 15;
    $from = (($page * $max_results) - $max_results); 
    if ($logged_in && $level == '1' && !isset($submit)) {
        mysql_connect("$host", "$username", "$password")or die("cannot connect");
        @mysql_select_db("$database")or die("cannot select DB");
        $query = "SELECT * FROM `users` ORDER BY `$order` ASC LIMIT $from, $max_results";
        $result=mysql_query($query);
        echo '<form action="main.php?id=users&page=delete" method="post"><h3>Delete Users</h3><table align="center" width="70%"><tr><td colspan="3">';  if($page > 1){
        $prev = ($page - 1);
        echo '<a href="main.php?id=users&page=view=page_num='.$prev.'">Previous</a> -';
    } else {
        echo 'Previous - ';
        } for($i = 1; $i <= $total_pages; $i++){
            if(($page) == $i){
                echo ''.$i.' - ';
            } else {
                echo '<a href="main.php?id=users&page=view=page_num='.$i.'">'.$i.'</a> - ';
        }
        } if($page < $total_pages){
                $next = ($page + 1);
                echo '<a href="main.php?id=users&page=view=page_num='.$next.'">Next</a>';
        } else {
            echo 'Next';
        }
        echo '<tr bgcolor="#000000">
    <td align="center" width="0%"></td>
    <td align="center"><a href="main.php?id=users&page=delete&order=username" class="tablelink">Username</a></td>
    <td align="center"><a href="main.php?id=users&page=delete&order=name" class="tablelink">Name</a></td>
    <td align="center"><a href="main.php?id=users&page=delete&order=level" class="tablelink">Level</a></td>
    <td align="center"><a href="main.php?id=users&page=delete&order=active" class="tablelink">Active</a></td>
    </tr>';
        while($r=mysql_fetch_array($result)){
            $db_id = $r["id"];
            $db_first = $r["first"];
            $db_last = $r["last"];
            $db_username = $r["username"];
            $db_level = $r["level"];
            $db_active = $r["active"];
            if ($alternate == 1) {
                $color = "#293843";
                $alternate = 2;
            } else {
                $color = "#546879";
                $alternate = 1;
            } if ($db_level == 3) {
                $userlvl='Member';
            } elseif ($db_level == 2) {
                $userlvl = 'Moderator';
            } else {
                $userlvl='Admin';
            } if ($db_active == 0) {
                $active2='<a href="users/edit.php?id='.$db_id.'&enable=1" style="color:#FF0000;">No</a>';
            } else {
                $active2='<a href="users/edit.php?id='.$db_id.'&enable=0" style="color:#339900;">Yes</a>';
            }
            echo '<tr bgcolor="'.$color.'">
    <td align="center" width="0%"><input type="checkbox" name="id" value="'.$db_id.'" /></td>
    <td><span>'.$db_username.'</span></td>
    <td><span>'.$db_first.' '.$db_last.'</span></td>
    <td><span>'.$userlvl.'</span></td>
    <td><center><span>'.$active2.'</span></center></td>
    </tr>';
        }
        mysql_close();
        echo '<tr><td colspan="5"><center><input type="submit" name="submit" value="Delete Selected" /></center></form></td></tr></table>';
    } elseif (isset($submit) && $logged_in && $level == '1') {
        mysql_connect("$host", "$username", "$password")or die("cannot connect");
        @mysql_select_db("$database")or die("cannot select DB");
        $query = "SELECT `id` FROM `users`";
        $result=mysql_query($query);
        $count=mysql_num_rows($result);
        for ($i=0;$i<$count;$i++) {
            mysql_query("DELETE FROM `users` WHERE `id`='$id' LIMIT 1") or die(MySQL_Error()); 
        }
        mysql_close;
        echo '<h3>Delete Users</h3><br><table align="center"><tr><td><center><span>User(s) deleted successfully!</span> <a href="main.php?">Back to Main</a>.</center></td></tr></table>';
        echo '<br>'.$id.'';
    } else {
        echo '<h1>Forbidden</h1><p class="logintxt">You are not authorized to view this page</p>';
    }
?>