Deleting record from MySQL

[keeps21]

I'm having a bit of trouble creating a page to delete a story from the content table in my database.

I have checked my DELETE query directly in phpmyadmin and it is working fine.

I am echoing the query to check that it comes out as expected and it looks fine.

The problem seems to be that when my code is run the record never gets deleted.

Meaning that mysql_affected_rows() is always -1 and therefore an error message - 'Could not delete the story' is always output.

I just can't figure out why, Any help would be greatly appreciated.

Thanks.

Here is my code
[php]
<?php
# Filename - delete_story.php
# Date - 10th August 2007
# Author - Stephen Hoult
# Author Email - [e-mail]

// This file allows logged in users to delete a story.

// Include config file for error management and such
include('./includes/config.inc.php');

// Set page title and include HTML header
$page_title = 'Delete a Story';
include('./includes/header.html');


// If no first_name variable exists, redirect the user.
if (!isset($_SESSION['first_name'])) {

// Start defining the URL.
$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);
// Check for a trailing slash.
if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
$url = substr ($url, 0, -1); // Chop off the slash.
}

// Add the page.
$url .= '/login.php';

ob_end_clean(); // Delete the buffer.
header("Location: $url");
exit(); // Quit the script.

} else { // First name variable exists - user is logged in

// Check to see if ID exists
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) {
$id = $_GET['id'];

} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) {
$id = $_POST['id'];

} else { // Kill The script
echo '<p><font color="red" size="+1">That ID does not exist. Please select a Valid ID</font></p>';
include('./includes/footer.html'); // Include HTML footer
exit();

} // End of If ID exists


// Connect to the database
require_once('../mysql_connect.php');

// Check if the form has been submitted
if (isset($_POST['submitted'])) {


if ($_POST['sure'] == 'Yes') { // Delete the story - User selected Yes

// Make the query
$query = "DELETE FROM content WHERE id = $id LIMIT 1";
$result = mysql_query($query); // Run the query


echo $query; //For debugging purposes
echo mysql_affected_rows(); //For debugging purposes


if (mysql_affected_rows() == 1) { // Query ran ok

// Print a message

echo '<p>The story has been deleted.</p>';

} else { // If it didn't run ok

echo '<p><font color="red" size="+1">The story could not be deleted. Please try again.</font></p>';

} // End if query ran ok


} else { // Wasn't sure about deleting - User selects No
echo '<p><font color="red" size="+1">The story was not deleted.</font></p>';
}

} else { // else the form was not submitted - Show the form


?>
<fieldset><legend>Delete a story</legend>
<p> Are you sure you want to delete this story?</p>

<form action="delete_story.php" method="post" >
<p><input type="radio" name="sure" value="Yes" >Yes</p>
<p><input type="radio" name="sure" value="No" checked="checked" >No</p>

<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="text" name="id" value="<?php $_POST['id'] = $id; echo $_POST['id'] ;?>">

</form>

<?php

} // End of is submitted
} // End if logged in

mysql_close(); // Close the database connection
include('./includes/footer.html'); // Inlcude the HTML footer.
?>[/php]

[Atli]

Hi.

Try adding this line if your query fails. It will print out any error that MySQL returns on a failed query.

echo mysql_error();
 

[jx2]

[PHP]
$query = "DELETE FROM content WHERE id = $id LIMIT 1";
$result = mysql_query($query); // Run the query

[/PHP]
well i think this is the line which might cos your problem

should be:

id = '$id'

and is the LIMIT 1 necessary in here?

regards
jx2

[keeps21]

SOLVED

The single quotes around the $id variable in the string are unneccesary.

I added the mysql_error() and recieved the following message:

Table 'content.content' doesn't exist

Problem was I had included the wrong mysql_connect.php file

This was flagged by the mysql_error() message showing that the query was looking for a database named 'content' instead of the content table on the my_cms database

Thanks for your help.

[Atli]

[PHP]
$query = "DELETE FROM content WHERE id = $id LIMIT 1";
$result = mysql_query($query); // Run the query

[/PHP]
well i think this is the line which might cos your problem

should be:

id = '$id'

and is the LIMIT 1 necessary in here?

regards
jx2

Quotes should only be used arround string values (including dates and binary strings), numeric values should not be quoted.

You should always use LIMIT clause if it is at all possible when deleting or updating data. There is always a possibility that something might go wrong, or that somebody makes it go wrong, and this prevents such an error from whiping out your entire table.

[Atli]

SOLVED
..

Glad you found your answer.

[jx2]

great men :-)

i didnt notice you did checked it if that was a number :-)

jx2