Hey All, I am having a hard time learning php. I am trying to delete an item from my database but it isnt working.
I thought I had it working because there was no errors but the item is still in the database.
Here is what I have.
A link to delete.php then I have this for delete.php
<?php
ob_start();
// contact to database
$host = "localhost";
$username = "admin";
$password = "mypassword";
$database="database";
$tbl_name="new_equip";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query=("SELECT * FROM new_equip");
$result=mysql_query($query);
$row=mysql_fetch_array($result);
$id=$row[0];
mysql_query("DELETE from new_equip WHERE id='$id'");
header("location:inventory.php");
?>
I would also like for it to say something like “Item was successfully deleted” when they get to the inventory.php page.
I know this is probably something simple and I have been searching and trying everything I can find, but I cannot seem to get it working.
If you are learning PHP, then you’d better stop immediately using the mysql_ functions, and switch to mysqli_ or pdo. It will save you from wasting time learning stuff that won’t be around anymore in the next PHP version.
As far as your code is concerned, don’t use @ because it suppresses errors, so you won’t see them, and when developing and debugging, you want to see them.
Further, don’t put () around string values:
$query= "SELECT * FROM new_equip";
I believe the id is numeric? So don’t put quotes around it. And you can use the “or die” construction on the query too:
mysql_query("DELETE from new_equip WHERE id= $id") or die("error in query : " . mysql_error());
Final comment: I don’t understand the logic of your delete script. Instead of deleting a row you’ve selected, you just delete the first one in the table?
If you’re learning PDO for the first time like already stated, it pays to start off on the right foot in my opinion.
<?php
/* Usually goes in a separate file (connect.php for example) */
define('DATABASE_HOST', 'Host');
define('DATABASE_NAME', 'Database');
define('DATABASE_USERNAME', 'Username');
define('DATABASE_PASSWORD', 'Password');
$db_options = [
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
try {
/* Connect and create the Database Object */
$pdo = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
/* Set the query up to delete the record */
/* using column id with a prepared statement */
$query = 'DELETE FROM new_equip WHERE id=:id';
/* prepare the statement */
$stmt = $pdo->prepare($query);
/* execute the statement while setting the id prepared statement */
$result = $stmt->execute(array(':id' => $id));
if ($result) {
echo 'Record Successfully Deleted';
}
}
catch(PDOException $e) {
echo $e->getMessage();
}
Oh and if you haven’t done so, turn on errors →
I am going to learn mysqli but I need to get this working before I start the changeover. So if anyone can help me to get this while I am learning mysqli I would appreciate it. I would start it right now but I need the site functional because there are a bunch of pages that need switched over.
Learning how to use DEPRECATED code that will soon break and require more work
Not to mention that it requires more work to make it secure
vs.
Learning how to use current code that will work for the foreseeable future
And solves many of the security issues for you easily
I fail to see how learning one over the other would affect development time.
Because this is the only thing I am needing for the site to be complete, then while its live I can go in and redo it correct with mysqli while it is live
The function mysql_query returns a value. If it is a delete query, the function returns true upon success and false otherwise.
If it fails, use mysql_error.
One more thing: it’s time to switch to mysqli for mysql is deprecated and will be removes in the future.
If after executing DELETE query the row is still in the database it is possible that you have to issue a COMMIT transaction statement. This is needed if autocommit is set to false.
Although we can agree that there are issues with the provided code (post#1), the connection code works (as is, with or without error suppression and without setting a $connect variable as suggested in post#16). The query string (even with parenthesis) works. Even though he is calling all fields using * instead of the field id, the first record from the table is returned even though there might be many records, but as he has not specified WHERE conditions, that is what is returned. The DELETE query even with quoted variable does delete the record.
So though there are better ways to write this code, I don’t see any reason why it wouldn’t work.
This is assuming the field id IS the first field in the table.
I would echo the $id variable before the delete query to see if in fact that first query is returning expected result.
This is why I was asking about the database table layout - as it’s already been mentioned that all this does is delete the first record (though not specifying the order might make it not the expected first record) I got the impression from the very brief “that doesn’t work” response that it wasn’t even doing that. But all speculation without the OP.
