deleting a databse record!

[TrueJoker]

I am trying to use php to delete a record from a database. the code i am using is this:
<?php

require_once ('mysql_connect.php');

if (!isset($_GET['do']) || $_GET['do'] != 1) {
?>
<p align="center">
Are you sure you want to delete this <?php
echo $_GET['type']; ?>?<br>
<a href="<?php echo $_SERVER['REQUEST_URI']; ?>&do=1">Yes</a>
or <a href="view.php">View Databasebase</a>
</p>
<?php
} else {
// delete a record

$sql = "DELETE FROM 'Personel'
WHERE " . $_GET['type'] . "
LIMIT 1";
//echo SQL for debug purpose
echo "<!--" . $sql . "-->";
$result = mysql_query($sql)
or die("Invalid query: " . mysql_error() );

?>
<p align="center">
Your <?php echo $_GET['type']; ?> has been deleted.
<a href="view.php">View Database</a>
</p>
<?php
}
?>

as i am still getting to grips with php and mysql i have tried to adapt an example from a book to suit my own means, so its probably way off base. The error that is displayed is :

You have an error in your SQL syntax near ''Personel' WHERE Surname=Carrick LIMIT 1' at line 1

The database i ahve created consists of two fields a Surname and a Firstname. What i am trying to achieve is delete an entire person or row from the database!

 

[jpadie]

you need to put the Carrick in quotes
also Personel is usually spelled with two "n"s. could it be a typo in your code?
and ... you should not put table names or column names in quotes. ideally you should surround them in backticks "`" but this is not strictly necessary unless you are using a reserved word as a table or column name.

 

[TrueJoker]

I ahve made some changes notebaley the typo S lol the code now looks liek this:

<?php

require_once ('mysql_connect.php');

if (!isset($_GET['do']) || $_GET['do'] != 1) {
?>
<p align="center">
Are you sure you want to delete this <?php
echo $_GET['type']; ?>?<br>
<a href="<?php echo $_SERVER['REQUEST_URI']; ?>&do=1">Yes</a>
or <a href="view.php">View Databasebase</a>
</p>
<?php
} else {
// delete a record

$sql = "DELETE FROM Personnel
WHERE Surname = " . $_GET['type'] . "
LIMIT 1";
//echo SQL for debug purpose
echo "<!--" . $sql . "-->";
$result = mysql_query($sql)
or die("Invalid query: " . mysql_error() );

?>
<p align="center">
Your <?php echo $_GET['type']; ?> has been deleted.
<a href="view.php">View Database</a>
</p>
<?php
}
?>

and i now get a different which i think could be progress S:

Invalid query: Unknown column 'Carrick' in 'where clause'

 

[jpadie]

you need to footprint your sql. the error will be derived from $_GET['type']

$sql = "DELETE FROM Personnel
                WHERE Surname = " . $_GET['type'] . "
                LIMIT 1";
echo $sql;

at the very least you should also be enquoting the criterion (and escaping it) as said above

$sql = "DELETE FROM Personnel
                WHERE Surname = '" . mysql_escape_string(trim($_GET['type'])) . "'
                LIMIT 1";
echo $sql;

 

[TrueJoker]

Thank you for the help so far )

now it all goes through with no error messages but the record is not deleted S it displays the message saying that it has been deleted but when i view the database the record is still there, so i guess im missing something now lol not sure what!

 

[jpadie]

then it is not finding a record that corresponds to the where part of the query.

can you post the full query (without variables)

 

[TrueJoker]

here is the query minus the variables:

<?php
} else {
// delete a record

= "DELETE FROM Personnel
WHERE '" . mysql_escape_string(trim($_GET['type'])) . "'
LIMIT 1";
//echo SQL for debug purpose
echo "<!--" . $sql . "-->";
= mysql_query($sql)
or die("Invalid query: " . mysql_error() );

?>

 

[jpadie]

sorry when i meant without the variables i meant can i see the real query. i.e. that which is actually sento the mysql engine.

echo $sql;

 

[TrueJoker]

Ah I see! the $sql contains this:

"DELETE FROM Personnel
WHERE '" . mysql_escape_string(trim($_GET['type'])) . "'
LIMIT 1";

I hope thats right!

 

[TrueJoker]

Does it matter that the database contains 2 columns a surname column and a firstname column.
The surname is set as the index or id for the database. The surname and first name are stored in serperate fields so will deleting the surname the delete the entire record firstname and surname?

 

[TrueJoker]

Just a quick update, if your interested ) I have managed to sort out the code s it will now delete a record! 1 minor problem it does not delete the selected record. It is deleting the record that was entered first e.g.

If i enter Joe Bloggs first then enter Davey Crocket the table is shown as follows:

Surname Firstname
Bloggs Joe [DELETE]
Crocket Davey [DELETE]

But if i try and delete Davey Crocket first it will just delete joe bloggs because he was entered first S

 

[TrueJoker]

FYI all i did was change the $sql query to:

"DELETE FROM Personnel
WHERE 'Surname' = '" . mysql_escape_string(trim($_GET['type'])) . "'
LIMIT 1";

 

[jpadie]

your sql is still not going to work. and please can you post an actual sql script that is actually sent to the db engine. there should be no variables in it, just text.

i.e. not

"DELETE FROM Personnel
 WHERE 'Surname' = '" . mysql_escape_string(trim($_GET['type'])) . "'
 LIMIT 1";

but

"DELETE FROM Personnel
 WHERE [b]'Surname'[/b] = 'somesurnameinsertedhere'
 LIMIT 1";

your sql code as posted above WILL NOT WORK becuase you are enquoting a column name and not putting it in backticks.

 

[TrueJoker]

I see.

DELETE FROM 'Personnel' WHERE 'Surname' = 'Bloggs' AND 'Firstname' = 'Joe' LIMIT 1

That is the script that is used to delete the query when i use PHPMyAdmin.

 

[jpadie]

this will not work. it may be that phpmyadmin does some clever stuff to undo user damage.

once again: do not enquote table names or column names.

if surname is a unique index then you do not need firstname in the where query.

other than for very specify applications, it is bad practice, in my book, to include LIMIT in a delete query. Typically in this kind of instance you would delete against a unique key.

this query will work:

DELETE FROM Personnel WHERE Surname = 'Bloggs' AND Firstname = 'Joe'

and so will this query

DELETE FROM `Personnel` WHERE `Surname` = 'Bloggs' AND `Firstname` = 'Joe'

 

[TrueJoker]

Thank you, I can delete a record of my choosing now but only if is specify in the php code the surname! what i was aiming for was in the table view page where the table appears like so:

Surname Firstname
Bloggs Joe [DELETE]
Crocket Davey [DELETE]

When [DELETE] is selected it will delete that specific record. Does this mean that i have to create a seperate page for each record in the database or is there a way so 1 page will delete the desired record if that makes sense?

Sorry if im ******* you off i really appriciate the help

 

[jpadie]

Sorry if im ******* you off i really appriciate the help

Never.

try this. The code is a self-processing form. just put the whole form in a single page and add your database connection code (mysql_connect and mysql_select_db) at the top.

<?
$msg = "";
if (isset ($_GET['action']) && $_GET['action'] === "delete"):
 if (isset($_GET['Surname'])):
  deleteuser($_GET['Surname']);
 endif;
endif;
function deleteuser ($surname) {
 $result = @mysql_query("Delete from Personnel where Surname='". mysql_escape_string($surname) ."'");
 global $msg;
 $msg = ($result) ? "User $surname has been deleted": "There was an error deleting $surname from the database"; 
}
$sql = "Select Surname, Firstname from Personnel";
$result = mysql_query ($sql) or die(mysql_error());
if (empty($msg)):
 echo $msg . "<hr/>";
endif;
$table = "<table>\r\n<tr><th>Surname</th><th>Firstname</th><th>&nbsp;</th></tr>
while ($row = mysql_fetch_assoc($result)):
 $contents .= "\r\n<tr><td>".$row['Surname']."</td><td>".$row['Firstname']."</td><td><a href=\"".$_SERVER['PHP_SELF']."?action=delete&Surname=".$row['Surname'].">Delete</a></td></tr>";
endwhile;
$contents .= "</table>";
echo $contents;
?>