Issue when deleting rows from my database using html, php, js

Question

I'm making an app in which I have this table:

<?php
require_once 'Connect2db3.php';
?>    
<form>    
<fieldset>
<article class="rondehoeken"> 
<header>
    <div class="streep1"></div>
    <div class="streep2"></div>
    <div class="streep3"></div>
    <div class="streep4"></div>
    <div class="streep5"></div>
    <h1 id="artikel-titel" >Op Vooraad</h1>
</header>

<div id="artikel-container">    
<table class="table 1">
<thead>
 <title>Inventory Grid.html</title>
    <meta charset = "UTF-8" />
    <style type = "text/css">
    table, td, th {
      border: 1px solid black;
    } 
    </style>
</thead>    
<tbody>
 <?php
$con=mysqli_connect("localhost","root","admin","inventarisdb");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM BCD");

echo "<table border='0'>
<tr>
<th>Categorie</th>
<th>SerieNummer</th>
<th>MacAdress</th>
<th>ProductCode</th>
<th>Prijs</th>
<th>RekNummer</th>
<th>PaletNummer</th>
<th>Hoeveelheid</th>
<th>Aantekeningen</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Categorie'] . "</td>";
  echo "<td>" . $row['SerieNummer'] . "</td>";
  echo "<td>" . $row['MacAdress'] . "</td>";
  echo "<td>" . $row['ProductCode'] . "</td>";
  echo "<td>" . $row['Prijs'] . "</td>";
  echo "<td>" . $row['RekNummer'] . "</td>";
  echo "<td>" . $row['PaletNummer'] . "</td>";
  echo "<td>" . $row['Hoeveelheid'] . "</td>";
  echo "<td>" . $row['Aantekeningen'] . "</td>";
  echo '<td><input type="hidden" class="entryid" name="id" value='.$row['ID'].' /><a href="#" class="delete">delete</a></td>';
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

</article>
</fieldset>
</form>

In the header:

Delete form:

<?php
$db = array (
    'host' => 'localhost',
    'user' => 'root',
    'pass' => 'admin',
    'dbname' => 'inventarisdb'
);

if(!mysql_connect($db['host'], $db['user'], $db['pass']))
{
    trigger_error('Fout bij verbinden: '.mysql_error());
}
elseif(!mysql_select_db($db['dbname']))
{
    trigger_error('Fout bij selecteren database: '.mysql_error());
}
else
{
    $sql = "SET SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY'";
    if(!mysql_query($sql))
    {
        trigger_error('MySQL in ANSI niet mogelijk');
    }
}

$id = $_GET['id'];
$sql="DELETE FROM BCD WHERE id='$Categorie', '$SerieNummer', '$MacAdress', '$ProductCode', '$Prijs', '$RekNummer','$PaletNummer' ,'$Hoeveelheid', '$Aantekeningen'";
?>

This table looks up data from another table and also provides the option to delete a row in that table from the database.

Everything I just said this table does works with this script, except for the data being actually deleted from my Database. Upon pressing delete the delete action gets executed with a prompt an all saying that ur about to delete a row. It removes it from the table but when u check in the database or simply refresh the page with the table, That row is still there and hasn't been deleted

Any ideas why this is happening or what to do ? Or maybe how to do it easier ?

it's better to move your title tag inside the head tag!

Hassan Khodadadeh
– Hassan Khodadadeh

2013-08-22 07:22:28 +00:00
Commented Aug 22, 2013 at 7:22 — Hassan Khodadadeh
– Hassan Khodadadeh, Commented Aug 22, 2013 at 7:22

allen213 · Accepted Answer · 2013-08-22 07:57:27Z

3

I don't think this will work

$sql="DELETE FROM BCD WHERE id='$Categorie', '$SerieNummer', '$MacAdress', '$ProductCode', '$Prijs', '$RekNummer','$PaletNummer' ,'$Hoeveelheid', '$Aantekeningen'";

You should separate the parameters with AND or OR, depending on what your successful delete criteria needs not a comma, also provide the column name each time.

Something like

$sql="DELETE FROM BCD WHERE id='$Categorie' AND  serial_number='$SerieNummer', //etc

It would be better to delete based on a primary key rather than such a range of values

Also do not use mysql_ functions, use mysqli or PDO with parameterised queries

edited Aug 22, 2013 at 7:57

answered Aug 22, 2013 at 7:22

allen213

2,2692 gold badges15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Kentje Over a year ago

I changed it all to AND which I should use rather than OR in this case, but I get the exact same executed actions and results.

vee Over a year ago

@Kentje, This is not going to work for you because non of those variables that you've used are defined.

allen213 Over a year ago

provide the column name for each of the ANDs/ORs

Kentje Over a year ago

@ vinodadhikary, What do u mean by this? All the variables I used are fields in my tables in my DB.. how could they not be defined? or what do u mean? And how do u suggest I make it work?

vee Over a year ago

@Kentje, I am talking about the variables like $Categorie, $SerieNummer etc.

|

Babak Bandpay · Accepted Answer · 2013-08-22 08:03:53Z

2

as it is obvious in your code, you don't execute the query.

answered Aug 22, 2013 at 8:03

Babak Bandpay

1776 silver badges15 bronze badges

1 Comment

allen213 Over a year ago

This is true unless he forgot to paste that bit - the query in the original would not work though

vee · Accepted Answer · 2013-08-22 07:41:19Z

1

Your hidden input is named id and you are calling $id = $_GET['id'], all other variables you have in your query are undefined because you haven't shown where you've defined them if you have defined them.

Replace:

$id = $_GET['id'];
$sql="DELETE FROM BCD WHERE id='$Categorie', '$SerieNummer', '$MacAdress', '$ProductCode', '$Prijs', '$RekNummer','$PaletNummer' ,'$Hoeveelheid', '$Aantekeningen'";

with:

$id = intval($_GET['id']); // assuming your id column is integer type.
$sql="DELETE FROM BCD WHERE id=$id";

Also note that mysql_ functions are deprecated. You should be using either mysqli or pdo.

edited Aug 22, 2013 at 7:41

answered Aug 22, 2013 at 7:29

vee

38.7k7 gold badges76 silver badges80 bronze badges

13 Comments

Kentje Over a year ago

U mean '$ID'"; then right? And it didn't make a difference after I just tried, I get the same executed action but still it doesn't delete the stats from my DB.

vee Over a year ago

No $id from <input type="hidden" class="entryid" name="id" value='.$row['ID'].' />

Kentje Over a year ago

When I do this I get an error in my script saying that id = undefined variable and my table no longer displays the data it looks up

vee Over a year ago

@Kentje, There was a missing closing double quotes " in my query. Please try that again.

Kentje Over a year ago

My input class = entryid, name = id and value = $row, All my variables are defined as $ row(s) so how can this not work, I don't think the probleml lies here, tho i much appreciate ur help and advice! but after making the chages u suggested I get either the same results or an error which doens't let me see my table :S

|

Tieson T. · Accepted Answer · 2013-09-05 03:43:09Z

Based on what your sample code demonstrates, I came to the same conclusion as @BabakBandpay; you never actually run the DELETE query.

Regardless of whether that is true or not, while it's a good step to move to the MySQLi library, you're still using code that is very susceptible to SQL injection. Try using a prepared statement instead: http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Here's an example based off of your code (and the API documentation):

<?php

    $mysqli = new mysqli('localhost', 'root', 'admin', 'inventarisdb');

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    // ... the rest of your code

    $id = $_GET['id'];
    $stmt = $mysqli->prepare("DELETE FROM BCD WHERE id=?");
    $stmt->bind_param("i", $id);

    $success = $stmt->execute();

    $mysqli->close();
?>

This code, while untested and not really production-quality, is quite a bit safer just by the nature of bound statements:

Bound variables will be escaped automatically by the server.

I was more of a fan of PDO when I still coded in PHP, but using the OOP verion of MySQLi is a decent compromise, so I'd highly recommend reading through the examples in the documentation.

EDIT: As an addendum, I'd also recommend running your input variable(s) through the filter module (http://www.php.net/manual/en/book.filter.php). Assuming $id should be an integer, I'd update the assignment to:

$id = filter_var($_GET['id'], FILTER_SANITIZE_NUMBER_INT);

This will remove everything from $_GET['id'] that isn't a digit or a +/- sign.

Thank you! I will read this through and try it out! Much oblidged!

Collectives™ on Stack Overflow

Issue when deleting rows from my database using html, php, js

4 Answers 4

10 Comments

1 Comment

13 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

1 Comment

13 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related