Trying to get a PHP button to delete MySQL rows

Question

EDIT: Added the whole code for viewProblems, with the actual credential information just ***.

I've been trying to do this all day and I can't figure it out. What I want is a button at the end of each row (the button shows up correctly) that allows me to delete that row from the MySQL database and the page (doesn't delete anything though). The code I have is as follows:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="description" content="">
        <meta name="author" content="">

    <title>View Problems</title>

    <!-- Bootstrap core CSS -->
    <link href="bootstrap/css/bootstrap.min.css" rel="stylesheet">
</head>

<body>

    <div class="well">
        <div class="container">
            <div class="page-header">
                <h1>Jimmy's Laundry</h1>
            </div>
        <ol class="breadcrumb">
              <li><a href="index.html">Home</a></li>
              <li><a href="login.html">Login</a></li>
              <li><a href="adminPage.php">Admin page</a></li>
        </ol>
        </div>
    </div>

        <?php
        $servername = "***";
        $username = "***";
        $password = "***";
        $dbname = "***";
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) 
        {
             die("Connection failed: " . $conn->connect_error);
        }

        $sql = "SELECT problem_id, machine_id, description FROM tbl_problem";
        $result = $conn->query($sql);
        if ($result->num_rows > 0)
        {
            echo '<table class="table table-hover"><tr><th>Problem ID</th><th>Machine Number</th><th>Problem Description</th><th>     </th></tr>';

            while($row = $result->fetch_assoc())
            {
                echo "<tr><td>" . $row['problem_id']. "</td><td>" . $row['machine_id']. "</td><td>" . $row['description']. "</td><td><form action='deleteProblem.php?name=" . $row['problem_id']."' method= 'post'><input type='hidden' name='id' value=".$row['problem_id']."><input class ='btn btn-danger' type='submit' name='submit' value='Resolved?'></form></td></tr>";
            }
            echo "</table>";

        }
        else
        {
            echo "There are no problems! :)";
        }
        ?>
            </table>
</body>

For my main page, viewProblems.php. My deleteProblem.php page is as follows:

<?php
$query= "DELETE FROM tbl_problem WHERE problem_id={$_POST['id']}";
mysql_query ($query);
if (mysql_affected_rows() == 1)
{
    echo "<strong>Row has been deleted</strong>"
}
else
{
    echo "<strong>Deletion Failed</strong>"
}?>

I've been browsing this site and Google, and I'm trying to get it to work, but it just won't. The page loads the table correctly, but when I click the button, it takes me from website/viewProblems.php to website/deleteProblem.php?name=9(or 10, 11, 12, 13, depending on which button I press) but the page is just white space and the database doesn't get updated.

Any help would be appreciated.

P.S. I know that mySQL_ methods are dated, but we have to use them.

You might be stuck with having to use mysql_*() functions, but that doesn't excuse you from writing [sql injection attack](http://bobby-tables.com)-vulnerable code. You're simply assuming your queries could never fail. Did you check mysql_errors()? YOu don't show how/where you connect to the DB, so for all we know your delete query never gets executed at all. — Marc B, Dec 01 '14 at 20:53
start debugging: top of `deleteProblem.php` ass `print_r($_POST);` — , Dec 01 '14 at 20:55
Echo `$query` before executing it. You will see the exact query as it will be executed. Try to use functions like `mysql_error()` to get error information. Also you can try to execute that query in PHPMyAdmin and see if it works there. — GolezTrol, Dec 01 '14 at 20:56
@MarcB Give the poor OP a chance to fix their current problem before ranting on about the SQL injection risks. At the end of the day, it's the OP's own fault if their website is hacked because of SQL-injections. — AStopher, Dec 01 '14 at 21:04
@MarcB, I added the whole code so you could see where I connected. We don't need it to be secure, but while we're on the subject, do you know of any good anti SQL injection attack tutorials? — Jaysen Stoudt, Dec 01 '14 at 21:07
That's because your action is based on a GET method `deleteProblem.php?name` and your query is looking for POST. Plus, you're using `mysqli_` and then `mysql_` in another. Stick to one API. — Funk Forty Niner, Dec 01 '14 at 21:07
@GolezTrol, the second snippet is the whole Php file. I assumed that I didn't have to reconnect to the DB and it worked similarly to a method.... — Jaysen Stoudt, Dec 01 '14 at 21:08
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php If you have to use mysql_*() functions, why are you using mysqli in the original form-building code? that doesn't make any sense. — Marc B, Dec 01 '14 at 21:09
@JaysenStoudt No, that's not the case. It's a separate request, so it is completely isolated from the request that generated the page. So you will have to make a new database connection if you want to query or delete data. — GolezTrol, Dec 01 '14 at 21:09
@MarcB To clarify, it's not so much that we have to use MySQL_*, as we can't use PDO (I think that's what it's called). Honestly, this is my first attempt doing anything with PHP or MySQL, so I've pretty much been just kind of looking around and going with the flow. — Jaysen Stoudt, Dec 01 '14 at 21:13
Then start adding error checking. e.g. `mysql_query(...) or die(mysql_error());`. you still haven't shown how you connect to the db in your `insert` script. but if you're assuming that the mysqli connection you established in the first script will work: it won't. PHP is not persistent and all resources (e.g. db connections) will be terminated when the script exits. And DB handles you establish in one db library (mysqli) are NOT usable in other libraries (mysql). — Marc B, Dec 01 '14 at 21:20
@Fred-ii- Actuall the form contains both the name in the url *and* a hidden field containing the (posted) id. So the form should work, although the id in the url is superfluous. — GolezTrol, Dec 01 '14 at 21:28
@GolezTrol I noticed that after I posted my comment, but decided to keep it there in regards to mixing MySQL APIs. Jumping from one to another and saying he/she can't use `mysqli_` is totally unclear. — Funk Forty Niner, Dec 01 '14 at 21:29

score 2 · Answer 1 · answered Dec 01 '14 at 21:10

2

The script that deletes the row is a separate script that is executed in a separate request. Therefor, it is completely isolated from the request that generated the page and you will have to make a new database connection if you want to query or delete data.

In your current situation, you don't make a connection, so that's why the delete statement fails.

answered Dec 01 '14 at 21:10

GolezTrol

114,394
18
182
210

Not to mention going from one API to another ;) – Funk Forty Niner Dec 01 '14 at 21:15
Since it's a different, isolated script, that actually shouldn't matter. Using `mysql_connect` in the second script would work perfectly fine. – GolezTrol Dec 01 '14 at 21:16
You have a point there. However, if OP is using PHP 5.5 chances are that even having connection codes using `mysql_` functions, it will fail because of deprecation. Least, that's the case for Wamp. – Funk Forty Niner Dec 01 '14 at 21:18
1

True. But that will be the *next* problem. Baby steps. :) – GolezTrol Dec 01 '14 at 21:19
I added the same connection info from the first page. It still does the same thing though. I have also added the print_r($_POST); statement suggested by Dagon and a mysql_error(), but nothing shows up when I click the buttons still. – Jaysen Stoudt Dec 01 '14 at 21:23
The first script uses `mysqli_connect` (part of the mysql**i** API). The second script uses `mysql` functions (without **i**). So in the second script, you either need to use `mysql_connect` or change the other functions to their `mysqli` equivalents. – GolezTrol Dec 01 '14 at 21:25
@GolezTrol OP states that they have to use ``mysql_`` functions, not ``mysqli_``. – Dec 01 '14 at 21:26
1

@TheElm, I think you haven't read the first script in the question and the rest of the comments below it. – GolezTrol Dec 01 '14 at 21:26
@GolezTrol, I have; They state in a comment they have to stick to ``mysql_*``, but they just need to modify the first script so they stick to one API. – Dec 01 '14 at 21:29
Well, it's a bit unclear, but it doesn't really matter. I just said they need to match, so all of the functions need to be either mysql_* or mysqli_*, which one to use is up to OP. – GolezTrol Dec 01 '14 at 21:31
I changed the second script from mysqli to mysql_connect, and did @TheElm's suggestion in his answer, and still I'm getting nothing. – Jaysen Stoudt Dec 01 '14 at 21:37
@JaysenStoudt Have you checked your Apache error_log? – Dec 01 '14 at 21:39
@TheElm It it bad that I don't know what that is? – Jaysen Stoudt Dec 01 '14 at 21:40
@JaysenStoudt No, a lot of people tend not to. Its a log where all errors are returned to. If you're running on a local machine it should be in ``/var/log/apache2/error_log``. When scripting I tend to open terminal and run ``tail -f /var/log/apache2/error_log`` so it automatically shows new errors. – Dec 01 '14 at 21:43
* Yes, although a lot of people tend not to. ;) – GolezTrol Dec 01 '14 at 21:47
@TheElm and GolezTrol Thank you both so much for your help, but it just wasn't working for me. So after a little longer of searching Google I found this tutorial that I followed and it worked! I really appreciate your help, however! :) https://www.youtube.com/watch?v=od0UM78JXg0 – Jaysen Stoudt Dec 01 '14 at 22:16

Trying to get a PHP button to delete MySQL rows

1 Answers1